get hierarchical data from mysql

Question

I want to display menu from mysql database. This is what I tried so far and don't know what is the error for. I appreciate any help . I posted the table that I want to display and the error that I got

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\second _try\eCRA.php on line 18

<?php
    function display_menu($parent, $level) {
        $query=mysql_query("SELECT n.id, n.menu_name, n.link, d.count FROM 'menu' n 
        LEFT OUTER  JOIN(SELECT parent, COUNT (*) AS count FROM 'menu' GROUP BY
        parent) d ON  n.id=d.parent  W HERE n.parent=".$parent);
        echo"<ul>";

        while($row = mysql_fetch_assoc($query)) {
            if($row['count']>0) {
                echo"<li><a href='" .$row['link']. "'>". $row['menu_name'] ."</a>";
                display_menu($row['id'], $level + 1);
                echo"</li>";
            }//f
            elseif($row['count']==0) { 
                echo"<li><a href='". $row['link'] ."'>". $row['menu_name'] ."</a></li>";
            }else; 
        }//w
        echo"</ul>";
    }

    display_menu(0,2);
?>

![error][1] ![Table][2]

Your query fails, therefore `$query` is initialized with `false`. Use `mysql_error()` to investigate your issue. — diegoperini, Sep 08 '13 at 12:39
Your mysql_query call is returning `false` due to an error. (You should check for this before using `$query`). Assuming the space in `W HERE` is a typo in your question then you need to use `mysql_error` to show the database error. i.e `echo mysql_error();` just after the `mysql_query` call. — SpaceDog, Sep 08 '13 at 12:40
A little reminder: Standard mysql library is currently marked as deprecated and all code depending on it should be rewritten with mysqli classes. Furthermore, error handling should never be omitted from project code. In your case, you should always check value of `$query` before your main logic. — diegoperini, Sep 08 '13 at 12:43
Just FYI, by convention, where you use $query, I would use $result... and "W HERE" looks a bit odd — Strawberry, Sep 08 '13 at 12:59
@SpaceDog i got this ////You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS count FROM `menu` GROUP BY parent) d ON n.id=d.parent WHERE n.parent=0' at line 1 — Suma, Sep 08 '13 at 17:46
The other answers below point out the error (the quotes round the table name). But the best way to debug this is to print the query string after you create it, then test it on the command line of mysql (or in a web interface like phpMyAdmin). Once you have the working SQL you can go back and fix the code that generates it. — SpaceDog, Sep 09 '13 at 01:35

score 0 · Answer 1 · answered Sep 08 '13 at 12:44

On line 11 you have $row['count'] which means you are retrieving the column count which is not in in your database columns (As per your attached image), I think you want this.

if(mysql_num_rows($query)>0){
//do stuff here 
}
elseif(mysql_num_rows($query)>==0){
//do stuff here 
}

score 0 · Answer 2 · answered Sep 08 '13 at 12:48

Here's what's wrong:

$query=mysql_query("SELECT n.id, n.menu_name, n.link, d.count FROM 'menu' n 
    LEFT OUTER  JOIN(SELECT parent, COUNT (*) AS count FROM 'menu' GROUP BY
    parent) d ON  n.id=d.parent  W HERE n.parent=".$parent);

You're using single quotes for referencing to a table name. You cannot do that. You'd need to use backticks for that!

Like so:

$query=mysql_query("SELECT n.id, n.menu_name, n.link, d.count FROM `menu` n 
    LEFT OUTER JOIN (SELECT parent, COUNT (*) AS count FROM `menu` GROUP BY
    parent) d ON  n.id=d.parent  W HERE n.parent=".$parent);

I would also like to point out that your code is possibly vulnerable to SQL injection. Look into that, if you don't mind.

score 0 · Answer 3 · edited Sep 08 '13 at 13:12

0

You're using the wrong syntax. FROM 'menu' should be with backticks instead of single quotes:

  FROM `menu`

edited Sep 08 '13 at 13:12

Maxime Lorant

34,607
19
87
97

answered Sep 08 '13 at 12:48

Mihai

26,325
7
66
81

score 0 · Answer 4 · answered Sep 08 '13 at 12:51

mysql_query fails to get the result use mysql_error() to find the problem

 function display_menu($parent, $level) {
    $query=mysql_query("SELECT n.id, n.menu_name, n.link, d.count FROM 'menu' n 
    LEFT OUTER  JOIN(SELECT parent, COUNT (*) AS count FROM 'menu' GROUP BY
    parent) d ON  n.id=d.parent  W HERE n.parent=".$parent) or die(mysql_error());
    echo"<ul>";

    while($row = mysql_fetch_assoc($query)) {
        if($row['count']>0) {
            echo"<li><a href='" .$row['link']. "'>". $row['menu_name'] ."</a>";
            display_menu($row['id'], $level + 1);
            echo"</li>";
        }//f
        elseif($row['count']==0) { 
            echo"<li><a href='". $row['link'] ."'>". $row['menu_name'] ."</a></li>";
        }else; 
    }//w
    echo"</ul>";
}

display_menu(0,2);

score 0 · Answer 5 · answered Sep 08 '13 at 13:04

Assuming parent is numeric, try this...

SELECT n.id
     , n.menu_name
     , n.link
     , d.count 
  FROM menu n 
  LEFT 
  JOIN 
     ( SELECT parent 
            , COUNT (*) count 
         FROM menu
        GROUP 
           BY parent
     ) d 
    ON n.id = d.parent 
 WHERE n.parent = $parent;

get hierarchical data from mysql

5 Answers5