Get a single array from mysqli

Question

I'm trying to get all of the values in a certain field in a table. The basic query:

SELECT `field`
FROM `table`
WHERE `category`="something"
ORDER BY `other_field` DESC

The names of these fields will be return to an AJAX call, so my idea right now is to get this information into an array, and then json_encode that array.

But it's just a list of names, and it's ending up in JavaScript. And my "array/encode" method would require going over the results twice (when the query returns, I'd have to convert a two-dimensional array to one-dimensional). I'd like to avoid that, if possible.

So is there a better method to do this that I don't see? Is there MySQL function that will return all the fields as a single array?

a1ex07 · Accepted Answer · 2011-02-03 15:36:21Z

3

You can try to use GROUP_CONCAT to get all field values in one row, then convert it to array by explode in php. I'm not sure if it is faster than iterating over collection though.
Update A short query example.

SELECT GROUP_CONCAT(`field` ORDER BY `order_field` DESC SEPARATOR '|')
FROM `table`
WHERE `category`="something";

*Notice : while choosing separator, make sure it doesn't occur in field's values. It's also possible to escape occurrences of separator in field using, for instance, REPLACE

edited Feb 3, 2011 at 15:36

answered Feb 3, 2011 at 1:44

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

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

2 Comments

sdleihssirhc Over a year ago

This is exactly what I was looking for. Now I can just echo the GROUP_CONCAT straight to JavaScript, and split it there. Thanks.

xkeshav Over a year ago

@alex please write UPDATED Query..it will b helpful for seeker

Collectives™ on Stack Overflow

Get a single array from mysqli

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related