php/mysql dynamic menu

Question

I have an existing drop down menu that I am attempting to convert to PHP/MySQL to allow the client to edit the menu items. This is the structure:

Home-index.php
About Us-#
  -about.php
  -annualreport.php
  -awards.php
Services-#
  -services.php
  -loans.php

and so forth. The query I'm using is:

select distinct mainlinks.id as daddyid, mainlinks.title as daddytitle, mainlinks.urlink as daddyurl, babylinks.id as babyid, babylinks.title as babytitle, babylinks.urlink as babyurl from mainlinks, babylinks where mainlinks.id = babylinks.parentid order by mainlinks.listorder";

And the result displayed with the code below:

$result = mysql_query($query) or die(mysql_error());
// keep track of previous maincategory
$previous_maincategory = NULL;

while ($row = mysql_fetch_assoc($result))
{

        // if maincategory has changed from previouscategory then display it
        if ($previous_maincategory != $row['daddytitle']) 
        {
                echo "<strong><h2>" . strtoupper($row['daddytitle']) . "</h2></strong>";
        }

        echo '<a onclick="return confirmSubmit()" class="inside" href="deletesubcategory.php?id=';
        echo $row['babyid'];
        echo '">';
        echo $row['babytitle'];
        echo "</a>";
        echo "<br/>";

        // record what the previous category was
        $previous_maincategory = $row['daddytitle'];
}

It only displays the items that have a child element, not the parent items that do not have child elements. Any ideas? I'm guessing the problem is with the query where clause but I can't figure out how to grab what I need. Thanks.

ain · Accepted Answer · 2011-07-27 14:20:35Z

1

You have to use LEFT JOIN to get items which don't have childs, try

select distinct mainlinks.id as daddyid, mainlinks.title as daddytitle, mainlinks.urlink as daddyurl, babylinks.id as babyid, babylinks.title as babytitle, babylinks.urlink as babyurl 
from mainlinks 
  LEFT JOIN babylinks ON mainlinks.id = babylinks.parentid
order by mainlinks.listorder;

answered Jul 27, 2011 at 14:20

ain

22.8k3 gold badges60 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SeanCannon · Accepted Answer · 2011-07-27 14:20:43Z

1

Your query is only grabbing items where mainlinks.id = babylinks.parentid meaning it will ignore items where that relationship is non-existent.

Try this:

SELECT DISTINCT mainlinks.id as daddyid, mainlinks.title as daddytitle, mainlinks.urlink as daddyurl, babylinks.id as babyid, babylinks.title as babytitle, babylinks.urlink as babyurl FROM mainlinks LEFT JOIN babylinks ON mainlinks.id = babylinks.parentid ORDER BY mainlinks.listorder

Using a LEFT JOIN will tell your query to pull all the mainlinks regardless if they have a baby link, but only pull baby links that have a parent.

answered Jul 27, 2011 at 14:20

SeanCannon

78.1k17 gold badges126 silver badges145 bronze badges

Collectives™ on Stack Overflow

php/mysql dynamic menu

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related