CODEWITHSUNDEEP
phpmysql
Chris Cummings
Chris Cummings

Reputation: 1548

Select Smallest Value From Multiple Columns with PHP/MySQL

I have a table set up as the following

ID COL1 COL2 COL3
----------------
10 200  n/a  125
11 150  130  n/a
12 100  300  200
13 500  n/a  n/a

Other than ID all columns are TEXT.

Using PHP and MySQL I need to select the minimum "number" from COL1,COL2,COL3, in this case it would be 100 (from row 12, COL1).

I tried this: 

$query = ("SELECT MIN(LEAST(COL1,COL2,COL3)) FROM rug AS 'query1'");

but I think something is wrong with the MIN(LEAST and also the AS 'query1' part. I'm getting a warning: 

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource

Any thoughts? Thanks!

Upvotes: 3

Views: 6707

Answers (3)

CAFxX
CAFxX

Reputation: 30291

I suspect using LEAST will result in table scans. You may want to break it down manually (obviously, I expect indexes to be present on COL1, COL2 and COL3):

SELECT ID, VALUE FROM (
  SELECT ID, COL1 AS VALUE FROM MYTABLE ORDER BY COL1 ASC LIMIT 1
  UNION
  SELECT ID, COL2 AS VALUE FROM MYTABLE ORDER BY COL2 ASC LIMIT 1
  UNION
  SELECT ID, COL3 AS VALUE FROM MYTABLE ORDER BY COL3 ASC LIMIT 1
) ORDER BY VALUE ASC LIMIT 1

Upvotes: 3

Chris Cummings
Chris Cummings

Reputation: 1548

I could not get least(min( to return any results, not the way I wanted to do it but I finally did part of the work in MySQL and part in php. Selected min from each row, filtered out the empty results (it returned n/a as empty) and then used PHP to sort and get lowest value. Here it is.

$query = ("SELECT LEAST(COL1,COL2,COL3) FROM rug WHERE COL1 != '' AND COL2!= '' AND COL3 != ''");
$result=mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
$num=mysql_numrows($result);
$i=0;
while ($i < $num) 
    {
    $pricing[$i]=mysql_result($result, $i);
    $i++;
    }
    sort($pricing);
    $lowest_price = $pricing[0]; //lowest price

Upvotes: 0

Bjoern
Bjoern

Reputation: 16304

I've discovered two errors.

1. SQL

Your query works for your specific example, but try using a smaller number in COL2 or COL3, then you'll discover it doesn't provide the results you're looking for.

Try this instead:

SELECT LEAST(
   MIN(COL1),
   MIN(COL2),
   MIN(COL3)
)
FROM yourtable;

2. PHP

In order to use a mysql query in php, please check the manual on how this can be archived, your example isn't how it is done. If your query string is stored in $SQL, then it could look like this:

/* querying... */
$result = mysql_query($SQL);

/* handling possible errors */
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

/* handling the response */
while ($row = mysql_fetch_assoc($result)) {
    var_dump($row);
}

Upvotes: 6

Related Questions