How to list mysql results grouped by one column value, groups listed bellow

I have table "properties" with columns id, name, location, type, price, sq_ft and some other...

I want properties to be listed grouped by value of "type"column (i.e. values in this columns are Townhome, Appartment, Condo). But, with "type" values as titles of the groups, and dinamically so when I add new value in type column i.e. "Commercial" those properties are listed too. So, what I want is:

Townhome
Townhome Property 1
Townhome Property 2

Appartment
Appartment Property 1
Appartment Property 2

Condo
Condo Property 1
Condo Property 2.... and bellow this, after adding "Commercial" in type column

Commercial
Commercial Property 1
Commercial Property 2

So, basically, any new type of properties is dinamically added with its properties and with title of type.

I am doing this in CodeIgniter 4 and so far I managed to list properties only per one type per page/view.

Cotroller:

Code:

public function getByPropType($type)
{
    $properties = $this->db->query("SELECT * FROM properties WHERE type = '$type'")->getResult();

    return view('props/props-by-type', compact('properties', 'type'));
}

View:

PHP Code:

<?php foreach($properties as $prop) : ?>
    <div class="col-md-6 col-lg-4 mb-4">
        <div class="property-entry h-100">
            <a href="<?= url_to('prop.single', $prop->id); ?>" class="property-thumbnail">
                <div class="offer-type-wrap">
                    <span class="offer-type bg-success"><?= $prop->type; ?></span>
                </div>
                <img src="<?= base_url('public/assets/images/'.$prop->image.'') ?>" alt="Image"
                    class="img-fluid">
            </a>
            <div class="p-4 property-body">
                <h2 class="property-title"><a
                        href="<?= url_to('prop.single', $prop->id); ?>"><?= $prop->name; ?></a></h2>
                <span class="property-location d-block mb-3"><span class="property-icon icon-room"></span>
                    <?= $prop->location; ?></span>
                <strong
                    class="property-price text-primary mb-3 d-block text-success">$<?= $prop->price; ?></strong>
                <ul class="property-specs-wrap mb-3 mb-lg-0">
                    <li>
                        <span class="property-specs">Beds</span>
                        <span class="property-specs-number"><?= $prop->num_beds; ?></span>

                    </li>
                    <li>
                        <span class="property-specs">Baths</span>
                        <span class="property-specs-number"><?= $prop->num_baths; ?></span>

                    </li>
                    <li>
                        <span class="property-specs">SQ FT</span>
                        <span class="property-specs-number"><?= $prop->sq_ft; ?></span>

                    </li>
                </ul>

            </div>
        </div>
    </div>
<?php endforeach; ?>

https://codeigniter.com/user_guide/datab...y-grouping

Codeigniter First, Codeigniter Then You!!

yekrinaDigitals

Using your code as an example, I would to the following:

PHP Code:

// controller
$types = $this->db->query("SELECT DISTINCT type FROM properties")->getResult();

$items= [];
foreach($types as $type){
    $newItem['title'] = $types->type;
    $newItem['properties'] = $this->db->query("SELECT * FROM properties WHERE type = '$type'")->getResult();
    $items[] = $newItem
}
// this will produce
// [0] =>
//    [title] => Townhome
//    [properties => [...array of properties...]
// [1] =>
//    [title] => Condo
//    ....

// view
<?php foreach($items as $item): ?>
<h1><?=$item['title'];?></h1>
<ul>
<?php foreach($item['properties'] as $property): ?>
<li><?=$item;?></li>
<?php endforeach; ?>
<hr />
<?php endforeach;?>

However, a better practice would be to

• make seperate tables for "property_types" and "properties"
  
• have a foreign key "property_type" referencing the "property_types" table
  
• Use CI4's model
  

Then the controller code could be

PHP Code:

// controller
$typeModel = model('PropertyTypeModel');
$propertyModel = model('PropertyModel');

$items = [];
foreach($typeModel->findAll() as $type){
    $newItem['type'] = $type->name;
    $newItem['properties'] = $propertyModel->where('property_type', $type->name)->findAll();
    $items[] = $newItem;
}