SQL results to array without duplicated id and post_title in one array

user_12941277 0
Viewed 51

I am working on received data from the database. I want this data in an array but the ID and post_title returns more than one in the results. 

wp_posts
+----+------------+
| ID | post_title |
| 1  | radio      |
| 2  | television |
+----+------------+

wp_postmeta
+---------+---------+----------+------------+ 
| meta_id | post_id | meta_key | meta_value |
|    1    |    1    | price    | 12.50      |
|    2    |    1    | _sku     | 5876gh7    |
|    3    |    1    | thumb_id | 56         |
|    4    |    2    | price    | 342.50     |
|    5    |    2    | _sku     | tv784      |
|    6    |    2    | thumb_id | 78         |
+---------+---------+----------+------------+ 

$query = "
    SELECT p.ID, p.post_title, m.meta_id, m.meta_key, m.meta_value
    FROM wp_posts p
    LEFT JOIN wp_postmeta m ON (m.post_id = p.ID)
    WHERE EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
    OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
    ORDER BY p.ID, m.meta_id
";


$result     = mysqli_query($connection, $query);

while($row = $result->fetch_object() ){
        $sendback[] = $row;
}

print_r($sendback);

This is the result that I see if I print this. You can see the ID and post_title is showing in every result.

array(
    [0] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => price 
        [meta_value]    => 12.50 
    ) 
    [1] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => _sku 
        [meta_value]    => 5876gh7
    )
    [2] => stdClass Object ( 
        [ID]            => 1 
        [post_title]    => Radio  
        [meta_key]      => thumb_id 
        [meta_value]    => 56
    )
)

Which method can I use to get the ID and post_title once like this

array(
        'id'            => '1',
        'post_title'    => 'radio',
        'price'         => '12.50',
        'sku'           => '5876gh7',
        'thumb_id'      => '56'
    )
join arrays sql php database
2 Answers

There's no way to solve this directly using only SQL, you'll need to process those entries. The reason is that wp_postmeta has many entries for a single wp_posts entry. So what you can do is:

$SQL = <<<SQL
  SELECT *
  FROM wp_posts
SQL;

$posts = $dbh->query($SQL)->fetchAll();

You'll have all of your posts

foreach($posts as $post) {
  $prodArr[]['id'] = $post['id'];
  $prodArr[count($prodArr) - 1]['post_title'] = $post['post_title'];
  $SQL = <<<SQL
    SELECT *
    FROM wp_postmeta
    WHERE post_id = {$post['id']}
SQL;
  $metas = $dbh->query($SQL)->fetchAll();
  foreach($metas as $meta) {
    if ($meta['meta_key'] == 'price')
      $prodArr[count($prodArr) - 1]['price'] = $meta['meta_key'];
    else if ($meta['meta_key'] == '_sku')
      $prodArr[count($prodArr) - 1]['sku'] = $meta['meta_key'];
    else if ($meta['meta_key'] == 'thumb_id')
      $prodArr[count($prodArr) - 1]]['thumb_id'] = $meta['meta_key'];
  }
}

So the $prodArr will have all the info you need for all products and then you can iterate it. The code may contain errors, I haven't tested it.

user_27265380

If you know in advance the list of meta keys that you want to return, you can use conditional aggregation to pivot your dataset and produce the results that you expect:

SELECT 
    p.ID, 
    p.post_title, 
    MAX(CASE WHEN m.meta_key = 'price'    THEN m.meta_value END) price,
    MAX(CASE WHEN m.meta_key = '_sku'     THEN m.meta_value END) sku,
    MAX(CASE WHEN m.meta_key = 'thumb_id' THEN m.meta_value END) thumb_id
FROM wp_posts p
LEFT JOIN wp_postmeta m ON m.post_id = p.ID
WHERE 
    EXISTS (SELECT 1 FROM wp_posts WHERE post_title LIKE '%rad%' AND ID = p.ID)
    OR EXISTS (SELECT 1 FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%rad%' AND post_id = p.ID)
GROUP BY p.ID, p.post_title
ORDER BY p.ID

I also suspect that the subqueries in the WHERE clause can be simplified:

SELECT 
    p.ID, 
    p.post_title, 
    MAX(CASE WHEN m.meta_key = 'price'    THEN m.meta_value END) price,
    MAX(CASE WHEN m.meta_key = '_sku'     THEN m.meta_value END) sku,
    MAX(CASE WHEN m.meta_key = 'thumb_id' THEN m.meta_value END) thumb_id
FROM wp_posts p
LEFT JOIN wp_postmeta m ON m.post_id = p.ID
GROUP BY p.ID, p.post_title
HAVING 
    p. post_title LIKE '%rad%'
    OR MAX(CASE WHEN m.meta_key = '_sku' AND m.meta_value LIKE '%rad%' THEN 1 ELSE 0 END) = 1
ORDER BY p.ID
user_106767160
Related