Query multiple MySQL tables with PHP

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?

Code:

---

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 = '___';

---

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

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.

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

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?

Quote:

---

Originally Posted by 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

---

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

PHP Code:

---

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


---

Quote:

---

Originally Posted by 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?

---

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

Works a treat, thanks very much! :)

You could test performance using PHP instead.

Query both tables then join the resulting arrays.

PHP Code:

---

$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.

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

PHP Code:

---

$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?

Quote:

---

SELECT p.NAME, pp.PRICE FROM

---

you have only asked it to select the name and the price, if you need something else selected you need to add them to the select

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

it seems there WHERE has no effect on which entry it selects
Whether I do the above or change it to:

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

It still only selects the first record in the table.

do you have some example output? or a site that can link us to an example?

also print out the exact query that it is generating. that may help in understanding why its not returning the full result set

the other thing would be to go into your SQL manager, and try to run the query and see what is returned.

if there are any errors produced please post those as well.

another thing to test is to do a query like

Code:

---

SELECT p.*, pp.* FROM product AS p LEFT JOIN productprogram AS pp ON p.PRODUCTID = pp.PRODUCTID WHERE p.PRODUCTID = '$id';

---

and plug in some actual values for the $id. that query should return every field that describe each record (data entry)

I have finally cracked it!
Thanks very much for all your help!
:)

congrats, what ended up being the problem?

not entirely sure but I did as you suggested and ran the query through my SQL manager, that didn't work but I was able to change the code easily from there.

ended up changing it to this:

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

I don't know if it's better to have aliases or not but once I used this code everything worked perfectly!

Thanks again for your help! Much appriciated.
:)