Welcome Guest, Not a member yet? Register   Sign In
How to list mysql results grouped by one column value, groups listed bellow
#3

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;

Reply


Messages In This Thread
RE: How to list mysql results grouped by one column value, groups listed bellow - by christianberkman - 08-16-2024, 12:44 PM



Theme © iAndrew 2016 - Forum software by © MyBB