Page 1 of 1

Grouping Results from Two MySQL Tables

Posted: Tue Jun 20, 2006 4:00 am
by maxd
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>';
    }
}
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]

Posted: Tue Jun 20, 2006 4:07 am
by JayBird
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.

Posted: Tue Jun 20, 2006 4:21 am
by Grim...
Use LEFT JOIN instead of INNER JOIN.

Getting closer

Posted: Tue Jun 20, 2006 11:16 am
by maxd
The JOIN query I'm modifying/using is from timvw's post:

viewtopic.php?t=27398&highlight=inner+join+cname+aname

Here's where my query and output stands, now, with LEFT JOIN implemented:

Code: Select all

// test of grouping display
$query = "SELECT sub3section.title AS sub3title, subsubsection.title AS subsubtitle FROM sub3section LEFT JOIN subsubsection ON sub3section.subsectionid=subsubsection.subsectionid  GROUP BY subsubsection.subsubsectionid ORDER BY subsubsection.rank ASC, sub3section.title ASC";
$result = mysql_query($query) or die(mysql_error());
$subsubsection = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($subsubsection != $row['subsubtitle']) {
    echo '<b>' . $row['subsubtitle'] . '</b><br>';
    $subsubsection = $row['subsubtitle'];
  }
  echo $row['sub3title'] . '<br>';
}
Unfortunately, the result displays the same "child" section within each "parent" section over and over, instead of only displaying the "child" with it's appropriate parent. In other words, I'm getting:

Section 1 Title
Section 1 subsection Title1

Section 2 Title
Section 1 subsection Title1

Section 3 Title
Section 1 subsection Title1

etc...

Instead of:

Section 1 Title
Section 1 subsection Title1

Section 2 Title

Section 3 Title
Section 3 subsection Title1
Section 3 subsection Title2

Any ideas of where I might change the code to fix this?

Thanks,
Max

Posted: Tue Jun 20, 2006 11:48 am
by RobertGonzalez
Why not two queries, then two separate loops (one within the other)? That has always worked for me.

Query 1 returns all sections, query 2 returns all subsections. Loop one loops through the sections, then while looping through the sections, run a second loop within each section, outputting the subsections that match the sections.

Two queries instead of God know how many, and a nice, clean segment of code.

Re: Two queries instead of one

Posted: Tue Jun 20, 2006 3:29 pm
by maxd
Yeah, that's pretty much the only way I've been able to get the desired result (see my first post). Is there any drawback to this approach in terms of efficiency? I'm thinking if you were to have a long list of "parent" sections, each with "child" sections (for example, in a site map), and maybe even "child2" sections under the "child" sections, you'd be running a whole bunch of sql queries over and over. '8O'

Any DB experts have any comment on this?

thanks,
max

Posted: Tue Jun 20, 2006 6:09 pm
by RobertGonzalez
Pretty simple thought... Would you rather process two queries or 500? Looping is fast in PHP. Matching is fast in PHP. But looping, contacting the database for each iteration, error checking each iteration, returning values for each iteration, etc, is in my opinion, entirely too inefficient and uses way to much resource.

Posted: Tue Jun 20, 2006 6:14 pm
by Robert Plank
You're doing the left join backwards.

Either switch the sub3section and subsection, or keep what you have and make it a right join instead.

The way I would do it is with one joined query, then store the contents of the previous row in some variable like $lastRow. If the previous row is null (meaning this is the first row we are getting) or the previous row's subsectionid is different from the current row, echo the bolded text with the subsection name.

IT'S ALIVE!

Posted: Tue Jun 20, 2006 7:32 pm
by maxd
Wow. I can't believe I actually got this to work.

Thanks for the tip on the JOIN being backwards, Robert, and on using LEFT JOIN instead of INNER JOIN, Grim. I also realized I needed to introduce a WHERE clause to eliminate picking up unwanted "Parent" categories, which was the final hurdle.

Here's how the code ended up:

Code: Select all

// test of grouping display
$query = "
SELECT subsubsection.title AS subsubtitle, sub3section.title AS sub3title
FROM subsubsection 
LEFT JOIN sub3section 
ON subsubsection.subsubsectionid = sub3section.subsubsectionid
WHERE subsubsection.subsectionid = $CurrSubsection
";
$result = mysql_query($query) or die(mysql_error());
$category = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($category != $row['subsubtitle']) {
    echo '<b>' . $row['subsubtitle'] . '</b><br>';
    $category = $row['subsubtitle'];
  }
  echo $row['sub3title'] . '<br>';
}
And the result:

Deputy Bureau Director

Associate Director of Operations
MAX BIA Sub Page
FINAL test link
Associate Director of Corrections

Associate Director of Training

Associate Director of Professional Standards

U.S. Indian Police Academy

U.S. Indian Police Academy Training Program Schedule

Regional Training Programs

PERFECT! Man, I am so happy.

Hopefully, some poor stumbler like myself will be able to pick this up and re-booger it to meet their needs. I think it will come in very handy for building dynamic site maps and for eliminating repetitive SQL queries.

THANKS EVERYONE!
max