Mysql group query with nested format
I have searched for older streams, but have not yet found a solution for the following problem: is it possible to group and order mysql results without resorting to nested queries?
See example below.
I have:
Month Jan, Location USA, Program DDD Month Jan, Location UK, Program EEE Month Jan, Location USA, Program LLL Month FEB, Location UAE, Program EEE Month FEB, Location USA, Program DDD Month FEB, Location UK, Program MMM Month MAR, Location USA, Program FFF Month MAR, Location UAE, Program FFF Month MAR, Location UK, Program FFF
I want them to display like this:
+---------++---------++---------+ |Month Jan||Month Feb||Month Mar| +---------------+---------++---------++---------+ |Location USA | || || | +---------------+---------++---------++---------+ | |DDD ||DDD ||FFF | +---------------+---------++---------++---------+ | |EEE || || | +---------------+---------++---------++---------+ | |LLL || || | +---------------+---------++---------++---------+ |Location UK | || || | +---------------+---------++---------++---------+ | |EEE ||MMM ||FFF | +---------------+---------++---------++---------+ |Location UAE | || || | +---------------+---------++---------++---------+ | | ||EEE ||FFF | +---------------+---------++---------++---------+
I tried to group the query, but I cannot display it in the HTML table.
SELECT
event.`event_id`,
event.`event_program_id`,
event.`event_month`,
event.`event_location_id`,
location.`location_name`,
program.`program_name`,
program.`program_shortname`
FROM
`event`
LEFT JOIN
`location`
ON
event.`event_location_id` = location.`location_id`
LEFT JOIN
`program`
ON
event.`event_program_id` = program.`program_id`;
His ad currently looks like this:
column1 column2 column3
line1 34
line2 34
line3 34
line5 34
Where I wanted like this:
column1 column2 column3
line1 34 34
34 34
34
line2 34
34 34 34
line3 34
line5 34
+3
source to share
1 answer
You can do some internal queries to achieve this for each column, if you have a predefined number of columns in the expected result, something like months of a month.
I had some experience with SQL based reports that used subqueries like this, in the end this solution predicted that it was a pain to manage :) Today I just read the data, stored it in a matrix in memory and in the end generate the result based on a matrix, it is much easier to change in the future.
greetings!
here is an example in PHP
<pre>
<?php
$columns = array();
$data = array();
// add the data from the database
$data["line1"]["column1"] += 34;
$data["line2"]["column1"] += 34;
$data["line3"]["column4"] += 34;
$data["line5"]["column2"] += 34;
$data["line1"]["column1"] += 34;
$data["line3"]["column4"] += 34;
// find the columns
foreach ($data as $line => $column) {
foreach ($column as $cname => $value) {
if( ! in_array($cname, $columns) ){
$columns[] = $cname;
}
}
}
sort($columns);
// display column names
echo " \t";
foreach ($columns as $index => $cname) {
echo $cname . "\t";
}
echo "\n";
// display the data
foreach ($data as $line => $column) {
echo $line . "\t";
foreach ($columns as $index => $cname) {
echo $column[$cname] . "\t";
}
echo "\n";
}
?>
</pre>
Here's another way to solve the problem
<pre>
<?php
$columns = array();
$data = array();
// add the data from the database
// sort the data in the SQL
$data[] = array("l" => "line1", "c"=> "column1", "v" => "AAA");
$data[] = array("l" => "line1", "c"=> "column1", "v" => "BBB");
$data[] = array("l" => "line1", "c"=> "column3", "v" => "CCC");
$data[] = array("l" => "line2", "c"=> "column2", "v" => "AAA");
$data[] = array("l" => "line3", "c"=> "column2", "v" => "AAA");
// find the columns
foreach ($data as $line => $column) {
if( ! in_array($column["c"], $columns) ){
$columns[] = $column["c"];
}
}
sort($columns);
// display column names
echo " \t";
foreach ($columns as $index => $cname) {
echo $cname . "\t";
}
echo "\n";
$name = '';
$count = 0;
// display the data
foreach ($data as $line => $column) {
if( $column['l'] == $name ){
$count ++;
} else {
$name = $column['l'];
$count = 0;
}
if( $count == 0 ){
echo $name . "\t";
} else {
echo " \t";
}
foreach ($columns as $index => $cname) {
if( $column['c'] == $cname ){
echo $column['v'] . "\t";
} else {
echo " \t";
}
}
echo "\n";
}
?>
</pre>
another link using html as output
<table border="1">
<?php
$columns = array();
$data = array();
// add the data from the database
// sort the data in the SQL
$data[] = array("l" => "line1", "c"=> "column1", "v" => "AAA");
$data[] = array("l" => "line1", "c"=> "column1", "v" => "BBB");
$data[] = array("l" => "line1", "c"=> "column3", "v" => "CCC");
$data[] = array("l" => "line2", "c"=> "column2", "v" => "AAA");
$data[] = array("l" => "line3", "c"=> "column2", "v" => "AAA");
// find the columns
foreach ($data as $line => $column) {
if( ! in_array($column["c"], $columns) ){
$columns[] = $column["c"];
}
}
sort($columns);
// display column names
echo "<tr>";
echo "<td> </td>";
foreach ($columns as $index => $cname) {
echo "<td>".$cname."</td>";
}
echo "</tr>";
$name = '';
$count = 0;
// display the data
foreach ($data as $line => $column) {
echo "<tr>";
if( $column['l'] == $name ){
$count ++;
} else {
$name = $column['l'];
$count = 0;
}
if( $count == 0 ){
echo "<td>".$name."</td>";
} else {
echo "<td> </td>";
}
foreach ($columns as $index => $cname) {
if( $column['c'] == $cname ){
echo "<td>".$column['v']."</td>";
} else {
echo "<td> </td>";
}
}
echo "</tr>";
}
?>
</table>
+3
source to share