PHP and MySQL: Dynamic Multi-level Menu

Question

I Googled and found something that can work for me (http://wizardinternetsolutions.com/web-database-design/dynamic-multilevel-css-menu-php-mysql/). I plan to modify it a bit and tailor it to my needs, once I get it to work. -Ej. I want to use label as Primary Key rather than a random number.

However, I am getting an error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /home/content/81/10038181/html/_html/menu/menu-generator.php on line 23

The funny thing is, that when I run the query, it works fine.

Here is the table structure (SQL):

CREATE TABLE `menu` (
  `id` int(11) NOT NULL auto_increment,
  `label` varchar(50) NOT NULL default '',
  `link` varchar(100) NOT NULL default '#',
  `parent` int(11) NOT NULL default '0',
  `sort` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=248 DEFAULT CHARSET=latin1;

Here is a mockup of the data

PHP:

$dbc = new mysqli("localhost", "username", "password", "temp_database");
$dbn = "temp_database";
    function display_children($parent, $level) {
        $sql = "SELECT a.id, a.label, a.link, Deriv1.Count FROM `$dbn`.`menu` a  LEFT OUTER JOIN (SELECT `parent`, COUNT(`parent`) AS Count FROM `".$dbn."`.`menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent . " ORDER BY `sort`";
        //echo $sql;  //For testing purposes
        $result = $dbc->query($sql);
        echo "<ul>\n";

        while ($row = $result->fetch_assoc() ) {
            if ($row['Count'] > 0) {
                echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a>";
                display_children($row['id'], $level + 1);
                echo "</li>";
                }
            elseif ($row['Count']==0) {
                echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
                } 
            else;
        }
        echo "</ul>";
    }
    display_children(0, 1);

Your mysql_query is failing and hence returning boolean false in $result variable. — Dr. Dan, Dec 17 '12 at 05:57
Probably means that `$result` is false. Put a `var_dump($result)` after `mysql_query` to see if the query is being executed properly. — Osiris, Dec 17 '12 at 05:57
@Osiris However, when I run the very same query in myphpadmin, it runs it ok — Omar, Dec 17 '12 at 05:59
@Osiris I made the proper changes/updates to my question. Can you please help me make the proper modifications so I can use `label` instead of `ID` as `Primary key`? — Omar, Dec 17 '12 at 06:57

score 0 · Answer 1 · answered Dec 17 '12 at 06:00

0

I followed the link - there's no code that connects to the database at all. Look at another PHP-MySql tutorial that includes mysql_connect.

Also, this is just a quick fix - mysql_ extensions have been deprecated. Use mysqli instead. Check the documentation on php.net

answered Dec 17 '12 at 06:00

Osiris

4,195
2
22
52

I did not include any information about the database connection, in purpose. Also, I noticed that it needed `mysql_select_db();` Once I added this line, it ran the code. Now, since you pointed out, I'll have to modify it to use `mysqli` – Omar Dec 17 '12 at 06:17
I also would like to modify this code, so I can use `label` as Primary key, and eliminate `ID` altogether – Omar Dec 17 '12 at 06:18

PHP and MySQL: Dynamic Multi-level Menu

1 Answers1