CODEWITHSUNDEEP
phpmysqlpdo
Eric Lee
Eric Lee

Reputation: 710

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

Upvotes: 1

Views: 45

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157885

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?

Upvotes: 3

Mihai Matei
Mihai Matei

Reputation: 24276

You can't get it like this with PDO, but you can rearrange the results. A possible way to do it would be to use the array_reduce function.

$orders = array_reduce($results, function ($carry, $item) {
    if (!isset($carry[$item->order_id])) {
        $carry[$item->order_id] = array(
            'order_id' => $item->order_id,
            'purchase' => array(array(
                'purchase_id' => $item->purchase_id,
                'product_option_id' => $item->product_option_id,
            ));
        );
    } else {
        $carry[$item->order_id]['purchase'][] = array(
           'purchase_id' => $item->purchase_id,
           'product_option_id' => $item->product_option_id,
        );
    }
    return $carry;
}, array());

Upvotes: 1

Related Questions