Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Dec 2007
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts

    PHP, 2 rows into 1

    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:
    Code:
    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:
    Code:
    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 	  	sales@proxyplayer.co.uk
    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?
    Code:
    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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    There are two ways. MySQL supports a group concatenation operator as an aggregate that allows you to lump multiple record's matching fields into a single returned result using the group_concat operation. The other is as you say. You'll need to fetch both rows out of the recordset, and go from there. You can't do it in a single while loop though, you'll need to capture them likely in an array and then process it after the loop, so it only has value if the entries are all related.
    This is why normalization is such a key thing. When you see comma separated lists or serialized arrays in your datastructures, there is likely something incorrect with the storage design itself. Incorrect normalization always leads to complex queries.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •