Inefficient SQL Query

Question

I'm building a simple web app at the moment that I'll one day open source. As it stands at the moment, the nav is generated on every page load (which will change to be cached one day) but for the moment, it's being made with the code below. Using PHP 5.2.6 and MySQLi 5.0.7.7, how more efficient can the code below be? I think joins might help, but I'm after advice. Any tips would be greatly appreciated.

<?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>';
    }
?>

Answer 1

You can run this query:

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

Then iterate thru the results to create the proper heading like:

// 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>');

Note that I used printf but you should use your own function instead which wraps around printf, but runs htmlspecialchars thru the parameters (except the first of course).

Disclaimer: I do not necessarily encourage such use of <ul>s.

This code is just here to show the basic idea of processing hierarchical data got with one query.