Efficiently Access All Lookup Pairs

Databases
1

(Bad title. Sorry.)

Let’s say I have some jokes, and I have some categories. Jokes can have multiple categories and categories can contain multiple jokes, so I create a lookup table.

No problem. But then I decide that I want to display all my jokes on one page. I’ll probably use some code similar to the following:


$sql = 'SELECT text FROM jokes';
$jokes = mysqli_query($link, $sql);
while ($joke = mysqli_fetch_array($jokes)) {
    echo $joke['text'].'<br />';
}

This gives me a nice page with each joke on its own line. But what if I also wanted to display the categories a joke belongs under? This was the first solution I came up with, and it sucks for obvious reasons:


$sql = 'SELECT id, text FROM jokes';
$jokes = mysqli_query($link, $sql);
while ($joke = mysqli_fetch_array($jokes)) {
    echo $joke['text'];
    $sql = 'SELECT category_id FROM lookup_table WHERE id = '.$joke['id'];
    $category_ids = mysqli_query($link, $sql);
    while ($category_id = mysqli_fetch_array($category_ids)) {
        $sql = 'SELECT name FROM categories WHERE id = '.$category_id['category_id'];
        $category = mysqli_query($link, $sql);
        $category = mysqli_fetch_array($category);
        $category = $category['name'];
        echo $category.', ';
    }
    echo '<br />';
}

This may accomplish the task, but holy crap, I’m making multiple queries to the database for each individual joke (not to mention that, when I list the category names, it ends with a hanging comma).

Please tell me there’s a better way.

2

JOIN!

SELECT
  jokes.text,
  categories.name
FROM  jokes
INNER JOIN lookup_table ON lookup_table.id = jokes.id
INNER JOIN categories ON categories.id = lookup_table.category_id

I highly recommend Simply SQL :slight_smile:

3

thanks dan :slight_smile:

i like the idea of using GROUP_CONCAT on the categories, this way you get one row per joke and don’t have to do all that fancy looping in the app…

SELECT jokes.text
     , GROUP_CONCAT(categories.name) AS categories
  FROM jokes
INNER 
  JOIN lookup_table 
    ON lookup_table.id = jokes.id
INNER 
  JOIN categories 
    ON categories.id = lookup_table.category_id
GROUP
    BY jokes.text
4

Oh man, GROUP_CONCAT is perfect. Thank you.