[RESOLVED] Joining multiple tables

retro

I hope this is the right place to put this!

I have a MySQL 4 database with 3 tables: orders, persons and products. These have fields as follows:

orders
ID
customer
date
Item1
Item2
(etc... there are 10 items)

products
ID
Item
Price
Category
SortOrder

persons
FirstName
Lastname
Middlename
City
Street
ZIPCode
Country
CellPhone
Homephone
Officephone
Email
Homepage

I am using the following query. Its purpose is to filter the results so we only get the orders that are to be delivered on a particular day of the week chosen from a calendar (i.e. $DelDate).

$result = mysql_query ("SELECT * FROM orders LEFT JOIN persons ON (persons.ID = orders.customer) WHERE DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . ')");

With this, I have a list of the items required and which customer they are for.

I am using this code:

if($total>0) {

$i = "Item";
// fetch the current row into the array $row
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
// print table header
echo("<table cellspacing=\"0\" cellpadding=\"0\" width=\"500\" class=\"edittable\">");
echo("<tr><td>" . $row["FirstName"] . " " . $row["Lastname"] . "</td></tr>");
for($j=1; $j<=10; $j++) 
{
if ($row[$i.$j]>0)
{
echo("<tr><td>" . $row[$i.$j] . "x Item" . $j . "</td></tr>");
}
}
echo("<tr><td>" . $row["date"] . "</td></tr>");
echo("</table>");
echo("<br /><br />");

}
}

This outputs only the items where the quantity is greater than 0, so the result is something like:

Joe Bloggs
1x Item1
2x Item3
4x Item6
2008-05-08

However, I want the quantities to state what product it is, for example 2x Oranges. For this, I need it to look up the "Item" value from the "products" table.

Am I able to modify my query so that I can do this?

Thanks in advance!

laserlight

Instead of having Item1, Item2, ..., Item10, have an items table with a foreign key to the orders table, another foreign key to the products table, and a column for the quantity.

retro

Ah, I forgot to mention, I can't have a foreign key, the database is MyISAM

laserlight

Ah, I forgot to mention, I can't have a foreign key, the database is MyISAM

You can always have foreign keys, just that in some cases you cannot enforce foreign key constraints.

retro

Hmm. Well, I'm a novice when it comes to MySQL and PhpMyAdmin. How do I create a foreign key?

I've only got tonight to get this done, and this isn't the only thing I have to sort out.. I don't really think I have enough time to learn something new and change everything. Am I able to use a second JOIN on my query?

I appeciate what you're saying, and I'll certainly look into it and most likely convert to that in the future when I don't have such limited time constraints. As it stands, it works, so I'd rather stick with that for now, so I can show my friend something that is working.

laserlight

How do I create a foreign key?

Something like this:

CREATE TABLE items (
    order_id INT NOT NULL REFERENCES orders(ID),
    products_id INT NOT NULL REFERENCES products(ID),
    quantity INT,
    PRIMARY KEY (order_id, products_id)
);

Of course, since foreign key constraints are not enforced the REFERENCES keyword has no effect.

Am I able to use a second JOIN on my query?

I am not sure what to suggest for the join since there is nothing that relates the orders table with the products table. The Item1, Item2, ..., Item10 columns in the orders table contain quantities, right? What does the Item column in the products table contain? How are these two tables related?

retro

The relation should be with the ID field in products, I guess.

ID	Item
1	Extra Small Box
2	Small Box
3	Medium Box
4	Large Box
5	Half Dozen Eggs
6	Green Streaky Bacon
7	Smoked Streaky Bacon
8	Green Back Bacon
9	Smoked Back Bacon
10	Flowers

So where I'm using:

"x Item" . $j .

To print Item1 (or whichever item $j is on in the loop), I want to be able to look up 'Item' in 'products' where 'ID' (in 'products') = $j, if that makes sense.

laserlight

Oh, in that case you probably have no choice but to simulate the join in PHP. That is, you retrieve the Item column from the products table then do the substitution of the orders column name with the Item name in PHP.

retro

OK, I'll give it a go. Thanks!

retro

Yup, that worked fine. Here's what I did:

$query = mysql_real_escape_string("SELECT ID,Item FROM products ORDER BY ID");
$result2 = mysql_query ($query);

//create array 
$c = 0;
$itemNo = Array();

while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
  {
  $itemNo[$c] = Array($c,$row["Item"]);
  $c++;
  }

I then changed the line in the for loop to:

echo("<tr><td>" . $row[$i.$j] . "x " . $itemNo[$j - 1][1] . "</td></tr>");

Thanks again!!