Grouping Results from Two MySQL Tables
Posted: Tue Jun 20, 2006 4:00 am
Pimptastic | Please use
So, that's the only way I could get it to work. I tried a great INNER JOIN from another post, but unfortunately, not every "parent" section in my table has a corresponding "child" section in the 2nd table, and I found the INNER JOIN was eliminating any records which didn't have a "child".
If anyone can point me in a more efficient direction, I'm much obliged.
Thanks,
Max
Pimptastic | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I reviewed and tested numerous INNER JOIN and GROUPING posts, and I actually have come up with code that works, but I know there's a better way!
I've got 2 tables which contain data about sectional navigation. I want the navigation to display like this:
[b]Subsubsection Title1[/b]
Sub3section Title1
Sub3section Title2
[b]Subsubsection Title2[/b]
[b]Subsubsection Title3[/b]
Sub3section Title1
Sub3section Title2
The only way I could figure to make my navigation display properly was to run one query to get the main sections, then during the display loop, run queries against the 2nd table to pull the appropriate records for the main section being displayed.
If you look at my code, you'll probably be able to ascertain what I mean:Code: Select all
// first query to get main sections
$query = "SELECT subsubsectionid,title FROM subsubsection WHERE subsectionid=$CurrSubsection ORDER BY subsubsection.rank";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
$myCurrentSubSub = $row['subsubsectionid'];
echo '<b>' . $row['title'] . '</b><br>';
//2nd query to pull related subsections from 2nd table
$query2 = "SELECT sub3sectionid,title FROM sub3section WHERE subsubsectionid=$myCurrentSubSub ORDER BY sub3section.title";
$result2 = mysql_query($query2) or die(mysql_error());
while ($row = mysql_fetch_assoc($result2)) {
echo '<i>' . $row['title'] . '</i><br>';
}
}If anyone can point me in a more efficient direction, I'm much obliged.
Thanks,
Max
Pimptastic | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]