Grouping Results from Two MySQL Tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Grouping Results from Two MySQL Tables

Post 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]
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Use LEFT JOIN instead of INNER JOIN.
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Getting closer

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

Re: Two queries instead of one

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Robert Plank
Forum Contributor
Posts: 110
Joined: Sun Dec 26, 2004 9:04 pm
Contact:

Post 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.
maxd
Forum Commoner
Posts: 41
Joined: Sun Dec 04, 2005 12:12 am
Location: Denver

IT'S ALIVE!

Post 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
Post Reply