Displaying Data From Two Recordsets

I’m new to PHP. I’m using DW CS5, MySQL on a Linux Server. Here’s what I’m trying to do:

I have two recordsets 1) OrderInfo 2) orderDetails. orderInfo contains general information for the oder in the DB (i.e. total price, approval code, etc.). orderDetails contains one or more products from any given order, linked by the orderID from the orderInfo table.It contains one record per item ordered during the order.

I’m building an HTML table that displays each record from the orderDetails page. About half the data is pulled from each database table. How do I go about doing this?

For example: see attached image of table and data source.

I am assuming the data is related.

Two easy ways.

  1. Create a sql statement “joining” the tables. DW’s will use the results as one record set. More info about Joins @ http://www.tizag.com/mysqlTutorial/mysqljoins.php

  2. Create a “View” in the database. DW can use that View as the source for the record set. A view is sort of like a temp table maintained by the database. More info about views @ http://techonthenet.com/sql/views.php

I love views myself. Any apps that can connect to the database can use/see the view.

If you love views you should be using Oracle not MySQL.

wha??!!! :eek:

justification, please

I do use Oracle. I use MySQL too. You do know MySQL supports views right?

Thank you for pointing me in the right direction. Based on the links provided by Crag, I was able to create the MySQL Join. While I was able to save the resulting Join into an array and display the entire contents, I’m unable to figure out how to populate my HTML table with the data from the Join:

What you see displayed is the data from the neoOrderDetails table. I need to populate the missing pieces of data from the neoOrders table. The very first column contains the foreign key order ID in the neoOrders table (which is the index ID in the neoOder table).

Here is the code for my Join:

$query = "SELECT neoOrder.*, neoOrderDetails.* ".
 "FROM neoOrder, neoOrderDetails ".
	"WHERE neoOrder.ID = neoOrderDetails.orderID";
$result = mysql_query($query) or die(mysql_error());

I figured it out! I was not treating the new join table as its own ‘recordset’. I was able to get past my block after taking a break and thinking over the logic. I’m beginning to really like PHP!