Page 1 of 1

JOINING TABLES

Posted: Mon Oct 14, 2002 11:07 pm
by lloydsmods
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.

Posted: Tue Oct 15, 2002 1:10 am
by sfhc
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.

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.

}
Hope this helps.