How to Select Smallest Value From Multiple Columns with PHP

Question

I have a table with the following:

    6xx     8xx      9xx     11xx      12xx
  1  0.01    0.002    0.004   0.001     0.025
  2  0.025   0.125    0.002   0.01      0.011

I would like to find the Smallest Value from the column make that column to be green color.

For example in 1st the smallest value is 0.001 so i want it to be green color, for second 0.002 is smallest value i want it to be green color.

can any one guide me how to make this ,thanks

below is the code how i selecting it from database and displaying int in a table

<?php

$dbHost = 'localhost'; // usually localhost
$dbUsername = 'xxxx';
$dbPassword = 'xxxx';
$dbDatabase = 'xxxx';
$db = mysql_connect($dbHost, $dbUsername, $dbPassword) or die ("Unable to connect to Database Server.");
mysql_select_db ($dbDatabase, $db) or die ("Could not select database.");



$ColumnNames = mysql_query("SELECT column_name 
                              FROM information_schema.COLUMNS 
                             WHERE table_name = 'supplierprice' 
                               AND column_name NOT IN ('supp_price_id',
                                                       'region',
                                                       'country',
                                                       'net_id',
                                                       'networkname', 
                                                       'mcc', 
                                                       'mnc', 
                                                       'mnp')")
                or die("mysql error"); 

$columnArray=array();
$i=0;
while($rows=mysql_fetch_array($ColumnNames))
{

$columnArray[]=$rows[0];

echo "<th style='width:67px;' class='. $columnArray[$i] .' >" . $columnArray[$i] . " 
            </th>";
$i++;
}

?>

foreach($columnArray as $value) {


//$columnArray[]=$rows1[0];

echo '<td style="width:67px;font-weight:'.$text.'" id="CPH_GridView1_xxx" width="0px;" class="'.$value.' '.$rows["net_id"].'"><p>'.$rows[$value].'</p></td>';   
}

http://stackoverflow.com/questions/9570881/select-smallest-value-from-multiple-columns-with-php-mysql — Avinash Babu, Jul 22 '14 at 08:20

score 0 · Answer 1 · answered Jul 22 '14 at 08:19

one answer for your problem may be this:

1- extract minimum of each column by sql query like this:

$res1=mysql_query('select min(6xx) as min6, min(8xx)as min8, min(9xx) as min9, min(11xx)as min11, min(12xx) as min12 from tbl_name');

$rec1=mysql_fetch_array($res1);

$min6=rec1['min6'];

$min8=rec1['min8'];

$min9=rec1['min9'];

....

2- when you fetch information in html you should check if the value is like min then background (some css) become green:

$res=mysql_query('select * from tbl_name');
echo "<table>";
foreach($rec=mysql_fetch_array($res))
{
echo "<tr>"
    echo "<td";
        if($rec['6xx']==$min6) echo "class='green_cell' ";
    echo "";
        echo $rec['6xx'];
    echo "</td>";
    ....

echo "</tr>"

score 0 · Answer 2 · answered Jul 22 '14 at 09:19

Not entirely sure on your requirements. You appear to be getting some column names for a table. I presume that you want to get the values of these columns and display them in a table, highlighting the one with the lowest value.

If some something like this. Gets the columns, loops through them once to display the headings. For each row it then calls a function with displays them. The key of the lowest value is found (if 2 keys share the lowest value then the lowest key is used). It loops around the columns returned and echos them out, putting out a color:#ff0000; in the style for the lowest value column.

<?php

$dbHost = 'localhost'; // usually localhost
$dbUsername = 'xxxx';
$dbPassword = 'xxxx';
$dbDatabase = 'xxxx';
$db = mysql_connect($dbHost, $dbUsername, $dbPassword) or die ("Unable to connect to Database Server.");
mysql_select_db ($dbDatabase, $db) or die ("Could not select database.");

$ColumnNames = mysql_query("SELECT GROUP_CONCAT(CONCAT('`', column_name, '`')) AS some_columns
                              FROM information_schema.COLUMNS 
                             WHERE table_name = 'supplierprice' 
                               AND column_name NOT IN ('supp_price_id',
                                                       'region',
                                                       'country',
                                                       'net_id',
                                                       'networkname', 
                                                       'mcc', 
                                                       'mnc', 
                                                       'mnp')")
                or die("mysql error"); 

if($rows=mysql_fetch_array($ColumnNames))
{
    $sql = "SELECT ".$row['some_columns']." FROM supplierprice";
    $query_values = mysql_query($sql);
    if($rows2=mysql_fetch_assoc($ColumnNames))
    {
        echo "<tr>";
        foreach($rows2 AS $key=>$value)
        {
            echo "<th style='width:67px;' >".$key." </th>";
        }
        echo "</tr>";
        process_row($rows2);
        while($rows2=mysql_fetch_assoc($ColumnNames))
        {
            process_row($rows2);
        }
    }
}

function process_row($in_row)
{
    $lowest_values_key = min(array_keys($in_row, min($in_row)));  
    echo "<tr>";
    foreach($in_row AS $key=>$value)
    {
        echo "<td style='width:67px;".(($lowest_values_key == $key) ? 'color:#ff0000;' : '' )."' >".$value." </th>";
    }
    echo "</tr>";
}

?>

How to Select Smallest Value From Multiple Columns with PHP

2 Answers2

Linked