DanielFox DanielFox - 4 months ago 7
MySQL Question

How can I group elements in HTML while printing query results?

I have a MySQL table with different types of food:

id name type
1 apple 1
2 orange 1
3 lettuce 2
4 broccoli 2
5 pork 3


Then in my PHP file I have an array with a number code for each type of food, like this:

$foodTypes = array (
1 => 'FRUITS',
2 => 'VEGETABLES',
3 => 'MEAT'
);


What I want is to select my whole food table and print it in HTML with the food type as the title, like this:

FRUITS:
apple
orange

VEGETABLES:
lettuce
broccoli

MEAT:
pork


I know how to do this with multiple queries, one for each food type, but I'd like to do this with one single query. How can I do this?

Answer

I think you should be able to do this with what you already have. First, select from your foods table ordered by type.

SELECT id, name, type FROM foods ORDER BY type;

Then loop through the results, adding a heading each time the type changes.

$type = null;                                // No type initially
while($row = $result->fetch_assoc()) {
    if ($row['type'] !== $type) {            // Check if type has changed
        $type = $row['type'];                // Reset type to the new type
        echo $foodTypes[$type] . ":<br/>";   // Print header
    }
    echo $row['name'] . "<br/>";
}

Obviously the html here is pretty simple, but I assume you have a more specific idea for how you want to format it.

Comments