mySQL query using group_concat()

[rcane]

Hey folks,

I'm doing a query on the themes table to grab some a few custom profile fields and concatenate them.

For example:  cust_city, cust_state, and cust_zip would produce a result of

"chicagoIL60606".

But, mySQL has it's own ordering and the query comes out as:

"chicago60606IL"


This is the query I'm using.

Code Select Expand

SELECT GROUP_CONCAT(`value` SEPARATOR "") FROM smfqg_themes WHERE `id_member` = '1' AND `variable` IN ('cust_city', 'cust_state', 'cust_zip')
Since all the values are in the `values` column, I cannot figure out how to order them in any other desired way using ORDER BY or GROUP BY.

Can someone take a look at pen a suggestion if they have one?



If I can't figure out a query that works, is it possible to change the order in the table----without doing what I think would probably happen? 

[rcane]

It took some searching but ordering by case variable seems to work fine.

SELECT GROUP_CONCAT(
  `value`
  order by case variable when 'city' then 1 when 'state' then 2 else 3 end
  SEPARATOR ""
) citystatezip
FROM themes
WHERE `id` = '1' AND `variable` IN ('city', 'state', 'zip')

[rcane]

Well, if done in phpMyAdmin the query returns things just fine.

However, if I put it in php and try to output it to an array to either var_dump or print_r it says NULL.


For example: 

Code Select Expand

$test = $conn->query(" SELECT GROUP_CONCAT(
  `value`
  order by case variable when 'city' then 1 when 'state' then 2 else 3 end
  SEPARATOR ""
) citystatezip
FROM themes
WHERE `id_member` = '1' AND `variable` IN ('city', 'state', 'zip')"



while ($row = $test->fetch_object()) {
$results[] = $row;


var_dump($results);


[Sesquipedalian]

The posted PHP code contains multiple syntax errors and will not compile. You'll need to fix those before you can make any further progress.

[rcane]

The posted PHP code contains multiple syntax errors and will not compile. You'll need to fix those before you can make any further progress.

hmm.  it's working if I change it to:

$results[] = $row->citystatezip;


Can you tell which parts didn't look good to you anyway?

[Doug Heffernan]

Try defining the $results array.

$results = array();