Requête SQL inefficace

Je crée une application Web simple au moment où je serai un jour open source. En ce moment, le nav est généré sur chaque chargement de page (qui changera pour être mis en cache un jour), mais pour le moment, il est réalisé avec le code ci-dessous. À l'aide de PHP 5.2.6 et MySQLi 5.0.7.7, comment est-il plus efficace que le code ci-dessous? Je pense que des jointures pourraient aider, mais je suis après un conseil. Tous les conseils seraient grandement appréciés.

<?php $navQuery = $mysqli->query("SELECT id,slug,name FROM categories WHERE live=1 ORDER BY name ASC") or die(mysqli_error($mysqli)); while($nav = $navQuery->fetch_object()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; $subNavQuery = $mysqli->query("SELECT id,name FROM snippets WHERE category='$nav->id' ORDER BY name ASC") or die(mysqli_error($mysqli)); while($subNav = $subNavQuery->fetch_object()) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $subNav->name .'">'. $subNav->name .'</a>'; echo '</li>'; } echo '</ul>'; echo '</li>'; } ?> 

Vous pouvez exécuter cette requête:

 SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name SÉLECTIONNEZ c.id AS cid, c.slug AS cslug, c.name AS cname, SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name à l'AS sid, s.name AS sname SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name DES catégories AS c SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name LEFT JOIN snippets AS s ON s.category = c.id SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name OÙ c.live = 1 SELECT c.id AS cid, c.slug AS cslug, c.name AS cname, s.id AS sid, s.name AS sname FROM categories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live=1 ORDER BY c.name, s.name 

Ensuite, iterate à travers les résultats pour créer le bon titre comme:

 // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // dernière catégorie ID // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); $ lastcid = 0; // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); while ($ r = $ navQuery-> fetch_object ()) { // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); si ($ r-> cid! = $ lastcid) { // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // referons la dernière catégorie ouverte (le cas échéant) // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); si ($ lastcid) // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); printf ('</ li> </ ul>'); // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save la catégorie actuelle // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); $ lastcid = $ r-> cid; // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // catégorie d'affichage // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); printf ('<li> <a href="/%s">% s </a>', $ r-> cslug, $ r-> cname); // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // afficher le premier extrait // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); printf ('<li> <a href="/%s/%s">% s </a> </ li>', $ r-> cslug, $ r-> sname, $ r-> sname); // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // catégorie déjà traitée, affichez juste l'extrait // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // afficher l'extrait // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); printf ('<li> <a href="/%s/%s">% s </a> </a>', $ r-> cslug, $ r-> sname, $ r-> sname); // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); } // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); } // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // referons la dernière catégorie ouverte (le cas échéant) // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); si ($ lastcid) // last category ID $lastcid = 0; while ($r = $navQuery->fetch_object ()) { if ($r->cid != $lastcid) { // new category // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); // save current category $lastcid = $r->cid; // display category printf ('<li><a href="/%s">%s</a>', $r->cslug, $r->cname); // display first snippet printf ('<li><a href="/%s/%s">%s</a></li>', $r->cslug, $r->sname, $r->sname); } else { // category already processed, just display snippet // display snippet printf ('<li><a href="/%s/%s">%s</a></a>', $r->cslug, $r->sname, $r->sname); } } // let's close the last open category (if any) if ($lastcid) printf ('</li></ul>'); 

Notez que j'ai utilisé printf mais que vous devriez utiliser votre propre fonction à la place qui s'enroule autour de printf, mais exécute htmlspecialchars travers les parameters (sauf le premier, bien sûr).

Disclaimer: Je n'investis pas nécessairement cette utilisation de <ul> s.

Ce code est juste ici pour montrer l'idée de base du traitement des données hiérarchiques obtenues avec une requête.

Tout d'abord, vous ne devez pas interroger votre database dans votre vue. Cela combinerait votre logique métier et votre logique de présentation. Il suffit d'assigner les résultats de la requête à une variable dans votre controller et de l'itérer à travers elle.

En ce qui concerne la requête, yup une jointure peut le faire en 1 requête.

 SELECT * -- Make sure you only select the fields you want. Might need to use aliases to avoid conflict FROM snippets S LEFT JOIN categiries C ON S.category = C.id WHERE live = 1 ORDER BY S.category, C.name SELECT * - Assurez-vous de sélectionner uniquement les champs souhaités. SELECT * -- Make sure you only select the fields you want. Might need to use aliases to avoid conflict FROM snippets S LEFT JOIN categiries C ON S.category = C.id WHERE live = 1 ORDER BY S.category, C.name Peut-être utiliser des alias pour éviter les conflits SELECT * -- Make sure you only select the fields you want. Might need to use aliases to avoid conflict FROM snippets S LEFT JOIN categiries C ON S.category = C.id WHERE live = 1 ORDER BY S.category, C.name FROM snippets S LEFT S'INSCRIRE aux catégories C ON S.category = C.id SELECT * -- Make sure you only select the fields you want. Might need to use aliases to avoid conflict FROM snippets S LEFT JOIN categiries C ON S.category = C.id WHERE live = 1 ORDER BY S.category, C.name OÙ vivre = 1 SELECT * -- Make sure you only select the fields you want. Might need to use aliases to avoid conflict FROM snippets S LEFT JOIN categiries C ON S.category = C.id WHERE live = 1 ORDER BY S.category, C.name 

Cela vous procurera un résultat initial. Mais cela ne vous donnera pas datatables bien ordonnées comme vous l'attendiez. Vous devrez utiliser un peu de PHP pour le regrouper dans des arrays que vous pouvez utiliser dans vos loops.

Quelque chose dans le sens de

 $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ categories = array (); $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } foreach ($ résultats en $ résultat) { $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ snippet = array (); $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } // affecte toutes datatables liées à l'extrait dans cette var $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } si (isset ($ categories [$ result ['snippets.category']])) { $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ categories [$ result ['snippets.category']] ['snippet'] [] = $ snippet; $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ category = array (); $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } // affecte toutes datatables liées à la catégorie dans cette var; $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ categories [$ result ['snippets.category']] ['snippet'] = array ($ snippet); $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } $ categories [$ result ['snippets.category']] ['category'] = $ category; $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } } $categories = array(); foreach ($results as $result) { $snippet = array(); //assign all the snippet related data into this var if (isset($categories[$result['snippets.category']])) { $categories[$result['snippets.category']]['snippet'][] = $snippet; } else { $category = array(); //assign all the category related data into this var; $categories[$result['snippets.category']]['snippet'] = array($snippet); $categories[$result['snippets.category']]['category'] = $category; } } 

Cela devrait vous donner un set de catégories qui ont tous les extraits liés dans un tableau. Vous pouvez simplement parcourir ce tableau pour reproduire votre list.

J'essayerais ceci:

 SELECT c.slug,c.name,s.name FROM categories c LEFT JOIN snippets s ON s.category = c.id WHERE live=1 ORDER BY c.name, s.name c.slug, c.name, s.name SELECT c.slug,c.name,s.name FROM categories c LEFT JOIN snippets s ON s.category = c.id WHERE live=1 ORDER BY c.name, s.name catégories c SELECT c.slug,c.name,s.name FROM categories c LEFT JOIN snippets s ON s.category = c.id WHERE live=1 ORDER BY c.name, s.name GAUCHE JOIN snippets s SELECT c.slug,c.name,s.name FROM categories c LEFT JOIN snippets s ON s.category = c.id WHERE live=1 ORDER BY c.name, s.name ON s.category = c.id SELECT c.slug,c.name,s.name FROM categories c LEFT JOIN snippets s ON s.category = c.id WHERE live=1 ORDER BY c.name, s.name 

Je ne l'ai pas testé, cependant. Vérifiez également les index à l'aide de l'instruction EXPLAIN , de sorte que MySQL ne fait pas une parsing complète de la table.

Avec ces résultats, vous pouvez lancer les résultats en PHP et vérifier quand le nom de la catégorie change et créer votre sortie comme vous le souhaitez.

En plus d'une seule requête combinée, vous pouvez utiliser deux séparés.

Vous avez une structure arborescente basique ici avec des éléments de twig (table de catégories) et des éléments de feuille (tableau des extraits). La lacune de la solution de requête unique est que vous obtenez l'élément de braquage propriétaire à plusieurs resockets pour chaque élément de feuille. Il s'agit d'informations redondantes et en fonction du nombre de feuilles et la quantité d'informations que vous interrogez de chaque élément de twig peut générer une quantité importante de trafic supplémentaire.

La solution à deux interrogations ressemble à:

 $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ navQuery = $ mysqli-> query ("SELECT id, slug, name FROM catégories WHERE live = 1 ORDER BY name") $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } ou mourir (mysqli_error ($ mysqli)); $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ subNavQuery = $ mysqli-> query ("SELECT c.id AS cid, s.id, s.name FROM catégories AS c LEFT JOIN snippets AS s ON s.category = c.id WHERE c.live = 1 ORDER BY c .name, s.name ") $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } ou mourir (mysqli_error ($ mysqli)); $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ sub = $ subNavQuery-> fetch_object (); $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } // pré-lecture d'un logging $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } while ($ nav = $ navQuery-> fetch_object ()) { $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '<li>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '<a href="/'. $nav-> slug.' "> '. $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ nav-> nom. '</a>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '<ul>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } while ($ sub-> cid == $ nav-> id) { $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '<li>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '<a href="/'. $nav-> slug.' / '. $ sub-> name.' "> '. $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ sous-> nom. '</a>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '</ li>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } $ sub = $ subNavQuery-> fetch_object (); $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } } $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } echo '</ ul>'; $navQuery = $mysqli->query ("SELECT id, slug, name FROM categories WHERE live=1 ORDER BY name") or die (mysqli_error ($mysqli)); $subNavQuery = $mysqli->query ("SELECT c.id AS cid, s.id, s.name FROM categories AS c LEFT JOIN snippets AS s ON s.category=c.id WHERE c.live=1 ORDER BY c.name, s.name") or die (mysqli_error ($mysqli)); $sub = $subNavQuery->fetch_object (); // pre-reading one record while ($nav = $navQuery->fetch_object ()) { echo '<li>'; echo '<a href="/'. $nav->slug .'">'. $nav->name .'</a>'; echo '<ul>'; while ($sub->cid == $nav->id) { echo '<li>'; echo '<a href="/'. $nav->slug .'/'. $sub->name .'">'. $sub->name .'</a>'; echo '</li>'; $sub = $subNavQuery->fetch_object (); } echo '</ul>'; } 

Il devrait imprimer complètement le même code que votre exemple

 $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; $ navQuery = $ mysqli-> query ("SELECT t1.id AS cat_id, t1.slug, t1.name AS cat_name, t2.id, t2.name $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; DES catégories AS t1 $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; LEFT JOIN snippets AS t2 ON t1.id = t2.category $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; OÙ t1.live = 1 $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; ORDER BY t1.name ASC, t2.name ASC ") ou die (mysqli_error ($ mysqli)); $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; $ current = false; $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; while ($ nav = $ navQuery-> fetch_object ()) { $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; si ($ courant! = $ nav-> cat_id) { $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; si ($ courant) écho '</ ul>'; $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; echo '<a href="/'. $nav-> slug.' "> '. $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; $ nav-> cat_name. '</a> <ul>'; $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; $ courant = $ nav-> cat_id; $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; } $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; si ($ nav-> id) {// vérifie la catégorie vide $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; echo '<li> <a href="/'. $nav-> slug.' / '. $ nav-> nom.' "> '. $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; $ nav-> nom. '</a> </ li>'; $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; } $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; } $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>'; // dernière catégorie $navQuery = $mysqli->query("SELECT t1.id AS cat_id,t1.slug,t1.name AS cat_name,t2.id,t2.name FROM categories AS t1 LEFT JOIN snippets AS t2 ON t1.id = t2.category WHERE t1.live=1 ORDER BY t1.name ASC, t2.name ASC") or die(mysqli_error($mysqli)); $current = false; while($nav = $navQuery->fetch_object()) { if ($current != $nav->cat_id) { if ($current) echo '</ul>'; echo '<a href="/'. $nav->slug .'">'. $nav->cat_name .'</a><ul>'; $current = $nav->cat_id; } if ($nav->id) { //check for empty category echo '<li><a href="/'. $nav->slug .'/'. $nav->name .'">'. $nav->name .'</a></li>'; } } //last category if ($current) echo '</ul>';