Select Smallest Value From Multiple Columns with PHP/MySQL

Question

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!

The error message is not coming from that row. It's from the row where you use `mysql_numrows()`. Please show the full code you have. — JJJ, Mar 05 '12 at 17:14
Please show the code using `mysql_numrows`. The error is very descriptive in pointing out that you are attempting to use `mysql_numrows` with something that is NOT an sql query result object. — Julien, Mar 05 '12 at 17:15
Using "AS" and then assigning a string value should throw a syntax error. Try `SELECT MIN(LEAST(COL1,COL2,COL3)) FROM rug AS query1`. — thetaiko, Mar 05 '12 at 17:27
The error msg seems to be throwing at numrows because its trying to get the number of rows from the result but the result isn't valid. The error is in the query — Chris Cummings, Mar 05 '12 at 18:33

score 6 · Answer 1 · answered Mar 05 '12 at 17:25

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);
}

Well, it works for my test enviroment. Please provide more details about your table structure to further analyze this. — Bjoern, Mar 05 '12 at 19:10

score 3 · Answer 2 · answered Mar 05 '12 at 19:13

3

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

answered Mar 05 '12 at 19:13

CAFxX

28,060
6
41
66

This works better for my situation where I need the minimum value across columns in multiple tables where there might not be matching records. This solution avoids ugly and inefficient full outer joins. – Louise Eggleton Oct 03 '14 at 19:34

score 0 · Answer 3 · answered Mar 05 '12 at 18:58

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

Select Smallest Value From Multiple Columns with PHP/MySQL

3 Answers3

Linked