CODEWITHSUNDEEP
phpmysqlpdo
Peter Lur
Peter Lur

Reputation: 758

MySQL GROUP BY: How to return multiple rows?

I have the current two tables. I want to retrieve all the corresponding rows of an order in ONE query.

enter image description here

My current code is as follow:

$query = '
SELECT * FROM table_a
JOIN table_b ON table_b.order_id = table_a.order_id
WHERE table_a.order_id = '1'
GROUP BY table_a.order_id
';

$result_prepare = $this->DB->prepare($query);
$result_prepare->execute();

$result = $result_prepare->fetchAll(PDO::FETCH_ASSOC);
$query_result = array('result'=>$result);

print_r($query_result);

The output result return only THE FIRST ROW:

Array
(
    [0] => Array
        (
            [order_id] => 1
            [row_id] => 1
            [value] => 100
        )

}

I would like the output result to return all rows GROUPED by row.

Array
(
    [0] => Array
        (
            [order_id] => 1
            [rows] => Array
                    (
                        [0] => Array
                            (
                                [row_id] => 1
                                [value] => 100

                            )

                        [1] => Array
                            (
                                [row_id] => 2
                                [value] => 101

                            )

                    )

        )

}

How can I achieve this? I need to change my SQL query.

Upvotes: 2

Views: 3132

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270609

Your single-row is a result of MySQL's handling of GROUP BY absent any aggregate functions like COUNT(),SUM(),MIN(),MAX(). It collapses the group to a single row with indeterminate values for the non-grouped columns and is not something to be relied on.

You don't need a GROUP BY though, if you want an array structure which is indexed by order_id. Instead, perform a simple query and then in your PHP code, restructure it in a loop to the format you are looking for.

// join in table_a if you need more columns..
// but this query is doable with table_b alone
$query = "
SELECT 
 order_id,
 row_id,
 value
FROM table_b
WHERE order_id = '1'
";

// You do not actually need to prepare/execute this unless you
// are eventually going to pass order_id as a parameter
// You could just use `$this->DB->query($query) for the
// simple query without user input
$result_prepare = $this->DB->prepare($query);
$result_prepare->execute();

$result = $result_prepare->fetchAll(PDO::FETCH_ASSOC);

// $result now is an array with order_id, row_id, value
// Loop over it to build the array you want
$final_output = array();
foreach ($result as $row) {
  $order_id = $row['order_id'];

  // Init sub-array for order_id if not already initialized
  if (!isset($final_output[$order_id])) {
    $final_output[$order_id] = array();
  }
  // Append the row to the sub-array
  $final_output[$order_id][] = array(
    'row_id' => $row['row_id'],
    'value' => $row['value']
  );
  // You could just append [] = $row, but you would still 
  // have the order_id in the sub-array then. You could just 
  // ignore it. That simplifies to:
  // $final_output[$order_id][] = $row;
}

// Format a little different than your output, order_id as array keys
// without a string key called order_id
print_r($final_output);

Upvotes: 2

Mandu
Mandu

Reputation: 157

I think you need to use an OUTER JOIN statment, not GROUP BY.

Upvotes: 0

duffymo
duffymo

Reputation: 308743

I think you want this: 

$query = '
SELECT * 
FROM table_a
JOIN table_b 
ON table_b.order_id = table_a.order_id
WHERE table_a.order_id = '1'
ORDER BY b.row_id
';

Upvotes: 0

Related Questions