newbie sql join question

[mobly]

I'm trying to write a simple SQL query that passes into php.

I have 2 tables 1 containing orders and the other containing individual items in a order.

So the statement needs to return the order details and the items for that order.

I have these tables:
order

ID | user_id | date | amount | order_type

order_items

ID | order_id | item | price | quantity

So my guess was:

SELECT * FROM order 
LEFT JOIN order_items 
ON order.ID = order_items.order_id 
AND order.user_id = "0"

if($orders_result){
    $orders_numRows = mysql_num_rows($orders_result);
    for($i=0; $i<$orders_numRows; $i++){
	$orders[$i] = mysql_fetch_assoc($orders_result);
    }
}

however what gets returned is 2 arrays:

Array
(
    [0] => Array
        (
            [ID] => 0
            [user_id] => 0
            [date] => 2007-02-08
            [amount] => 63
            [order_type] => Test
            [order_id] => 0
            [item] => 12B444
            [price] => 10
            [quantity] => 2
        )

    [1] => Array
        (
            [ID] => 1
            [user_id] => 0
            [date] => 2007-02-08
            [amount] => 63
            [order_type] => Test
            [order_id] => 0
            [item] => 345AA1
            [price] => 43
            [quantity] => 1
        )

which makes it look like 2 orders, where as I want the order details

     [ID] => 1
            [user_id] => 0
            [date] => 2007-02-08
            [amount] => 63
            [order_type] => Test
            [order_id] => 0

followed by all the items in that order

            [item] => 345AA1
            [price] => 43
            [quantity] => 1

and

            [item] => 12B444
            [price] => 10
            [quantity] => 2

to come out a a single array for that order.

Can anyone help me work out what the SQL would need to do this please?

 

[hvass]

Are you looking for

order1, order_item1.1, order_item1.2, order_item1.3
order2, order_item2.1,,,
order3, order_item3.1, order_item3.2,,
etc..
?

If you are this predisposes that you know the maximum number of order_items per order.. you therefore cannot write this as a general piece of SQL code (not easily anyway) ... a traditional approach to this would be to use code/scripting language to put in the logic to handle any number of order_items something like php...

 

[mobly]

yup, something like that.
I basically want all the details in one array per order
not 1 array per item ordered with the order details repeated.

I'm sure I've done this before but I spent the best part of the last 2.5 years away from the internet so I'm having to wake up old memories...

 

[r937]

that's a php question, not a mysql question

but since you posted your query, here are some tips

- never use the dreaded, evil "select star"
- don't use reserved words like ORDER as a table name
- use an INNER JOIN, not a LEFT JOIN
- if user_id is numeric, don't compare it to a string

welcome back to the internet

r937.com | rudy.ca