I'm pretty new to Joins so hope this all makes sense.
I'm joining 4 tables and want to create a while loop that spits out results nested under different categories.
My Tables
categories
id | category_name
pages
id | page_name | category
*page_content*
id | page_id | image_id
images
id | thumb_path
My current SQL join
<?php $all_photos = mysql_query("
SELECT * FROM categories JOIN pages ON pages.category = categories.id
JOIN image_pages ON image_pages.page_id = pages.id
JOIN images ON images.id = image_pages.image_id
");?>
The result I want from a while loop
I would like to get something like this....
Category 1
page 1
Image 1, image 2, image 3
page 2
Image 2, image 4
Category 2
page 3
image 1
page 4
image 1, image 2, image 3
I hope that makes sense.
Each image can fall under
multiple pages and each page can fall under
multiple categories.
at the moment I have 2 solutions, one which lists each category several times according to the the amount of pages inside them:
eg. category 1, page 1, image 1 - category 1, page 1, image 2 etc
One that uses a while loop inside another while loop inside another while loop, resulting in 3 sql queries.
<?php
while($all_page = mysql_fetch_array($all_pages)) {
?>
<p><?=$all_page['page_name']?></p>
<?php $all_images = mysql_query("
SELECT * FROM images JOIN image_pages ON image_pages.page_id = " . $all_page['id'] . " AND image_pages.image_id = images.id");
?>
<div class="admin-images-block clearfix">
<?php
while($all_image = mysql_fetch_array($all_images)) {
?>
<img src="<?=$all_image['thumb_path']?>" alt="<?=$all_image['title']?>"/>
<?php
}
?>
</div>
<?php
}
}
?
