SQL Problem...seems very complicated to me!

[Sephirangel]

Right, i have 2 tables in my database. The structures for them are below:

 

sales(Sales_Num, Date, Time, BuyerID, PostPacking, Payment_Method, PaypalFee, Sent)

 

saleitems(SaleItem_Num, Sales_Num, ItemCode, SaleQuantity, PricePerItem)

 

 

The 'sales' table holds the main details of each sale and the 'saleitems' holds the items that are present in each sale. Obviously I have done this as their can be an infinite number of items in each sale. The 'Sales_Num' in sales is the same as in saleitems, linking the item records in 'saleitems' and 'sales'.

 

The problem: I want to be able to send an email automatically each day (on first log in) of all the sales from the previous day. I have created the following SQL statement which returns with the records i want but, how would i go about seperating each of the records out.

 

For instance, if i had a sale with more than one item, mysql_fetch_assoc() will only give me 1 of the items when i use print_r().

Also if possible, ill be using this to send multiple sales in one email so i will need to get the different sales and saleitems grouped as well!

 

I hope all of that made sense! Here is the SQL statement im currently using;

 

//where $yesterday is yesterdays date
"SELECT * FROM sales, saleitems WHERE sales.Sales_Num = saleitems.Sales_Num AND sales.Sent = '0' AND sales.Date = ' " . $yesterday . " ' "