PHP，2行为1

Question

I am having trouble getting the correct result set into 1 row because of the way the db is organised. I use the following SQL:

SELECT ord.ID, ord.post_date, ord.post_status, ord.post_type, 
meta.meta_value, term.term_id, term.name, 
SUBSTRING(meta.meta_value,LOCATE('\"id\";i:', meta.meta_value)+7,3) AS prodID,
user.user_email
FROM wp_posts ord
INNER JOIN wp_postmeta meta ON meta.post_id = ord.ID
INNER JOIN wp_term_relationships rel ON ord.ID = rel.object_id 
INNER JOIN wp_terms term on term.term_id = rel.term_taxonomy_id 
LEFT JOIN wp_users user ON user.ID = meta.meta_value
WHERE ord.post_type = 'shop_order'
AND (meta.meta_key = '_customer_user' OR meta.meta_key = '_order_items')
AND ord.post_date >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
AND ord.post_status <> 'trash'
AND term.term_id = 34
ORDER BY `ord`.`ID`  DESC

results are:

ID Descending   post_date   post_status     post_type   meta_value  term_id     name    prodID  user_email
451     2013-02-02 10:24:00     publish     shop_order  2   34  processing      [e-mail]
451     2013-02-02 10:24:00     publish     shop_order  a:1:{i:0;a:10:{s:2:"id";s:3:"339";s:12:"variation_...   34  processing  338     NULL

Ideally, I would like to bring back the meta.meta_value as just one row, so, the meta value 2 would be concatenated with a:1:{i:0;a:10:{s:2:"id";i:338;s:12:"variation_id";...

However, I don't think I can do that in this instance as the meta_value has 2 separate rows in the meta table.

So, I thought I could control it in PHP and get the value 2 and the value 338 out. But how can I do this in a while loop?

while ($squid = mysql_fetch_array($result, MYSQL_ASSOC)) {

//1st row

//2nd row

//processing code

}

do I put an extra while loop inside that to skip through every 2 rows of the data in the db? Should I load it into an array and the loop through 2 rows in the array?

Answer 1

You're correct that you need to do this in PHP: MySQL doesn't understand PHP's serialized data format.

However, you don't need to skip rows as you say. Create an array outside the loop that will store a $ID -> meta_data relation. I don't exactly know how you want to concatenate them, but look at this for an example.

$squArr = array();
while($squid = mysql_fetch_assoc...) // I'm using fetch assoc so I can use the row names as array indices
{
    $rowID = $squid['ID'];
    $rowMeta = $squid['meta_value'];
    $squArr[$rowID][] = $rowMeta;
}

Here, this will create an array of meta data for each ID. You could replace the operation in the last line of the while loop with a different operation though.