PHP and MYSQL : do while that dosn't work

Question

Hello, I would like to display Division, then categories under each division and subcategories who's under each catagory. But when I do my do while I didn't receive the right result...

example of what I want:

Division Name
Categorie
  Sub-categorie
  Sub-categorie
  Sub-categorie
Categorie 2
  Sub-categorie 2
  Sub-categorie 2
  Sub-categorie 2
Division Name 2
Categorie 3
...

Sanitaire (that's a division)

Produits papiers (that's a categorie)

Distributeurs assortis (that's a subcategorie)

Distributeurs assortis (that's the same subcategorie...)

Distributeurs assortis (and again...)

Distributeurs assortis (and again...)

(...) i receive the same subcategorie for 10 lines

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 'AND __Produits_Sous_Cat.id_langue = 1 AND __Produits_Sous_Cat.visible = '1'' at line 1

here's is my code :

$LANG = 1;
mysql_select_db($database_dl, $dl);
$query_divisionlist = "SELECT * 
                         FROM `__Division`  
                        WHERE `__Division`.id_langue = ".$LANG." 
                          AND `__Division`.visible = '1'";

$divisionlist = mysql_query($query_divisionlist, $dl) or die(mysql_error());
$row_divisionlist = mysql_fetch_assoc($divisionlist);
$totalRows_divisionlist = mysql_num_rows($divisionlist);

do { echo '<p>'.$row_divisionlist['titre'].'</p>'; 
   mysql_select_db($database_dl, $dl);
   $query_categorielist = "SELECT * 
                             FROM `__Produits_Cat` 
                            WHERE `__Produits_Cat`.id_Division = ".$row_divisionlist['id']." 
                              AND `__Produits_Cat`.id_langue = ".$LANG." 
                              AND `__Produits_Cat`.visible = '1'";

   $categorielist = mysql_query($query_categorielist, $dl) or die(mysql_error());
   $row_categorielist = mysql_fetch_assoc($categorielist);
   $totalRows_categorielist = mysql_num_rows($categorielist);

   do { echo '<p>'.$row_categorielist['titre'].'</p>'; 
     mysql_select_db($database_dl, $dl);
     $query_scategorie = "SELECT * 
                            FROM `__Produits_Sous_Cat` 
                           WHERE `__Produits_Sous_Cat`.id_Produit_Cat = ".$row_categorielist['id']." 
                             AND `__Produits_Sous_Cat`.id_langue = ".$LANG." 
                             AND `__Produits_Sous_Cat`.visible = '1'";

     $scategorie = mysql_query($query_scategorie, $dl) or die(mysql_error());
     $row_scategorie = mysql_fetch_assoc($scategorie);
     $totalRows_scategorie = mysql_num_rows($scategorie);

     do { echo '<p>'.$row_scategorie['titre'].'</p>'; 
       mysql_select_db($database_dl, $dl);
       $query_sscategorie = "SELECT * 
                               FROM `__Produits_Sous_sousCat` 
                              WHERE `__Produits_Sous_sousCat`.id_Produit_Sous_Cat = ".$row_scategorie['id']." 
                                AND `__Produits_Sous_sousCat`.id_langue = ".$LANG."  
                                AND `__Produits_Sous_sousCat`.visible = '1'"; 

       $sscategorie = mysql_query($query_sscategorie, $dl) or die(mysql_error());
       $row_sscategorie = mysql_fetch_assoc($sscategorie);
       $totalRows_sscategorie = mysql_num_rows($sscategorie);

       do { echo '<p>'.$row_sscategorie['titre'].'</p>'; 

       } while ($row_divisionlist = mysql_fetch_assoc($divisionlist));
    } while ($row_categorielist = mysql_fetch_assoc($categorielist)); 
  } while ($row_scategorie = mysql_fetch_assoc($scategorie));
} while ($row_sscategorie = mysql_fetch_assoc($sscategorie));

Thank you very much for your help.

Answer 1

This:

           } while ($row_divisionlist = mysql_fetch_assoc($divisionlist));
    } while ($row_categorielist = mysql_fetch_assoc($categorielist)); 
  } while ($row_scategorie = mysql_fetch_assoc($scategorie));
} while ($row_sscategorie = mysql_fetch_assoc($sscategorie));

seems backward -- the order is reverse of the do's -- shouldn't it be:

           } while ($row_sscategorie = mysql_fetch_assoc($sscategorie));
    } while ($row_scategorie = mysql_fetch_assoc($scategorie)); 
  } while ($row_categorielist = mysql_fetch_assoc($categorielist));
} while ($row_divisionlist = mysql_fetch_assoc($divisionlist));

instead?

Answer 2

Hi,

your code is extremely messy. If it is homework or learning project, you are ok. If you are developing application that you will have to maintain... oh boy you will have fun.

I edited your code little bit. Try this:

$LANG = 1;

mysql_select_db($database_dl, $dl);

//  
$divisionQuery = "SELECT * FROM `__Division`  WHERE id_langue = $LANG  AND visible = '1'";
$divisionResult = mysql_query($divisionQuery, $dl) or die(mysql_error());

//  Scan through all divisions
while ($divisionRow = mysql_fetch_assoc($divisionResult)) {
    echo '<p>' . $divisionRow['titre'] . '</p>';

    //  Get all division categories
    mysql_select_db($database_dl, $dl);
    $categoryQuery = "SELECT * FROM `__Produits_Cat` WHERE id_Division = $divisionRow[id]  AND id_langue = $LANG  AND visible = '1'";
    $categoryResult = mysql_query($categoryQuery, $dl) or die(mysql_error());

    //  Scan through all categories
    while ($categoryRow = mysql_fetch_assoc($categoryResult)) {
        echo '<p>' . $categoryRow['titre'] . '</p>';

        //  Get all sub-categories
        mysql_select_db($database_dl, $dl);
        $subCategoryQuery = "SELECT * FROM `__Produits_Sous_Cat` WHERE id_Produit_Cat = $categoryRow[id] AND id_langue = $LANG AND visible = '1'";
        $subCategoryResult = mysql_query($subCategoryQuery, $dl) or die(mysql_error());

        //  Scan through sub-categories
        while ($subCategoryRow = mysql_fetch_assoc($subCategoryResult)) {
            echo '<p>' . $subCategoryRow['titre'] . '</p>';

            //  Get all sub-sub-categories
            mysql_select_db($database_dl, $dl);
            $subSubCategoryQuery = "SELECT * FROM `__Produits_Sous_sousCat` WHERE id_Produit_Sous_Cat = $subCategoryRow[id] AND id_langue = $LANG  AND  visible = '1'";
            $subSubCategoryResult = mysql_query($subSubCategoryQuery, $dl) or die(mysql_error());

            //  Scan through all sub-sub-categories
            while ($subSubRow = mysql_fetch_assoc($subSubCategoryResult)) {
                echo '<p>' . $subSubRow['titre'] . '</p>';
            }
        }
    }
}

Answer 3

Don't worry so much about your PHP at this stage but just try to get a more robust and relational query. Generally, having a loop around a query is an indication that something is wrong. Read up on Joins in MySQL and you should find you can replace your 4 queries (which are executed numerous times) with 1 query (called once), and your 4 nested loops with one much simpler loop.