Following up on this question, there is problem with the standard approach or a recursive approach in the case below.
For instance, I want to return the page with the same parent_id and some pages have multiple row contents,
page table
page_id page_title parent_id
1 a 1
2 aa 1
3 ab 1
4 ac 1
content table
content_id content_text
1 text one
2 text two
3 text one aa
4 text two aa
5 text one ab
6 text one ac
content structure table
page_id content_id order_in_page
1 1 1
1 2 2
2 3 1
2 4 2
3 5 1
4 6 1
The standard approach,
SELECT
p.*,
c.*,
x.*
FROM pages AS p
LEFT JOIN pages_structures AS x
ON x.page_id = p.page_id
LEFT JOIN pages_contents AS c
ON c.content_id = x.content_id
WHERE p.parent_id = '1'
AND p.page_id != '1'
result (it lists the row as 4 items),
page_id page_title parent_id content_text order_in_page
2 aa 1 text one aa 1
2 aa 1 text two aa 2
3 ab 1 text one ab 1
4 ac 1 text one ac 1
As you can notice that there are two rows with page_id 2 and one row for each 3 and 4. How do you display the data into HTML with the standard approach like below (as suggested in one of the answer in the previous question)?
echo $page[0]['page_title'];
echo $page[0]['content_text'];
But with set-based one, I can do it with this,
SELECT
page_id,
page_title,
MAX(IF(order_in_page = 1, content_text, NULL)) AS content_1,
MAX(IF(order_in_page = 2, content_text, NULL)) AS content_2,
.
.
.
FROM
pages AS p LEFT JOIN
pages_structures AS x ON x.page_id = p.page_id LEFT JOIN
pages_contents AS c ON c.content_id = x.content_id
WHERE
p.parent_id = '1'
AND
p.page_id != '1'
GROUP BY page_id
in PHP,
foreach($items as $item)
{
echo '<li><h3>'.$item['page_title'].'</h3>';
echo '<p>'.$item['content_1'].'</p>';
echo '<p>'.$item['content_2'].'</p></li>';
}
the HTML (it lists the row as 3 items which is correct),
<li>
<h3>aa</h3>
<p>text one aa</p>
<p>text two aa</p>
</li>
<li>
<h3>ab</h3>
<p>text one ab</p>
<p></p>
</li>
<li>
<h3>ac</h3>
<p>text one ac</p>
<p></p>
</li>
Hope this makes sense!
