0

I'm having these two tables:

wp_posts

ID post_title post_type
1 Foo product
2 Bar product
3 Test product

wp_postmeta

ID post_id meta_key meta_value
150 1 stock_ny 150
151 1 stock_la 70
152 1 stock_den 90
153 1 blah_blah asdf
160 1 blah_blah2 dfdfdf
175 2 stock_ny 80
185 2 stock_la 20
195 2 stock_den 0
200 2 blah_blah asdf
210 2 blah_blah2 dfdfdf
220 3 stock_ny 86
230 3 stock_la 100
240 3 stock_den 99
250 3 blah_blah gddj
260 3 blah_blah2 jdfdfj

I'm calling it via PHP and WordPress. I've made it this far (not very long, I know):

<?php
$query = 'SELECT ID, post_title, post_type';
$query .= 'FROM wp_posts';
$query .= 'WHERE post_type = "product" LIMIT 10';
$products = $wpdb->get_results( $query, OBJECT );
echo '<pre>';
print_r($products);
echo '</pre>';
?>

And I would like an output like this:

[1] => stdClass Object
    (
        [id] => 1
        [post_title] => Foo
        [post_type] => product
        [stocks] => [
            [stock_ny] => 150,
            [stock_la] => 70,
            [stock_den] => 90
        ]
    ),
[2] => stdClass Object
    (
        [id] => 2
        [post_title] => Bar
        [post_type] => product
        [stocks] => [
            [stock_ny] => 80,
            [stock_la] => 20,
            [stock_den] => 0
        ]
    ),
[3] => stdClass Object
    (
        [id] => 3
        [post_title] => Test
        [post_type] => product
        [stocks] => [
            [stock_ny] => 86,
            [stock_la] => 100,
            [stock_den] => 99
        ]
    )

I assume it's something with a join?

Zeth
  • 2,273
  • 4
  • 43
  • 91
  • The column names in the table dont match the column names used in the query??? It really soes help if you sanity check your question before posting – RiggsFolly Dec 28 '21 at 15:18
  • Yes a JOIN would work, do give it a try – RiggsFolly Dec 28 '21 at 15:19
  • I fixed the names of the columns, so it matches my query (thanks for pointing that out). And can you give me a small push in the right direction? Which join should I use - and how do I merge them together? Perhaps a link to somewhere, where this is done? I'm shooting blanks, here. :-/ :see_no_evil: – Zeth Dec 28 '21 at 15:22
  • You cannot make a query generate that data structure by itself. You will have to query the data and then create the required array from what SQL will allow – RiggsFolly Dec 28 '21 at 15:24
  • Use a join to put the related rows from the two tables in the same row of the results. Then see https://stackoverflow.com/questions/35473174/creating-one-array-from-another-array-in-php for how to create the nested arrays from the common keys. – Barmar Dec 28 '21 at 15:35

0 Answers0