while loop in mysql_fetch_assoc not working

Question

i've been struggling with this all day: I'm displaying a menu with submenus, this same code is working in production witch makes my case even weirder. my current dev enviroment uses php 5.4.

In dev enviroment this code leads to an empty page with no error (with display_errors and error_log activated and working). if instead of going to the second while, I just run through it once, everything works (but only shows one item)

$query = 'SELECT * FROM permisos as p LEFT JOIN secciones_web as s ON p.id_seccion_web = s.id WHERE s.parent_id = 0 AND s.activo = 1 AND (p.seccion = "'.$_SESSION["user_section"].'" OR p.usuario="'.$_SESSION["user"].'") GROUP BY p.id_seccion_web ORDER BY s.orden ASC';
$results = mysql_query($query);

$menu = "";

while($rowp = mysql_fetch_assoc($results)){
    $menu.='<li class="submenu"><a href="#"><i class="icon '.$rowp["icon"].'"></i> <span>'.$rowp["nombre"].'</span></a><ul>';
    $q = 'SELECT * FROM permisos as p LEFT JOIN secciones_web as s ON p.id_seccion_web=s.id WHERE s.parent_id='.$rowp["id"].' AND s.activo=1 AND (p.seccion = "'.$_SESSION["user_section"].'" OR p.usuario="'.$_SESSION["user"].'") GROUP BY p.id_seccion_web ORDER BY s.orden ASC';
    $resultado = mysql_query($q,$db_conn);

    while($rowsubmennu = mysql_fetch_assoc($resultado)){
        if($_SESSION["user_section"] == "Almacén" && $rowsubmennu["url"] == "pedido-almacen.php"){
            $menu.='<li><a href="'.$rowsubmennu["url"].'" class="navi">'.$rowsubmennu["nombre"].'<span class="label label-pedidos">0</span></a></li>';
        }else{
            $menu.='<li><a href="'.$rowsubmennu["url"].'" class="navi">'.$rowsubmennu["nombre"].'</a></li>';
        }
    }
    $menu.= "</ul></li>";
}

any clue on why the 2nd while is crashing my app without error?

UPDATE: full page here: http://pastebin.com/FPrQgsbE

@Hanky웃Panky Why? It's an optional argument, there's no harm in providing it explicitly. — Barmar, May 07 '15 at 16:42
Yep but there could be a typo in it and their first query works because they don't provide it. If the value explicitly provided is an invalid resource it will fail — Hanky Panky, May 07 '15 at 16:43
Why are you doing two separate queries instead of combining them with a `LEFT JOIN`? — Barmar, May 07 '15 at 16:43
Add `or die(mysql_error())` to the end of both `mysql_query` lines, to see if you're getting a SQL error. — Barmar, May 07 '15 at 16:44
dont use mysql_* function http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/14110189#14110189 — NullPoiиteя, May 07 '15 at 16:44
**WARNING**: This is terrifyingly insecure because those parameters are not [properly escaped](http://bobby-tables.com/php). You should **NEVER** put `$_POST` data directly into the query: it creates a gigantic [SQL injection bug](http://bobby-tables.com/). `mysql_query` is an obsolete interface and should not be used, it's being removed from PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. — tadman, May 07 '15 at 17:20
A couple things. Running nested queries in loops should be something that you look at and think "That doesn't pass the smell test". You should always look for a way to consolidate this sort of pattern to a single query. Second, you are doing absolutely no handling of errors in this code at all. You should right your code to handle all error cases around DB access. — Mike Brant, May 07 '15 at 17:31
2. $db_conn is preferred (two different databases working) and not the issue here. It should have been in the first one too, but default connection is the correct. 3. Added mysql_error, no error popped out. 4. I understand the issues of using mysql_ but it's a huge app and would be a crazy ride to change it to pdo. 5 No $_POST is being used here. data has been cleaned before introducing it to db. 6. I have nested queries, But my knoledge doesn't go as far as to do this one in one only select. (And getting top level menus and submenus separated). But i'm open to your input in this one. — monxas, May 07 '15 at 18:19

Alex · Answer 1 · 2015-05-07T17:17:00.873

0

Just check for errors first:

$resultado = mysql_query($q,$db_conn);
if (!$resultado) {
    die('Invalid query: ' . mysql_error());
}
    while($rowsubmennu = mysql_fetch_assoc($resultado)){

And $db_conn is not set up in posted fragment of code. Where it was initialized?

edited May 07 '15 at 17:17

answered May 07 '15 at 16:58

Alex

16,739
1
28
51

nope, same. I must clear that the error is not just the mysql, it falis to load any part of the page and no error is returned. This same code is working on another machine. If I remove the second while while($rowsubmennu = mysql_fetch_assoc($resultado)){ ... } and only do: $rowsubmennu = mysql_fetch_assoc($resultado) it works (only showng one row, of course) – monxas May 07 '15 at 18:24
being a menu, the code is pretty much it. this file is required in almost any page of the app, only some html code left to show. but ok, I'll share it (update above) – monxas May 07 '15 at 19:49
do you have this page link online? – Alex May 07 '15 at 19:57

while loop in mysql_fetch_assoc not working

1 Answers1