显示命令的PHP购物车

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 get's 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

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        |