使用PHP/MySQL从多个列中选择最小值 [英] Select Smallest Value From Multiple Columns with PHP/MySQL

问题描述

我有一个设置如下的表

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

除ID外，所有列均为TEXT.

Other than ID all columns are TEXT.

使用PHP和MySQL，我需要从COL1，COL2，COL3中选择最小的数字"，在这种情况下，该数字应为100(来自第12行，COL1).

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

我尝试过:

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

但是我认为MIN(LEAST和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

有什么想法吗?谢谢！

推荐答案

我发现了两个错误.

1. SQL

您的查询适用于您的特定示例，但是尝试在COL2或COL3中使用较小的数字，然后您会发现它没有提供所需的结果.

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.

尝试以下方法:

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

2. PHP

为了在php中使用mysql查询，请查看手册关于如何将其归档，您的示例并非如此.如果您的查询字符串存储在$SQL中，则可能看起来像这样:

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

这篇关于使用PHP/MySQL从多个列中选择最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！