PHP get MySQL database as associative array by row

Question

I have a simple mySQL database table that I am loading into a PHP array. I would like the id column of the mySQL table (which is auto incremented, but I don't think that's relevant) to be the array key for each element of the PHP array, instead of the array being numeric.

Instead of this:

Array(
  Array(id=>'1', field1=>someval, field2=>val),
  Array(id=>'2', field1=>val, field2=>otherval),
  Array(id=>'4', field1=>val, field2=>otherval)
)

I want this:

Array(
  1=>Array(field1=>someval, field2=>val),
  2=>Array(field1=>val, field2=>otherval),
  4=>Array(field1=>val, field2=>otherval)
)

I don't care if id is left in the associative array for each row.

Is there a way to do this without looping through the original mySQL array and using up lots of processing time?

xdazz · Accepted Answer · 2011-10-21 01:39:17Z

2

You can do it at the fetch time like this:

$query_ret = mysql_query(...);
$result = array();
while ($row = mysql_fetch_assoc($query_ret)) {
    $result[array_shift($row)] = $row;
}

answered Oct 21, 2011 at 1:39

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xdazz Over a year ago

@Hammerite Didn't read the question? I think the original mySQL array which OP means is the $result array after fetch loop, you can make it at the fetch loop time, not another loop to change it after the fetch loop, the fetch loop is a must, isn't it?

Hammerite Over a year ago

When the OP says "original MySQL array", he means the MySQL result object contained in your $result. He asks for a way to achieve the end-result of your code, but without using the while loop in your code. But since your interpretation is a result of (what I see as being) misunderstanding rather than failing to read the question, I remove my downvote.

Hammerite Over a year ago

It turns out I cannot remove my downvote because it was too long ago that I placed it.

Hammerite · Accepted Answer · 2011-10-21 01:33:37Z

1

"Is there a way to do this without looping through the original mySQL array and using up lots of processing time?"

I believe the answer to this is no. The best you can do is to try to be as efficient as possible when looping through the array.

answered Oct 21, 2011 at 1:33

Hammerite

22.5k6 gold badges76 silver badges102 bronze badges

Comments

Caleb Gray · Accepted Answer · 2013-05-31 20:52:06Z

0

If you have PDO, you should definitely see Example #3 from the PDO documentation for fetchall: http://www.php.net/manual/en/pdostatement.fetchall.php#example-1022

Not only is this way more efficient use of your server's memory and processing power... it would also enable you to take advantage of several of PDO's other powerful APIs.

answered May 31, 2013 at 20:52

Caleb Gray

3,1272 gold badges24 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP get MySQL database as associative array by row

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related