mysql_fetch... with mutiple table SQL query.

Mikeybob

Hey guys,

I'm trying to draw a record from a table which has several records from which the user can choose. This choice is made on a separate table.

The SQL (I thought) was pretty simple:

SELECT ID, imgName, choice
FROM tbl_images, tbl_imagechoice
WHERE tbl_images.ID = tbl_imagechoice.choice

Is there a special PHP function that deals with multiple table SQL queries because I keep getting the "mysql_fetch_object(): supplied argument is not a valid MySQL result resource" error.

The PHP is: -

[PHP]
//Connection Parameters
include 'connections/connection.php';
//Connect
mysql_connect($hostname, $username, $password) or die ("Could not connect to DB server!");
//Select
mysql_select_db($database) or die ("Could not select the database!");

//Fetch the image from tbl_images that has been selected in tbl_imagechoice
$show_images = 'SELECT ID, imgName, choice FROM tbl_images, tbl_imagechoice WHERE tbl_images.ID = tbl_imagechoice.choice';
$result = mysql_query($show_images);

$row = mysql_fetch_object($result);
echo "<img src=\"images/$row->imgName\" alt=\"Title\" />";
[/PHP]

Thanks for your time. .

Mikeybob

OK, I can get it working if I either change the SELECT line to just 'SELECT *', or if I change the field names of the database. So it looks like the 'ID' is conflicting somehow as both tables have the same name for the field.

At the moment, the asterisk will do, but in the future, if I ever had any bigger tables, is there something I can do to solve this problem?

Kwitko

Yes, use table aliases:

SELECT a.ID, b.ID FROM TABLEA a, TABLEB b