CODEWITHSUNDEEP
phpmysql
Vano Nasaridze
Vano Nasaridze

Reputation: 33

Get data from mysql and group under categories

i get data from database with this srtucture

Cars   | Category   | 1Days |   3 Days  
-------|--------------|------–--|---------––
Car1   |  Off Road  | 50.00 | 90,00  
Car2   |  Off Road  | 50.00 | 90,00  
Car3   |  Minivan   | 50.00 | 90,00

I want to change it to

Off Road
Cars   | 1Days |    3 Days  
-------|----------|---––
Car1   |  50.00 | 90,00  
Car2   |   50.00 | 90,00  
Minivan
 Cars   | 1Days |   3 Days  
-------|----------|---––
Car3   |  50.00 | 90,00

I want to group tables under categories

Thank you for advance

Upvotes: 3

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You will probably have to handle this from your PHP presentation layer. Here is one approach:

$sql = "SELECT * FROM yourTable ORDER BY category, cars";
$result = $conn->query($sql);
$category = NULL;

echo "<table colspan=\"3\">";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        if ($category != $row["category"]) {
            echo "<tr><td colspan=\"3\">" . $row["category"] . "</tr>";
            echo "<tr><td>Cars</td><td>1 Days</td><td>3 Days</td></tr>";
            $category = $row["category"];
        }
        echo "<tr>";
        echo "<td>" . $row["cars"] . "</td>";
        echo "<td>" . $row["1days"] . "</td>";
        echo "<td>" . $row["3days"] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}

The above code uses logic which, upon seeing a not-yet-seen category in the result set, will print two header rows. The first header row just contains the category, and the second contains the headers for the three columns in your table.

An important note here is that we use ORDER BY category, cars in the MySQL query to impose the order we want in the result set data. Without using ORDER BY, our approach might not work.

Upvotes: 2

Related Questions