I am trying to draw data from three tables to create a report using mySQL and PHP.
Here are my tables and the pertinent fields:
PRODUCTS
fields: PRODUCT_ID, TITLE, COST, VENDOR_ID
VENDORS
fields: VENDOR_ID, VENDOR_NAME
ORDERS
fields: PO_NUM, VENDOR_ID, PRODUCT_ID, QTY_ORDERED
There may be several records in the Orders table with the same PO_NUM. I have been able to generate a list of all PO_NUMs with each number linking to a page which should display the detail of the PO.
Unfortunately, I can't get the detail to show up.
I want to display the PO_NUM, the VENDOR_NAME, the PRODUCT_ID, PRODUCT_NAME, QTY_ORDERED, COST, and then an extended price field which is the result of QTY_ORDERED * COST.
Any suggestions how this could be accomplished? I'm stumped.
JOINING TABLES
Moderator: General Moderators
Since you state that the orders table can have duplicate PO_NUMs, we'll assume that we need to handle multiple displays of a record detail on the same page.
Since cost is in a different table than the detail data, you must us a join query.
Here is how you do that.
Hope this helps.
Since cost is in a different table than the detail data, you must us a join query.
Here is how you do that.
Code: Select all
// You mentioned link, so I'll assume you are passing poNum in a hyperlink.
$query="select orders.PO_NUM, orders.VENDOR_ID, orders.PRODUCT_ID, orders.QTY_ORDERED, products.cost from products, orders where products.PO_NUM='$_GETїpoNum]'";
$result=mysql_query($query);
$numRows=@mysql_query($result);
// using a for loop allows for displaying multiple matches on PO_NUM
for($i=0;$i<$numRows;$i++){
$record=@mysql_fetch_object($result);
// calculate cost of all items ordered.
$cost=$record->QTY_ORDERED * $record->COST;
// you can build out the display now using the $record object and the $cost variable.
}