MySQL Fetch all rows into array using column as key

[Valakai]

I've been using this code for a long time and realised it's very repetitive, but the id_column I want changes all the time

 

function getRows()
{
$query = mysql_query($sql);
$rows = array();

while($row = mysql_fetch_assoc($query))
{
	$rows[$row['id_column']] = $row;
}

return $rows;
}

 

So I wrote this function that will automatically create an array with a column I choose if I wish, but I'm not sure if it's very efficient.

 

function getRows($query, $column_id = false)
{
$rows = array();

while($row = mysql_fetch_assoc($result))
{
	if($column_id === false)
	{
		$rows[] = $row;
	}
	else
	{
		if(isset($row[$column_id]))
		{
			$rows[$row[$column_id]] = $row;
		}
		else
		{
			$rows[] = $row;
		}
	}
}

        return $rows;
}

 

I would appreciate some input as to make it better.

Thanks.

[trq]

Neither of the functions posted will work, they both contain undefined variables. Nor does the idea make much sense. What if you want more than 1 record? You'll only ever get the last record.

[Valakai]

Sorry, I copied the functions wrong

 

In the first function $sql is just a place holder for whatever SQL I will want to execute.

 

In the second function mysql_fetch_assoc($result) is meant to be mysql_fetch_assoc($query), $query being returned from a mysql_query()

 

These functions would return all the records as the id_column I choose is going to be a unique id like the primary key.

 

As an example if I ran this on a user database

 

$rows = getRows(mysql_query('SELECT user_id, user_name, user_email FROM users'), 'user_id');

 

would return

 

Array
(
1 => Array('user_id' => 1, 'user_name' => 'example1', 'user_email' => 'example1@address.com'),
2 => Array('user_id' => 2, 'user_name' => 'example2', 'user_email' => 'example2@address.com'),
3 => Array('user_id' => 3, 'user_name' => 'example3', 'user_email' => 'example3@address.com')
)

 

[trq]

These functions would return all the records as the id_column I choose is going to be a unique id like the primary key.

 

How do you figure? You keep overriding the same index.

[Valakai]

I'm not sure if you are reading my code right, but to make it a bit simpler:

 

while($row = mysql_fetch_assoc($query))
{
$rows[$row['user_name']] = $row;
}

 

Would become:

 

$rows['user1'] = array('user_id' => 1, 'user_name' => 'user1');
$rows['user2'] = array('user_id' => 1, 'user_name' => 'user2');
$rows['user3'] = array('user_id' => 1, 'user_name' => 'user2');

 

There is no overriding indexes, each value is unique and would create an array of the data.

[trq]

I could have swarn you said the column_id wasn't unique. Sorry.

 

Anyway, I don't really see the point in this function. If it produces .....

 

$rows['user1'] = array('user_id' => 1, 'user_name' => 'user1');
$rows['user2'] = array('user_id' => 1, 'user_name' => 'user2');
$rows['user3'] = array('user_id' => 1, 'user_name' => 'user2');

 

Then it is duplicating data. Why do you need the user names twice? You much better off simply returning a numerically indexed array....

 

$rows = array();
while($row = mysql_fetch_assoc($query)) {
  $rows[] = $row;
}

 

And then again even more better off looping through the results in your calling code. This way you only need the one loop and not two.

[Valakai]

Ok, thank you for the input. I'll stick to a numeric index array.