[RESOLVED] fetching data from 2 tables at once

ffunit

well the name says it all really. Does anyone know how that could be done? Ok i have 3 tables in the same database and i need to get the info out of them using their id, it is supposed that the info in each table is linked according to the id so if i ..... SELECT *FROM table WHERE id='3' ..... for all the tables then i get the total info for a specific thing. the trouble is using the $query for 3 different tables. I assume that the compiler would be confused as to which table is to be dealt with. can anyone help?

NogDog

You'll want to do a "join".
General info from Wikipedia
Specific info from the MySQL manual

ffunit

$query = "SELECT salesa.product, salesb.dll, pictures.adress ".
"FROM salesa, salesb, pictures ".
"WHERE salesa.id = salesb.id = pictures.id ='1' ";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
$row = mysql_fetch_array($result);
echo $row['product']. " - $". $row['dll']." - ". $row['adress'];
echo "<br />";

ok that is the simple code i have so far but the problem is i need to show all the info in every column for the 3 tables for the row that corresponds to id = 1 but with all my trials i just can't make it happen. I am only able to select from a specific column from each table can anyone
help me with this? i need to get all the info from all the columns for a specific row?

laserlight

I suggest that you tell us what is your relevant database schema, and what exactly are you trying to select.

NogDog

$query = "
SELECT
   sa.product,
   sa.col2,
   sa.col3,
   sb.dll,
   sb.col4,
   sb.col5,
   pic.address,
   pic.col6,
   pic.col7
FROM salesa AS sa
   INNER JOIN salesb AS sb ON sa.id = sb.id
   INNER JOIN pictures AS pic ON sa.id = pic.id
WHERE sa.id = 1
";

ffunit

yea NogDog that is what i just did well close to it anyway without the JOin syntax though.

$query = "SELECT * FROM salesa, salesb, pictures
WHERE salesa.id = salesb.id = pictures.id ='1' ";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
$row = mysql_fetch_array($result);
echo $row['product']. " - $". $row['dll'].".". $row['cen']." - ".
$row['cna']." - ".$row['adress'];
echo "<br />";

I realized I could just use a simple SELECT syntax selecting from the 3 tables equaling the id's with the WHERE syntax and echoing the rows i needed echoed. I had to divide my table into 3 because apparently mysql doesn't allow tables with the total number of characters in the names of the column more than a certain number so i had to make the names smaller and divide my tables into 3, Before splitting them i had a total number of 12 columns.