Query multiple MySQL tables with PHP

[jc_gmk]

Is there an easy way to query multiple tables using PHP?

an example of what i'm trying to do:

Table 1 contains: Product_id & Product_name - among many others
Table 2 contains: Product_id & Product_price - among many others

Notice the Product_id is the same so I can reference them together.


If I then want to output a table like this:

Product_id | Product_ name | Product_price

What query would I use?

[boogyman]

SELECT t1.Product_name, t2.Product_price FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON t1.Product_id = t2.Product_id WHERE Product_id = '___';

[jc_gmk]

It doesn't seem to be working.

Where you put t1 and t2, do I leave those as they are, or do I change them to the table name?

I've changed "Table 1" & "Table 2" to my table names

[boogyman]

post your query and any errors that you get please. I have a feeling that its just a minor mistake that you have made somewhere

no you do not "need" to change the t1 / t2 however if you do change those then you would need to update accordingly throughout that query, also you cannot have any spaces in the table names.

[jc_gmk]

Thanks,

the query i'm using is:

$sql = "SELECT t1.NAME, t2.PRICE FROM product AS t1 LEFT JOIN productprogram AS t2 ON t1.PRODUCTID = t2.PRODUCTID WHERE PRODUCTID = '$id'";

The error message is:

Notice: Query failed: Column 'PRODUCTID' in where clause is ambiguous SQL: SELECT t1.NAME, t2.PRICE FROM product AS t1 LEFT JOIN productprogram AS t2 ON t1.PRODUCTID = t2.PRODUCTID WHERE PRODUCTID = 'Y6UJ9A00000A' in K:\My Documents\Websites\...\mysql.class.php on line 109

[jc_gmk]

Also, it seems to me that using a 'JOIN' creates a temporary table?

As both my tables have over 130,000 entries do you think there will be any performance issues?

[boogyman]

Thanks,

the query i'm using is:

$sql = "SELECT t1.NAME, t2.PRICE FROM product AS t1 LEFT JOIN productprogram AS t2 ON t1.PRODUCTID = t2.PRODUCTID WHERE PRODUCTID = '$id'";

The error message is:

Notice: Query failed: Column 'PRODUCTID' in where clause is ambiguous SQL: SELECT t1.NAME, t2.PRICE FROM product AS t1 LEFT JOIN productprogram AS t2 ON t1.PRODUCTID = t2.PRODUCTID WHERE PRODUCTID = 'Y6UJ9A00000A' in K:\My Documents\Websites\...\mysql.class.php on line 109

oops sorry, I forgot that you need to add the table in the where since its referencing the same in both. but t1 and t2 are also really not helping at all, so try this instead

$sql = "SELECT p.NAME, pp.PRICE FROM product AS p LEFT JOIN productprogram AS pp ON p.PRODUCTID = pp.PRODUCTID WHERE p.PRODUCTID = 'Y6UJ9A00000A'; 

Also, it seems to me that using a 'JOIN' creates a temporary table?

As both my tables have over 130,000 entries do you think there will be any performance issues?

yes it will create a temporary table, there are a bunch of different types of joins that you could use, however for this instance I believe that a LEFT JOIN would be the easiest. it is grabbing the records from both tables and putting them into the same table side by side, then it grabs the fields you requested. this is actually the fastest option you have, so if you have any performance issues it will not be because of the query as you have described.

sorry for the confusion and let us know if you need any more help

[jc_gmk]

Works a treat, thanks very much!

[djr33]

You could test performance using PHP instead.

Query both tables then join the resulting arrays.

$r1 = mysql_query(1);
$r2 = mysql_query(2);
$n=0;
while ($row = mysql_fetch_array($r1)) {
$out[$n]=$row;
$n++;
}
while ($row = mysql_fetch_array($r2)) {
$out[$n]=$row;
$n++;
} 

You can also use array_merge if you did it in a different order.

[jc_gmk]

Following on from my problem; i used the query you gave me:

$sql = "SELECT p.NAME, pp.PRICE FROM product AS p LEFT JOIN productprogram AS pp ON p.PRODUCTID = pp.PRODUCTID WHERE p.PRODUCTID = '$id'; 

It does select info from both tables but only from the first entry!

It's like it is ignoring the 'WHERE' altogether and just selecting the first entry it comes across.

However if I remove the 'WHERE' altogether it just crashes my browser, probably because its trying to fetch all 130,000 entries.

Any ideas?