SQL help
I have 2 tables. One that stores the paths to my images and the other table that stores the category details.
In my images table each row has a reference to a category ID.
While i loop through my images table to display the images i want to display the category that it is in.
I cannot figure out how i can do this with one query - with out having to run a query for each image.
For example below is where i build an array with all the image information:
if(mysql_num_rows($result) >0){
while($row = $db->sql_fetchrow($result)){
$myimages[] = array(
"id"=>$row['ID'],
"userid"=>$row['userID'],
"catid"=>$row['catid'],
"title"=>$row['title'],
"desc"=>$row['desc'],
"tag"=>$row['tag'],
"path"=>$row['path'],
"price"=>$row['price'],
"paypal"=>$row['paypal']
);
}
return $myimages;
exit;
}How can i get the category name from the category tabel without having to query each time i get a row from the images table (in this while loop)
teammatt3 posted this at 18:28 — 20th October 2008.
He has: 2,102 posts
Joined: Sep 2003
You'd want to use an inner join.
Something like:
SELECT * FROM Image INNER JOIN Category ON Image.CategoryID = Category.CategoryID
You select everything from table Image, and then JOIN it with Category where the Image's CategoryID matches one in the Category table.
pr0gr4mm3r posted this at 18:29 — 20th October 2008.
He has: 1,502 posts
Joined: Sep 2006
You need to join your tables.
So the query would be something like:
SELECT * FROM Pictures INNER JOIN Categories ON Pictures.catid = Categories.catidNow all values from your category table will be in the $row variable in your loop.
pr0gr4mm3r posted this at 18:30 — 20th October 2008.
He has: 1,502 posts
Joined: Sep 2006
Darn it, snipped by Matt.
benf posted this at 21:13 — 21st October 2008.
They have: 426 posts
Joined: Feb 2005
Thanks, works.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.