Asked  2 Years ago    Answers:  5   Viewed   90 times

I have a MySQL database that looks similar to this:

ID    Group   Name

1       1       John
2       1       Andrea
3       1       Jack
4       2       Mike
5       2       Kurt
6       3       Alice

I need to sort the results in a html table that looks like this:

Group       Name
-----------------------
  1         John
            Andrea
            Jack
-----------------------
  2         Mike
            Kurt
-----------------------
  3         Alice
-----------------------

I don't know if this should be done with a SQL query (concat_group, or something similar) or with PHP, can somebody please help me out?

Guys, thanks for the help below, but I also need to accomplish something similar, like this:

ID    meta_key  meta_value   name

1       group     1          John
2       group     1          Andrea
3       group     1          Jack
4       group     2          Mike
5       group     2          Kurt
6       group     3          Alice

and I need to sort / display the same as the example above, something like this:

group       name
-----------------------
  1         John
            Andrea
            Jack
-----------------------
  2         Mike
            Kurt
-----------------------
  3         Alice
-----------------------

Now my problem has taken new dimensions. My database looks like:

b.ID    b.meta_key  b.meta_value   a.title   

1       group       1                Title 1
2       group       1                Title 2
3       group       1                Title 3
4       group       2                Title 4
5       group       2                Title 5
6       group       3                Title 6
7       coef        6                Title 1
8       coef        4                Title 2
9       coef        12               Title 3
9       coef        2                Title 4
9       coef        3                Title 5
9       coef        7                Title 6

(I'm working with to tables)

And I need to achieve:

group       title         coef
---------------------------------
  1         Title 1        6 
            Title 2        2
            Title 3        12
--------------------------------
  2         Title 4        2
            Title 5        3
--------------------------------
  3         Title 6        7
--------------------------------

┬┐Is this even possible?

 Answers

3

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>
Wednesday, August 17, 2022
2

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
  }

}
Tuesday, December 6, 2022
 
pokrate
 
3

Use GROUP_CONCAT with GROUP BY. Try this -

SELECT `row`, `car_id`, `car_model`, GROUP_CONCAT(`car_features`, ',') 
FROM your_table GROUP BY `car_id`
Wednesday, September 21, 2022
4

I doubled your given sample data to show how it works with multiple userIds.

See it working live in an sqlfiddle here.

select
userid, groupnum,
min(ping) as start_date,
max(ping) as end_date,
max(ping) - min(ping) as duration
from (
select
*,
@groupnum := if(@prevUser != userId, @groupnum + 1, @groupnum),
@groupnum := if(ping - @prevTS > 60, @groupnum + 1, @groupnum) as groupnum,
@prevUser := userid,
@prevTS := ping
from
Table1 t
, (select @groupnum:=1, @prevTS:=NULL, @prevUser:=NULL) vars
order by userid, ping
) sq
group by userid, groupnum
Friday, December 23, 2022
5

You should indeed use php to get this done. But since most of current system sepate logic from display, I'd use only one pass and not (NUMBER OF DAYS + 1) SELECTs, and prepare an array that I can reuse later for my display.

$query = "SELECT DATE_FORMAT(date, '%M %D') as d, name FROM yourtable ORDER BY date";
$foo=array();
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
    //some logic to test if it's safe to add the name
    $foo[$row['d']][]=$row['name'];
}

And then when i'd need it (through a template or your "view")

foreach($foo as $date => $events) {
    echo $date . ":nt";          
    echo implode(",nt", $events);
    echo "n";
}

so it fits the format you set to yourself.

Hope that helped

Wednesday, September 7, 2022
 
dral
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages