Cant get this SQL query correct

benf posted this at 10:12 — 22nd October 2008.

Joined: Feb 2005

So i need help with my SQL.

I am building an image gallery and have 3 tables, imageGallery (details of the images), imageCat(category details), phpbb_users(users details)

If there is no "catid" (category selected) passed in the URL i can show all ads:

$sql1 = 'SELECT * FROM
		   imageGallery,
		   imageCat,
		   phpbb_users
	          WHERE 
		    imageGallery.catid = imageCat.ID
		  AND
		    imageGallery.userID = phpbb_users.user_id';

The above is fine.

If a category is selected i pass the catid in the URL ($catid)

	$sql1 = 'SELECT * FROM
		   imageGallery,
		   imageCat,
		   phpbb_users
		WHERE 
		   imageGallery.catid = '.$db->sql_escape($catid).'
		AND
		   phpbb_users.user_id = imageGallery.userID';

Things are not right with the above. When i print the category name under the image it is incorrect and it is printing out more than one image of the same image.

Basically none of the details are correct.

Can anyone help.

Good Value Professional VPS Hosting

pr0gr4mm3r posted this at 14:22 — 22nd October 2008.

He has: 1,502 posts

Joined: Sep 2006

Although the tables are not being joined correctly, this should work with the method you are using:

$sql1 = 'SELECT * FROM
   imageGallery,
   imageCat,
   phpbb_users
          WHERE
    imageGallery.catid = imageCat.ID
  AND
    imageGallery.userID = phpbb_users.user_id'
  AND
    imageGallery.catid = '.$db->sql_escape($catid).';

Joining the table using WHERE clauses is not the proper way to do it, though. Try something like this:

$sql1 = 'SELECT * FROM
   imageGallery
  INNER JOIN imageCat ON imageCat.ID = imageGallery.catid
  INNER JOIN phpbb_users ON phpbb_users.user_id = imageGallery.userID
  WHERE
    imageGallery.catid = '.$db->sql_escape($catid).';

benf posted this at 19:44 — 22nd October 2008.

They have: 426 posts

Joined: Feb 2005

Hello pr0gr4mm3r you always answer my questions promptly - appreciated.

It works perfectly.

pr0gr4mm3r posted this at 21:11 — 22nd October 2008.

He has: 1,502 posts

Joined: Sep 2006

Glad to help.