Grouping the same rows for a column in PHP/MySQL
Assume that I have a table structure like seen below
location|name
-------------
NY|john
London|neil
NY|clair
I retrieve data using
SELECT * FROM table
The HTML
view file is as follows:
<table>
<tr><td>location</td><td>name</td>
<?php foreach($rows as $row):>
<tr><td><?=$row->location></td><td><?=$row->name></td>
<?php endforeach;>
</table>
As you can guess, it outputs every column for a row multiple times.
What I actually want is, to list them 开发者_开发问答as,
NY
john
clair
London
neil
It would also be great if I can use alternating colors between NY and London.
ps: I have too many things to group, simple workarounds will not fit.
For your example, I would do:
SELECT * FROM table ORDER BY location
Now, the locations will be in groups. You can add more of these columns comma-separated.
Next, for the PHP:
<?php $lastLocation = 'havenotseenityet'; ?>
<table>
<tr><td>location</td><td>name</td>
<?php foreach($rows as $row):>
<tr>
<?php if ($lastLocation != $row->location) {
$lastLocation = $row->location;
?>
<td><?=$row->location></td><td><?=$row->name></td>
<?php
} else {
?>
<td></td><td><?=$row->name></td>
} ?>
<?php endforeach;>
</table>
What we're doing here is getting the rows so that the locations that are the same are next to each other (and sorted -- being next to each other is a side effect). Then we're tracking the last location we saw, and when we see a new one, we show the location, and otherwise we hide it till we see a new one. This can be extended to any number of items, though remember that if you sort by state, city
, then when you see a different state, you have to make city not seen too.
Finally, note that I didn't start with $lastLocation = null. That is because null is a valid value for DB columns, and you don't want to trip up on that.
in the query try using DISTINCT location
If you want to use a table with alternating colors for the table rows, I suggest that you use the frontend framework Bootstrap. Read more about this here.
For another similar issue with alternating row colors ("striped rows"), with a solution using Bootstrap, please see Alternating Row Colors in Bootstrap 3 - No Table
Note: The first link relates to Bootsrap v.4, and the second relates to Bootstrap v.3. Both should give you an understanding of the striped rows feature, independent of Bootstrap version.
精彩评论