php mysql 取最小值_使用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!

解决方案

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

}