+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to list mysql results grouped by one column value, groups listed bellow (/showthread.php?tid=91452)
How to list mysql results grouped by one column value, groups listed bellow - Miha71 - 08-12-2024
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; ?>RE: How to list mysql results grouped by one column value, groups listed bellow - luckmoshy - 08-13-2024
https://codeigniter.com/user_guide/database/query_builder.html#query-grouping
RE: How to list mysql results grouped by one column value, groups listed bellow - christianberkman - 08-16-2024
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
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;
}