CODEWITHSUNDEEP
phpmysql
DanielFox
DanielFox

Reputation: 675

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?

Upvotes: 0

Views: 126

Answers (4)

John Ruddell
John Ruddell

Reputation: 25872

what i was saying before is something like this...

SETUP:

  $foodTypes = array (
    1=> 'FRUITS',
    2=> 'VEGETABLES',
    3=> 'MEAT'
  );
  $rows = array(
    1 => array('name'=> 'apple', 'type' => 1),
    2 => array('name'=> 'orange', 'type' => 1),
    3 => array('name'=> 'lettuce', 'type' => 2),
    4 => array('name'=> 'broccoli', 'type' => 2),
    5 => array('name'=> 'pork', 'type' => 3)
  );

i just made $rows with the data but you could easily do that with a sql select and then just

array_push ($rows, $row)

LOOP:

foreach ($foodTypes as $fud_key => $temp){
echo "<h1>" .$temp. "</h1>";
echo "<br>";
foreach($rows as $key => $value){ 
  if($value['type'] == $fud_key){
     echo "<span>" . $value['name'] . "</span>";
     echo "<br>";
    }; 
  };
}

OUTPUT:

FRUITS

apple

orange

VEGETABLES

lettuce

broccoli

MEAT

pork

Upvotes: 1

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41820

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.

Upvotes: 0

durbnpoisn
durbnpoisn

Reputation: 4669

You do this:

SELECT name, type from [table] group by type

That will do your select and group. To name them, you add a case statement:

SELECT id, name, 
     CASE type 
         WHEN 1 
           THEN 'Fruits'
         WHEN 2 
           THEN 'Vegetables'
         WHEN 3 
           THEN 'Meats'
     END AS 'type' 
FROM [table] 
ORDER BY type

In addition, you can use your array rather than hardcoding the case results by replace it with $foodtypes[0] and so on.

This will take care of grouping. I think I may have misunderstood. If you need this to paint out each, it probably should look more like this: (noticed I dropped the GROUP BY and changed it to ORDER BY)

html

<table>
<tr><td>id</td><td>name</td><td>type</td>

php

    $result = $mysqli->query($stmt);

    while($row = $result->fetch_assoc()){
        print '<tr><td>'. $row['id'] .'</td><td>'. $row['name'] .'</td><td>'. $row'type'] .'</td></tr>';

}

end php

close html

</table>

That should be everything you need.

Upvotes: 1

Jerbot
Jerbot

Reputation: 1168

I'd suggest adding an additional table to your database for your types

id   type
1    FRUITS
2    VEGETABLES
3    MEAT

Then your database draw would be

SELECT
    t1.name,
    t2.type
FROM tblFood AS t1
INNER JOIN tblTypes AS t2 ON t2.id = t1.type

On the PHP side:

$result = array();
while($row = $query->fetch_assoc()) {
    $result[$row['type']][] = $row['name'];
}

Then echo the HTML:

foreach($result as $type=>$list) {
    echo "<h1>".htmlspecialchars($type)."</h1>";
    foreach($list as $food) {
        echo "<p>".htmlspecialchars($food)."</p>";
    }
}

Upvotes: 1

Related Questions