Damo Diggler Damo Diggler - 7 months ago 24
SQL Question

Display a table query but group a category and display subcategory horizontally

Here's my script so far..

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT Category, Genre FROM skills";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br> Category: ". $row["Category"]. " - Genre: ". $row["Genre"]. "<br>";
}
} else {
echo "0 results";
}

$conn->close();
?>


.

I am trying to get a display to read like:

Category: Genre, Genre, Genre. Category: Genre, Genre, Genre.

I have 15 different Categories in mySQL table, with anything between 5-15 genres for each category.

I have done something similar a while ago in Microsoft Access with the help of primary keys, but I am lost without access (now on Mac having to learn php & sql)

Answer

First use GROUP_CONCAT() function to concatenate all genres for each category, and then simply loop through the result set.

Here are the relevant references:

So you need to change your SQL query from

$sql = "SELECT Category, Genre FROM skills";

to

$sql = "SELECT Category, GROUP_CONCAT(Genre SEPARATOR ', ') as Genre FROM skills GROUP BY Category";

Here's the complete code:

$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT Category, GROUP_CONCAT(Genre SEPARATOR ', ') as Genre FROM skills GROUP BY Category";

$result = $conn->query($sql);

if ($result->num_rows > 0){
    while($row = $result->fetch_assoc()) {
        echo "Category: ". $row["Category"]. " - Genre: ". $row["Genre"]. "<br />";
    }
}else {
    echo "0 results";
}

$conn->close();