Eric Lee Eric Lee - 6 months ago 19
MySQL Question

how to retrieve a table with many tables with PDO

Recently I've been using PDO

and I'd like to get a table with hasmany related table.

I can get a table with the tables like this

array (
0 =>
stdClass::__set_state(array(
'order_id' => '170',
'purchase_id' => '222',
'product_option_id' => '014',
)),
1 =>
stdClass::__set_state(array(
'order_id' => '170',
'purchase_id' => '600',
'product_option_id' => '015',
)),
)


with SQL query like this

SELECT ord.order_id,puc.purchase_id,puc.product_option_id
FROM order ord
JOIN purchase puc
ON ord.order_id = puc.order_id
WHERE ord.order_id = '170'


However I'd like to get this data like this

array (
0 =>
array(
'order_id' => '170',
'purchase_id' => '222',
'purchase' => array(
0 =>
array(
'purchase_id' => '222',
'product_option_id' => '014',
),
1 =>
array(
'purchase_id' => '600',
'product_option_id' => '015',
),
)
)
)


How do get the data like this with PDO?

Thank you

Answer

Luckily, you CAN have it with PDO.

Were you need anything but id from orders, the mission were impossible.

But as long as you need only unique value from orders, you can get the thing like this

array (
  170 => 
  array(
         0 =>
         array(
             'purchase_id' => '222',
             'product_option_id' => '014',
         ),
         1 =>
         array(
             'purchase_id' => '600',
             'product_option_id' => '015',
         ),
      )
   )
)

with as simple code as

$sql = "your sql";
$pdo->query($sql)->fetchAll(PDO::FETCH_GROUP);

However, I see very little sense in fetching the same order ID usedto filter the data. May be it should be some other query that is closer to your real needs? Like one that is fetching several orders?