MySQL JOIN to arrange results for multiple HTML tables in PHP

Question

I have a MySQL database named "Problems":

  year  type  model
  ----  ----  -----
  2016  Cars  A
  2016  Cars  B
  2015  Cars  A
  2014  Cars  C
  2015  Vans  D
  2014  Vans  E

For different "types" of vehicles, it lists the "model" that has a problem and the year(s) in which these models have problems.

I need to create a separate HTML table for each type of vehicle. In each table, I need a row for each model and a column for each year. I will use "Y" in a cell to designate a problem, and a period (".") to designate no problem.

That is, I need to create the following two HTML tables:

HTML table for Cars:

    2016    2015    2014
A    Y       Y       .
B    Y       .       .
C    .       .       Y

HTML table for Vans:

    2016    2015    2014
D    .       Y       .
E    .       .       Y

I will be using mysqli in PHP to query the database and construct the tables. I suppose I could make a separate query for each table, but that doesn't seem like the most economical approach. Can a query be executed that will return ordered rows so that I can basically do an iteration pattern like this?

for type in types:
    // write start of <TABLE>
    for model in models:
        // write start of <TR>
        for year in years:
            // write <TD>
        // write close of <TR>
    //write close of <TABLE>

If it would make more sense to execute many different queries, please advise.

Sgt AJ · Accepted Answer · 2016-06-17 00:22:20Z

1

You could run a query something like this:

SELECT * FROM Problems ORDER BY VehicleType, VehicleModel, VehicleYear;

After that, loop through the results, using the logic you showed above. The query itself won't put the rows where you want, but your query results will be ordered in a way that allows you to loop through the results so the logic you laid out should work just fine.

answered Jun 17, 2016 at 0:22

Sgt AJ

8636 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

synaptik Over a year ago

The main problem with that, is that I won't know which "years" to put in the tables, since there won't always be a problem in every year for every "type".

Sgt AJ Over a year ago

Don't you want to list all the years anyway, with periods indicating no problems that year? (Like how you showed no problems in 2016 in your Van table above)

synaptik Over a year ago

Yes, I do. And with the method you suggested, how will I know the list of all the years?

Sgt AJ Over a year ago

Do you have a range of years you want to show data for to begin with? For example, from present to 10 years ago?)

synaptik Over a year ago

Yes, there will be a start year and end year (will only be querying a subset of the total set of years).

|

Kevin · Accepted Answer · 2016-06-17 01:41:42Z

You could definitely devise something from sql that does that outright, I can't think of it right now but another way would be to just reorganize the array such that it fits the format of that table presentation.

What I would to is just to group them according to the format.

First group them by type, then by model, then the year. Line them in that order.

$data[type][model][year]

Do note that you'll need to rearrange the array while fetching.

You can follow this idea. This will just give you a simple select:

$sql = 'SELECT * FROM problems';
$query = $con->query($sql);
while($row = $query->fetch_assoc()) {
    for($i = date('Y'); $i > 2013; $i--) {
        $data[$row['type']][$row['model']][$i][] = ($i == $row['year']) ? 'Y' : '';     }   
}

Note that Y and '' are just placeholders of value, you can have any type you have there.

After that grouping, you'll end up in this format, this would be easier to echo in your markup:

Array
(
    [cars] => Array
        (
            [A] => Array
                (
                    [2016] => 
                    [2015] => Y
                    [2014] => 
                )

            [B] => Array
                (
                    [2016] => Y
                    [2015] => 
                    [2014] => 
                )

            [C] => Array
                (
                    [2016] => 
                    [2015] => 
                    [2014] => Y
                )

        )

    [vans] => Array
        (
            [D] => Array
                (
                    [2016] => 
                    [2015] => Y
                    [2014] => 
                )

            [E] => Array
                (
                    [2016] => 
                    [2015] => 
                    [2014] => Y
                )

        )

)

Now you'll just go for it using a series of foreach. First loop goes to each table representing the type (cars, vans, etc.) since each of them needs their own table.

Then the model of each cars. Then lastly, which years have a value.

<?php foreach($data as $type => $v1) : ?>
    <h4><?php echo $type; ?></h4><!-- clasification -->
    <table id="<?php echo $type; ?>" cellpadding="10">
        <!-- year header -->
        <tr><th></th><?php foreach(array_keys(reset($v1)) as $year) echo "<th>{$year}</th>"; ?></tr>

        <?php foreach($v1 as $model => $v2): ?>
            <tr align="center">
                <td><?php echo $model; ?></td>
                <?php for($i = date('Y'); $i > 2013; $i--): ?>
                    <td><?php echo (!empty(array_filter($v2[$i]))) ? 'Y' : '.'; ?></td>
                <?php endfor; ?>

            </tr>
        <?php endforeach; ?>


    </table>
<?php endforeach; ?>

This would create a structure just like that illustration above. Do note that you'll need to do the design yourself.

Thanks. I would really like to know a pure SQL solution, so please let me know if it comes to you.

synaptik · Accepted Answer · 2016-06-17 02:35:53Z

After struggling mightily with this, I finally managed to come up with a pure SQL solution:

SELECT * FROM
(
    SELECT `type`, `model`, `year`, '.' AS flag
    FROM (
        SELECT * FROM
        (SELECT `type`, `model` FROM Problems GROUP BY `type`, `model`) tt
        CROSS JOIN
        (SELECT DISTINCT `year` FROM Problems) d
          ) x
    WHERE NOT EXISTS (
          SELECT 1
          FROM Problems h
          WHERE x.`year` = h.`year` AND x.`type` = h.`type` AND x.`model` = h.`model`
          )
    UNION
    SELECT `type`, `model`, `year`, 'Y' AS flag
    FROM Problems
    ORDER BY `type`, `model`, `year`
) z
WHERE `year` >= 2010 AND `year` <= 2017

I first use a CROSS JOIN to get every combination of (type,model) and (year). I then use WHERE NOT EXISTS to find those combinations that don't exist in the original table ("Problems"). Next, I combine those combinations that don't exist in the original table (having the flag='.') with the combinations that do exist in the original table (having the flag='Y'). Lastly, I filter by the date range 2010 through 2017.

Collectives™ on Stack Overflow

MySQL JOIN to arrange results for multiple HTML tables in PHP

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related