1

I'm trying to pull multiple rows from different tables in a database.

If I only have the first 3 rows from two tables, it works fine. As soon as I add the third table and try and pull another row it breaks!

Ultimately what I'm trying to do is pull the image, the dayid, the name of a person, and then name of a cause.

The imageURL and the Outfitday_id are in the same table, the name of the person is in the table Pilot, and the name of the cause is in the table cause.

For some reason it will pull the first two rows (imageUrl, outfitDay_id) from the table Outfitimage, then pull the 3rd row (name) from Pilot, but fails if I add the name and try and pull it from the Cause table.

$link = mysql_connect("host","user","pass");
if ($link) {
   mysql_selectdb("up",$link);  
   // Select records from the DB  
   $query  = "SELECT imageUrl,outfitDay_id,name,name FROM OutfitImage,Pilot,Cause ORDER BY Rand(" . date("Ymd") . ") LIMIT 1";
   $image = mysql_query($query);

   // Display records from the table  
   echo "";
   while ($row = mysql_fetch_array($image, MYSQL_NUM)) {
      echo "<IMAGE SOURCE='$row[0]'/><br>";
      echo "<div id='info'>Day $row[1] of ";
      echo "$row[2] Uniform Project for$row[3] </div>";
   }
   echo "";
} else {
    echo "Can't connect to the database!";
}
netcoder
  • 66,435
  • 19
  • 125
  • 142
Robert C Edwards
  • 708
  • 2
  • 8
  • 17
  • 1
    Why, I just wrote a really lengthy question and answer that covers this exact [sort of question](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database! – Fluffeh Sep 18 '12 at 14:05
  • Awesome! Thanks for commenting on this thread…that was a great write-up. – Robert C Edwards Sep 18 '12 at 18:31

2 Answers2

1

It probably happens because the name column is ambiguous. I'm guessing you have a name column in both the Cause and Pilot (or some other table). MySQL can't guess what table you want to retrieve the column value from, and will spit an error (Column name in field list is ambiguous).

Make sure you prefix the column name with the table name to remove ambiguity:

SELECT 
   imageUrl, outfitDay_id, Cause.name, Pilot.name 
FROM 
   OutfitImage, Pilot, Cause
netcoder
  • 66,435
  • 19
  • 125
  • 142
  • $query = "SELECT OutfitImage.imageUrl,OutfitImage.outfitDay_id,Pilot.name,Cause.name FROM OutfitImage,Pilot,Cause ORDER BY Rand(" . date("Ymd") . ") LIMIT 1"; – Robert C Edwards Mar 31 '11 at 03:25
0

You are attempting a cross-join across all three tables, but you aren't creating any conditions to filter them out. The clause FROM table1, table2, table3 attempts a cross-join across all three tables. A cross-join means every row from one table is paired up with every row from another table. For example, say I have three tables, each have two rows. The first table I call names with the two rows jack and jill, the second table I will call ages with rows 21 and 24, the third table will be genders, and have rows male and female. If I do a clause FROM names, ages, genders, it will return a table with 2 X 2 X 2 = 8 rows.

names ages genders
jack  21   male
jack  21   female
jack  24   male
jack  24   female
jill  21   male
jill  21   female
jill  24   male
jill  24   female

Normally, when you cross-join, you also add a condition to WHERE to filter out rows that don't make sense. For example, from example FROM clause you might have a WHERE genders.id = names.genders_id. In this case, you are linking a name with a specific gender row from the genders table. This will probably also solve your need for a LIMIT 1.

penguin359
  • 1,289
  • 13
  • 26