PHP / SQL grouping

Question

I have such a problem that I would like to group and list all car models only once, my problem looks like this:

I wish it looked like this:

MySQL:

$all_model = $mysqli->query("SELECT mark, mark_url, model, model_url, engine, engine_url, generation, generation_url, year_production, body_car, body_car_url FROM auta WHERE mark_url = '$code_curr[3]' and model_url = '$code_curr[4]' GROUP BY generation, body_car");

PHP:

<?php foreach ($all_model as $value): ?>
    <div class="col-lg-3">
        <div class="crand-car-container">
            <?php echo "Generation:" . $value["generation"]; ?>
            <a href="/<?php echo $value['mark_url'] ?>/<?php echo $value['model_url'] ?>/<?php echo $value['generation_url'] ?>/<?php echo $value['body_car_url'] ?>"><?php echo $value['mark'] . " " . $value['model'] . " " . $value['year_production'] . " [" . $value['generation'] . "]" ?></a>
        </div>
    </div>
<?php endforeach; ?>

You want to implement a simple _control break_ … Compare generation of the current record with that of the previous one, and then depending on whether they match or not, react accordingly. — CBroe, Aug 25 '20 at 14:03
May be these will help you [Codeigniter - Combining Three tables into correct view format](https://stackoverflow.com/questions/23243273/codeigniter-combining-three-tables-into-correct-view-format/23243935#23243935), [Query for post and tags in same query](https://stackoverflow.com/questions/31766576/query-for-post-and-tags-in-same-query/31824955#31824955) or this [How to render multiple row in a cell in Datatables from a different table?](https://stackoverflow.com/questions/63535214/how-to-render-multiple-row-in-a-cell-in-datatables-from-a-different-table/63535635#63535635) — M Khalid Junaid, Aug 25 '20 at 14:08
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 25 '20 at 14:10

Jsowa · Accepted Answer · 2020-08-25T15:52:52.093

You should definitely use some template engine for your project, because it will get more complicated in the time and you will have bigger mess with mixed PHP and HTML.

My idea is to get data structure as you wish and then just iterate over two arrays. With template engine it will look prettier than my code.

<?php
$generations = [];
foreach ($all_model as $value) {

    if (!isset($generations[$value['generation']])) {
        $generations[$value['generation']] = [];
    }

    $generations[$value['generation']][] = $value;
}

foreach ($generations as $generation => $model): ?>
    <div class="col-lg-3">
        <div class="crand-car-container">
            <?php echo "Generation:" . $generation; ?>
            <?php foreach ($model as $value): ?>
                <a href="/<?php echo $value['mark_url'] ?>/<?php echo $value['model_url'] ?>/<?php echo $value['generation_url'] ?>/<?php echo $value['body_car_url'] ?>"><?php echo $value['mark'] . " " . $value['model'] . " " . $value['year_production'] . " [" . $value['generation'] . "]" ?></a>
            <?php endforeach; ?>
        </div>
    </div>
<?php endforeach; ?>

PHP / SQL grouping

1 Answers1