PHP shopping cart showing orders

Question

Hy,

I'm creating a PHP shop for training purposes. I want the user give the possibility to view the last orders he placed - but I've problems to realize this.

I've these tables in my database:

customer (kid, name, address,...)
products (pid, product_name, product_description, price)
orders --> oid, kid,payment, address, status
order_detail --> oid, date, pid, quantity)

ok, I've created a function where a query gets the needed data from the database

function showOrder($kid)
{
$db = database();
$orders = $db->query ("SELECT * FROM orders
    INNER JOIN order_detail on orders.oid=order_detail.oid
    INNER JOIN products on order_detail.pid = products.pid
    WHERE kid='$kid' ");

$orders = $orders->fetchAll();

$lastoid = 0;
foreach($orders as $i){
    
    while($lastoid != $i['oid']) {
        $lastoid = $i['oid'];
        echo "Ordernr: ".$lastoid."<br/>";
        echo "Produktname: ".$i['product_name']."<br>";
        echo "Menge: ".$i['quantity']."<br/>";
        echo "Preis: ".$i['price']."<br/>";
        echo "Status: ".$i['status']."<br/>";
        echo "<br/><br/><hr/>";    
    }
}
}

What I want to do: list the single orders in a table (--> oderid | product name | quantity | price | status) it's working if the order only contains of one product, but if the order gets bigger (2 products), only the first one is shown.

$database looks like this:

Array
(
[0] => Array
    (
        [oid] => 1
        [0] => 1
        [kid] => 1
        [1] => 1
        [2] => 1
        [date] => 2012-04-17
        [3] => 2012-04-17
        [pid] => 1
        [4] => 1
        [quantity] => 2
        [5] => 2
        [payment] => Nachnahme
        [6] => Nachnahme
        [street] => teststraße
        [7] => teststraße
        [number] => 2
        [8] => 2
        [zip] => 2222
        [9] => 2222
        [city] => Teststadt
        [10] => Teststadt
        [status] => in Bearbeitung
        [11] => in Bearbeitung
        [12] => 1
        [product_name] => Acer Laptop
        [13] => Acer Laptop
        [price] => 29.00
        [14] => 29.00
        [details] => blabla
        [15] => blabla
        [category] => Laptop
        [16] => Laptop
        [date_added] => 2012-04-05
        [17] => 2012-04-05
    )

[1] => Array
    (
        [oid] => 1
        [0] => 1
        [kid] => 1
        [1] => 1
        [2] => 1
        [date] => 2012-04-17
        [3] => 2012-04-17
        [pid] => 2
        [4] => 2
        [quantity] => 2
        [5] => 2
        [payment] => Nachnahme
        [6] => Nachnahme
        [street] => teststraße
        [7] => teststraße
        [number] => 2
        [8] => 2
        [zip] => 2222
        [9] => 2222
        [city] => Teststadt
        [10] => Teststadt
        [status] => in Bearbeitung
        [11] => in Bearbeitung
        [12] => 2
        [product_name] => Grundig TV
        [13] => Grundig TV
        [price] => 22.00
        [14] => 22.00
        [details] => blabla

        [15] => blabla
        [category] => TV
        [16] => TV
        [date_added] => 2012-04-05
        [17] => 2012-04-05
    )

[2] => Array
    (
        [oid] => 1
        [0] => 1
        [kid] => 1
        [1] => 1
        [2] => 1
        [date] => 2012-04-17
        [3] => 2012-04-17
        [pid] => 7
        [4] => 7
        [quantity] => 1
        [5] => 1
        [payment] => Nachnahme
        [6] => Nachnahme
        [street] => teststraße
        [7] => teststraße
        [number] => 2
        [8] => 2
        [zip] => 2222
        [9] => 2222
        [city] => Teststadt
        [10] => Teststadt
        [status] => in Bearbeitung
        [11] => in Bearbeitung
        [12] => 7
        [product_name] => Nokia Handy
        [13] => Nokia Handy
        [price] => 69.00
        [14] => 69.00
        [details] => blabla
        [15] => blabla
        [category] => Handy
        [16] => Handy
        [date_added] => 2012-04-06
        [17] => 2012-04-06
    )

[3] => Array
    (
        [oid] => 2
        [0] => 2
        [kid] => 1
        [1] => 1
        [2] => 2
        [date] => 2012-04-17
        [3] => 2012-04-17
        [pid] => 8
        [4] => 8
        [quantity] => 1
        [5] => 1
        [payment] => Vorauskasse
        [6] => Vorauskasse
        [street] => musterstraße
        [7] => musterstraße
        [number] => 1
        [8] => 1
        [zip] => 1111
        [9] => 1111
        [city] => stadt
        [10] => stadt
        [status] => in Bearbeitung
        [11] => in Bearbeitung
        [12] => 8
        [product_name] => PC groß
        [13] => PC groß
        [price] => 66.00
        [14] => 66.00
        [details] => blabla
        [15] => blabla
        [category] => Computer
        [16] => Computer
        [date_added] => 2012-04-06
        [17] => 2012-04-06
    )
)  

Answer 1

Your code enters the while loop with

$lastoid = 0;

It will run the first while loop because $lastoid is not equal to $i['oid']. One is zero, the other is one. In the next line you assign the value of $i['oid'] to $lastoid. This makes them equal and your while loop will stop. If the loop wouldn't break, you would have an endless row of the same information. In the while loop there is no reference to the next order.

Change it like this

$lastoid = 1;
foreach($orders as $key => $value){

    if($lastoid == $value[$key]['oid']) {
        echo "Ordernr: ".$lastoid."<br/>";
        echo "Produktname: ".$i['product_name']."<br>";
        echo "Menge: ".$i['quantity']."<br/>";
        echo "Preis: ".$i['price']."<br/>";
        echo "Status: ".$i['status']."<br/>";
        echo "<br/><br/><hr/>";    
    } else {
        $lastoid = $value[$key+1]['oid'];
    }
}

Answer 2

The problem is you are not selecting from product table. Your select query is just orders.*, which is nothing more than all the columns under orders table. The solution is to add products columns to your select query. I am not very sure why you are explicitly using an inner join, mine would be something like this:

select orders.*, products.* from orders,order_detail,products where 
orders.oid=order_detail.oid and order_detail.pid=products.pid and orders.kid=1

Here is the result I have got:

| oid | kid | pid | product_name |
|  1  |  1  |  1  | camera       |
|  1  |  1  |  2  | pants        |