MySQL (Or PHP) Group Results by Field Data

MySQL (or PHP?) group results by field data

This would my solution, althoug is not elegant at all

<?php
$dbc = new MySQLI(DBHOST,DBUSER,DBPASS,DB);
$result = $dbc->query("
SELECT
p.Group as 'group',
GROUP_CONCAT(name) as names
FROM prueba p
GROUP BY p.Group
");
?>
<table>
<tr>
    <th>Group</th>
    <th>Name</th>
</tr>
<?php while($row = $result->fetch_assoc()){
    $names = split(",",$row["names"]);
?>
    <tr>
        <td><?php echo $row["group"] ?> </td>
        <td><?php echo $names[0]; array_shift($names) ?></td>
    </tr>
    <?php foreach( $names as $name){ ?>
        <tr>
            <td></td>
            <td><?php echo $name ?></td>
        </tr>
    <?php } ?>
<?php } ?>
</table>

PHP/MySQL group results by column

Like mluebke commented, using GROUP means that you only get one result for each category. Based on the list you gave as an example, I think you want something like this:

$sql = "SELECT * FROM products WHERE category IN (10,120,150,500) GROUP BY category ORDER BY category, id";
$res = mysql_query($sql);

$list = array();
while ($r = mysql_fetch_object($res)) {
  $list[$r->category][$r->id]['name'] = $r->name;
  $list[$r->category][$r->id]['whatever'] = $r->whatever;
  // etc
}

And then loop through the array. Example:

foreach ($list as $category => $products) {
  echo '<h1>' . $category . '</h1>';

  foreach ($products as $productId => $productInfo) {
    echo 'Product ' . $productId . ': ' . $productInfo['name'];
    // etc
  }

}

MYSQL and PHP group results by field data

First, try to keep logic away from layout. It's generally good practice to first gather the data you need, and then display it.

Using GROUP_CONCAT can make things more complicated, since you do not know how many results you will get for each student, nor will you be able to easily identify which marks are of what type.

With that in mind I've created a solution. You'll still need to extend the query of course.

$query = 'SELECT student, type, marks FROM log';
$res = mysqli_query($query);
$studentMarks = array();

while ($row = mysqli_fetch_array($res, MYSQLI_ASSOC))
{
    $studentMarks[$row['student']][$row['type']] = $row['marks'];
}

// Now $studentMarks should look like:
// $studentMarks = array(
//    23494 => array('CAT1' => 50, 'CAT2' => 35)
//  , 23495 => array('CAT1' => 20, 'MIDTERM' => 40)
// );    

echo '<table><thead><tr>';
echo '<td>Student</td><td>CAT1</td><td>CAT2</td><td>MIDTERM</td>';
echo '</tr></thead><tbody>';

foreach($studentMarks as $studentId => $marks)
{
    echo '<tr>';
    echo '<td>', $studentId, '</td>';
    echo '<td>', (isset($marks['CAT1']) ? $marks['CAT1'] : ' '), '</td>';
    echo '<td>', (isset($marks['CAT2']) ? $marks['CAT2'] : ' '), '</td>';
    echo '<td>', (isset($marks['MIDTERM']) ? $marks['MIDTERM'] : ' '), '</td>';
    echo '</tr>';
}
echo '</tbody></table>';

Get sql query result group by a column data

You can try this as you are using codeigniter
there is a mysql function called GROUP_CONCAT
learn more about it and you will understand

    $this->db->select('mo.id, mo.id_land_owner as id_land_owner, mo.group_name, GROUP_CONCAT(mu.username SEPARATOR ",") as group_users', false);
    $this->db->from('maintenance_users as mu');
    $this->db->join('maintenance_owner as mo','mu.id_maintenance = mo.id_mu');
    $this->db->group_by('mo.group_name'); 
    $this->db->where('mo.id_land_owner', 152);
    return $this->db->get()->result();

how to group result in subgroups in php

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("mydbname");

$sql    = 'SELECT * FROM table_name';
$result = mysql_query($sql);

$data = array();
while ($row = mysql_fetch_assoc($result)) {
    if ( empty($data[ $row['Country'] ]) ) {
        $data[ $row['Country'] ] = array();
    }

    if ( empty( $data[ $row['Country'] ][ $row['Books'] ] ) ) {
        $data[ $row['Country'] ][ $row['Books'] ] = array();
    }

    $data[ $row['Country'] ][ $row['Books'] ][] = $row['Book_price'];
}

$totalSum = 0;
foreach ( $data as $country => $books ) {

    echo '<b>' . $country . '</b><br/>';

    $totalCountry = 0;
    foreach ( $books as $book => $prices ) {
        $sum = array_sum( $prices );

        echo '<u>' . $book . '</u><br/>';

        echo implode(',', $prices) . '<br/>;

        echo 'Total ' . $book . ':' . $sum . '<br/>';

        $totalCountry += $sum;
    }

    echo 'Total ' . $country . ':' . $totalCountry . '<br/>';

    echo '<hr/>';

    $totalSum += $totalCountry;

}

echo 'TOTAL GEN: ' . $totalSum;

Group mysql results in groups of four

You can make use of the modulus-operator to group your rows into groups of 4, like this:

$i=0;
while($row = ...) {
    if($i%4 == 0) { // % = modulus operator. This returns the remainder of a division, so 1%4 = 1 (because it's 0+1/4), while 5%4 also returns 2 (5%4 = 1+1/4)
       if($i > 0) {
          echo '</div>';
       }
       echo '<div>';
   }
   echo $row;
   $i++;
}
echo '</div>';

This will group your results in sets of 4 like so:
<div>row 1 row 2 row 3 row 4</div><div>row 5 row 6 row 7 row 8</div> etc.

How can I group rows with the same value from mySQL into php?

MySQL has a non-standard SQL function called GROUP_CONCAT specifically to do this.

Use as:

SELECT  animal, GROUP_CONCAT(name) as grouped_name  FROM animals GROUP BY animal

Use in PHP:

 $sql = ' SELECT  animal, GROUP_CONCAT(name) as grouped_name  FROM animals GROUP BY animal';
 foreach ($pdo->query($sql) as $row) {
     echo '<td>'.$row['animal'].' </td>';
     echo '<td>'.$row['grouped_name'].' </td>'; 
 }

Note how the column with the group is renamed/aliased to grouped_name this is because that column is not the name column anymore. You can refer to this column by its alias grouped_name in your sql result.

PHP MySQL Group By result in array

Use GROUP_CONCAT():

SELECT cl, GROUP_CONCAT(typ) FROM logo GROUP BY cl

See it on sqlfiddle.

---

Related Topics