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

First off, you shouldn't query your database in your view. That would be mixing your business logic and your presentation logic. Just assign the query results to a variable in your controller and iterate through it.

As for the query, yup a join can do that in 1 query.

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

This will get you an initial result set. But this won't give you the data nicely ordered like you expect. You'll need to use a bit of PHP to group it into some arrays that you can use in your loops.

Something along the lines of

$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;
  }
}

This should give you an array of categories which have all the related snippets in an array. You can simply loop through this array to reproduce your list.

Answer 2

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.

Answer 3

I'd try this one:

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

I didnt test it, though. Also check the indexes using the EXPLAIN statement so MySQL doesnt do a full scan of the table.

With these results, you can loop the results in PHP and check when the category name changes, and build your output as you wish.

Answer 4

Besides a single combined query you can use two separate ones.

You have a basic tree-structure here with branch elements (categories table) and leaf elements (snippets table). The shortcoming of the single-query solution is that you get owner brach-element repeatedly for every single leaf element. This is redundant information and depending on the number of leafs and the amount of information you query from each branch element can produce large amount of additional traffic.

The two-query solution looks like:

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

Answer 5

It should print completely the same code as your example

$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>';