getting data from multiple tables

[Anon]

Hi,

I have two tables. I want to get all the data from both tables returned to me for display on a webpage. The code looks like this:

$tables = "table1, table2";
$result = mysql_query("SELECT * FROM $tables",$db);

or like this:

$tables = "table1 JOIN table2";
$result = mysql_query("SELECT * FROM $tables",$db);

However, the dataset I receive is not an array filled with the records from table1 followed by the records from table2, as expected. Instead, the dataset consists of the records from table1, repeated by the multiple of the number of records in table2. So if there are five records in table2, my dataset is all the records from table1, repeated five times.

I am testing this on MySQL 3.23.44.

Thanks for your help; I'm new to this and need all the help I can get.

-Michael

[[deleted]]

"However, the dataset I receive is not an array filled with the records from table1 followed by the records from table2, as expected."

That's because a JOIN does not do that. What you describe is done using a UNION which mysql 3.23.44 does not support:

SELECT FROM table1
UNION
SELECT FROM table2

A JOIN will link two tables together based on their content, because the second table has one column that references values from a column in the first table.

If you don't tell SQL how to link the two tables, it will link every record from table1 with every record from table2, generating talbe1 x table2 = maaaaany records.
This is called a cartesian product, and is one of the best ways to bring a database server to it's knees. (imagine doing this with three tables that have one million rows each, 1Mx1Mx1M = 1000000000000000000 records)