如何从mysql的列中选择多个记录(行)? [英] How to select multiple records (row) from a column in mysql?

问题描述

我想从这些ID中显示四(4)个项目的名称: 我可以这样吗?

I want to display four (4) items'name from these id: Can I do like this?

SELECT item_name from items WHERE item_id IN ('001', '012', '103', '500')

或

SELECT item_name from items WHERE item_id = '001' or item_id = '012' or item_id = '103' or item_id = '500'

响应所有答案

好吧，大多数答案都说它可行，但实际上却行不通.这是我的代码:

Well, most of the answers said it works, but it does not really work. Here is my code:

$query = "SELECT `item_name` from items WHERE item_id IN('s001','a012','t103','p500')";

$result = mysql_query($query, $conn) or die (mysql_error());
$fetch =  mysql_fetch_assoc($result) or die (mysql_error());
$itemsCollected = $fetch['item_name'];
echo $itemsCollected;

item_id是字母数字.

推荐答案

您可以执行任一操作，但是IN查询对于任何大型查询而言，效率更高.很久以前，我做了一些简单的测试，发现使用IN构造大约要快10倍.如果您要询问语法是否正确，则可以，看起来不错，除了缺少分号来完成语句.

You can do either one, but the IN query is much more efficient for this purpose for any large queries. I did some simple testing long ago that revealed it's about 10 times faster to use the IN construct for this. If you're asking if the syntax is correct then yes, it looks fine, other than missing semi-colons to complete the statement.

编辑:您似乎在问的实际问题是为什么这些查询只返回一个值".好吧，看看您发布的示例代码，问题就在这里:

EDIT: It looks like the actual question you were asking was "why do these queries only return one value". Well, looking at the sample code you posted, the problem is here:

$fetch = mysql_fetch_assoc($result) or die (mysql_error());
$itemsCollected = $fetch['item_name'];
echo $itemsCollected;

您需要循环遍历，直到没有其他要获取的结果为止，如 mysql_fetch_assoc :

You need to loop through and iterate until there are no more results to be fetched, as Pax pointed out. See the PHP manual page for mysql_fetch_assoc:

$sql = "SELECT item_name from items WHERE item_id IN('s001','a012')";
$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);

这篇关于如何从mysql的列中选择多个记录(行)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！