CODEWITHSUNDEEP
phpmysql
infobuster
infobuster

Reputation: 37

PHP MySql Query to return all items for an order based on order number

I am trying to create a script that displays in PHP all the items within a order.

Each order item uses a different row in the MySql database but has the same order ID so one order can have several rows in the datebase as below.

OrderNumber  |  Item
------------------------
101          | Banana
101          | Apple 
101          | Grapes
102          | Apple
103          | Grapes
103          | Banana

This is the code i have so far

$query ="SELECT *, 
            group_concat(OrderNumber)FROM MyOrders 
          group by OrderNumber 
          order by OrderNumber;";
$result = $conn->query($query);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $item = $row["Item"];
        $on =$row["OrderNumber"];
        echo $on;
        echo "<br/>";
        echo $item;
        echo "<br/>";
    }
}

However the code above seems to work but only displays the first poduct 

101
Banana
102
Apple
103
Grapes

what i am try to achieve is

101
Banana
Apple
Grapes
102
Apple
103
Grapes
Banana

Upvotes: 1

Views: 734

Answers (1)

BA_Webimax
BA_Webimax

Reputation: 2679

You are doing the group_concat on the wrong column. You should group the items. It also helps to assign an identifier to the group for easy access in the PHP portion of the script. I have also simplified the code some.

$query ="SELECT OrderNumber, group_concat(Item) as Items
        FROM MyOrders 
        GROUP BY OrderNumber 
        ORDER BY OrderNumber;";

$result = $conn->query($query);

if ($result->num_rows > 0) 
{
    while($row = $result->fetch_assoc()) 
    {
        echo $row["OrderNumber"] . '<br>';
        echo $row["Items"] . '<br>';
    }
}

Upvotes: 2

Related Questions