highlighting data values in a sql result set

Question

I can think of a number of ways to do this in PHP or even JavaScript, but I'm wondering if there's a SQL-based technique I'm overlooking.

I have a database table, let's say 20 fields X 10 rows. I want to display the entire table on an web page, so I'd do something like SELCT * FROM data_table;, and then format the result set using HTML table tags.

However, I'd also like to highlight values in the table based on whether they are the maximum or minimum value in their column. For example, I'd add bold tags around the max in each column. A resulting table might look something like this, with bold tags shown:

id |  field1  |  field2  |  field3  |  ...
0  |    5     |    2     | <b>7</b> |  ...
1  |    3     | <b>8</b> |    6     |  ...
2  | <b>9</b> |    5     |    1     |  ...
...

I could do a separate SELECT with an ORDER BY for each field and then interpret the results, but that seems like a lot of extra DB access.

My alternative right now is to just fetch the whole table, and then sort/search for the highlight values using PHP.

Is there a better way?

Answer 1

Best way I can think of:

$rowCount = 0;
$colorOne = '#ffffff';
$colorTwo = '#f3f3f3';

while($row = mysql_fetch_array($result)){
        $rowColor = ($rowCount % 2) ? $colorOne : $colorTwo; 
        echo "<tag bgcolor='$rowColor'></tag>";
$rowCount++;
}

Answer 2

Its not pretty, but it will do exactly what you ask for:

SELECT
    (CASE field1
        WHEN (SELECT MAX(field1) FROM data_table)
        THEN CONCAT('<b>',field1,'</b>')
        ELSE field1
    END) as field1,
    (CASE field2
        WHEN (SELECT MAX(field2) FROM data_table)
        THEN CONCAT('<b>',field2,'</b>')
        ELSE field2
    END) as field2
FROM data_table 

...repeat for other columns