Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    32

    Unanswered: LEFT OUTER JOIN for parent-sub items

    Hi,

    This is how for I could get with LEFT OUTER JOIN, what's the proper way of doing this:

    Code:
    $sql = "
    	SELECT 
    	menu1.title as title1, 
    	menu2.title as title2 
    	
    	FROM menu_links AS menu1 
    
    	LEFT OUTER JOIN menu_links AS menu2 
    		ON menu1.menu_link_id = menu2.parent_link_id 				
    
    	WHERE menu1.parent_link_id = 0";
    
    $stat = mysql_query($sql, $dblink);
    while ($row = mysql_fetch_array($stat)) {
    	$list .= "Title1: ".$row["title1"]."<br>";
    	if ($row["title2"]) {
    		$list .= "-- Title2: ".$row["title2"]."<br>";
    	}
    }
    It produces:

    Title1: About Us
    Title1: Articles
    -- Title2: Fruit
    Title1: Articles
    -- Title2: Meet
    Title1: Articles
    -- Title2: Vegetables
    Title1: Donations
    Title1: Services
    Title1: Volunteers
    Title1: News & Events
    Title1: Home
    Title1: Contact Us

    'Articles' needs to be printed once as a parent of Fruit, Meet and Vegetables. And how do I mention in the query not to give empty values which I'm avoiding using if condition?

    I will appreciate any sort of help in this matter.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is not a mysql question, i suggest you ask the mods to move your thread to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    32
    I'll rather keep on trying it myself and wait if someone cares to help me with this on this forum. Sorry to violate these strict rules.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they're not strict rules, i only suggested moving your thread to the php forum because it's a php question, and the guys who come to the php forum are more likely to be able and willing to give you an answer

    but as they told the doctor, suture self
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    32
    I have seen threads on forums through google discussing how to pull categories, sub-categories using only one database query, so I was convinced that it was a database question.

    Anyway, a php trick is working for me so far:

    Code:
    $prev_title = "";
    while ($row = mysql_fetch_array($stat)) {
    	if ($prev_title != $row["title1"]) $list .= " ".$row["title1"]."<br>";
    	if ($row["title2"]) $list .= "&nbsp; &nbsp; ".$row["title2"]."<br>";
    	$prev_title = $row["title1"];
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •