Querying table results

[ccompass]

I have three tables: events, orderdetails & orders.

 

First I query orderdetails to find all the records that match the EventID:

 

$query1 = SELECT * FROM orderdetails WHERE EventID = $_SESSION['EventID'];

 

This returns 4 records.  These 4 records have a field called DetailOrderID which is the foreign key for orders.OrderID.  Next I need to query the results of the first query to find all the records in the orders table that match up.  For example:

 

SELECT * from orders where $query1.DetailOrderID = orders.OrderID.

 

How would I go about doing this?  I'm head down the temporary table solution but wanted to through this one out for discussion before I invest too much time.

[mikosiko]

and what about a simple JOIN between the tables?

[jcbones]

What is your full table structure?  I'll attempt to get something working for you without it, but this query may not work.

 

SELECT a.*,b.* FROM orderdetails AS a JOIN orders AS b ON a.DetailOrderID = b.OrderID

 

MySQL table JOIN syntax

[ccompass]

Here are the two tables:

 

orderdetails

-------------------------

DetailID (PK)

DetailOrderID

DetailProductID

EventID

DetailName

DetailPrice

DetailQuantity

 

orders

------------------------

OrderID (PK)

OrderAmount

OrderShipName

OrderShipAddress

OrderCity

OrderState

OrderZip

OrderPhone

OrderEmail

OrderDate

 

This code seems to work, but I don't know how to query the $result array.

 

$result = mysql_query("SELECT * FROM orderdetails WHERE EventID =" . $_GET['EventID']) or die(mysql_error());  

while($row = mysql_fetch_array($result)){
echo "DetailID: " . $row['DetailID'] . " | DetailOrderID: " . $row['DetailOrderID'] . "<br>";
}

 

 

 

[jcbones]

Did you try my query?

[ccompass]

I did not.  Pardon my ignorance but what do the 'a' and 'b' mean? :-\

[jcbones]

It is just table aliases, so I didn't have to type the names out every time.

[ccompass]

Figured it out:

 

SELECT * FROM orderdetails, orders WHERE orderdetails.EventID = ".$_SESSION['EventID']." AND orderdetails.DetailOrderID = orders.OrderID

 

Thanks all!