[RESOLVED] Help with pulling from MySQL using Left Joins

[blckspder]

Hello,
I am trying to create a navigation column for a website that pulls from a database.

The column is broken down into categories with the pages listed underneath each.

The links should look like this:

• Ultrasonic Spray Nozzles
  This is test one
  This is test two
• Precision Sprayers
  Precision Spray Test1

But this is what is showing up using the Query I have:

• Ultrasonic Spray Nozzles
  Ultrasonic Spray Nozzles
  This is test one

• Precision Sprayers
  Precision Sprayers
  Precision Spray Test1
  This is test two

notice "This is test two" should be under ultrasonic spray nozzles and ultrasonic spray nozzles and precision sprayers should only be showing once.

Here is my code:

			<?php
			$proQuery = "SELECT DISTINCT(subcat.title), maincat.*, pagepos.*, pagemain.* FROM subcat LEFT JOIN maincat ON subcat.catKey=maincat.catKey LEFT JOIN pagepos ON subcat.subKey=pagepos.subKey LEFT JOIN pagemain ON subcat.pageKey=pagemain.pageKey WHERE maincat.catTitle='Electronics' AND pagepos.type='Products' ORDER BY pagepos.position ASC";
			$runPro = mysql_query($proQuery);


		while($nav = mysql_fetch_array($runPro)) {
			if(str_replace("_", " ", $nav['title']) == $nav['Title']) {
		?>
				<p>- <a href="splashelectronics.php?cat=<?php print $nav['catTitle']; ?>&amp;subCat=<?php print $nav['title']; ?>&amp;page=<?php print $nav['pageKey']; ?>&amp;pageTitle=<?php print $nav['Title']; ?>&amp;flag=SubCategory" class="blue"><?php print str_replace("_", " ", $nav['title']); ?></a></p>
		<?php
				}
		?>
			<div style="font-size: 10px; font-weight: normal; padding-left: 25px;">
		<?php
					print $nav['Title'] .'<br />';
			?>
			</div>
		<?php
			}
		?>

If anyone can help I would really appreciate it.

[TobyRT]

Have a look at this thread - I think it might help.

http://phpbuilder.com/board/showthread.php?t=10333076

[ahundiak]

Kind of hard to tell but I'm guessing that your Test1 entries have a pagepos of 1 and your Test2 entry has a pagepos of 2? think you need to add another column name to your ORDER BY clause to keep the tests associated wth their categories. The DISTINCT seems unusual as well.

[blckspder]

Thanks for both of your help

I added another column name to my ORDER BY clause like ahundiak said to and I removed the distinct and It worked correctly... I also had to put in an if statement to check and make sure the category names don't repeat... I have alot more pages to upload so hopefully this will work... Thanks again and here is the code I now have

			<?php
			$proQuery = "SELECT subcat.title, maincat.*, pagepos.*, pagemain.* FROM subcat LEFT JOIN maincat ON subcat.catKey=maincat.catKey LEFT JOIN pagepos ON subcat.subKey=pagepos.subKey LEFT JOIN pagemain ON subcat.pageKey=pagemain.pageKey WHERE maincat.catTitle='Electronics' AND pagepos.type='Products' ORDER BY pagepos.position AND pagepos.Title ASC";
			$runPro = mysql_query($proQuery);


		while($nav = mysql_fetch_array($runPro)) {
			if(str_replace("_", " ", $nav['title']) == $nav['Title']) {
		?>
				<p>- <a href="splashelectronics.php?cat=<?php print $nav['catTitle']; ?>&amp;subCat=<?php print $nav['title']; ?>&amp;page=<?php print $nav['pageKey']; ?>&amp;pageTitle=<?php print $nav['Title']; ?>&amp;flag=SubCategory" class="blue"><?php print str_replace("_", " ", $nav['title']); ?></a></p>
		<?php
				}
		?>
			<div style="font-size: 10px; font-weight: normal; padding-left: 25px;">
		<?php
			if(str_replace("_", " ", $nav['title']) != $nav['Title']) {
				print $nav['Title'] .'<br />';
				}
			?>
			</div>
		<?php
			}
		?>

[ahundiak]

Just for info, this:
ORDER BY pagepos.position AND pagepos.Title
Should probably be:
ORDER BY pagepos.Title,pagepos.position

Kind of surprised that using AND in an ORDER BY clause does not trigger a syntax error.