How to select multiple records (row) from a column in mysql?

Question

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

or

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

IN RESPONSE TO ALL ANSWERS

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;

The item_id is alphanumeric.

Answer 1

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;

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

Answer 2

Yes, both those should work fine. What's the actual problem you're seeing?

If, as you say, only one record is being returned, try:

select item_name from items order by item_id

and check the full output to ensure you have entries for 001, 012, 103 and 500.

If both those queries only return one row, I would suspect not.

If they all do exist, check the table definitions, it may be that the column is CHAR(4) and contains spaces for the others. You may have genuinely found a bug in MySQL but I doubt it.

After EDIT:

This is a perl/mysql problem, not an SQL one: mysql_fetch_array() returns only one row of the dataset at a time and advances a pointer to the next.

You need to do something like:

$query = "SELECT item_name from items WHERE item_id IN('s001','a012')";
$result = mysql_query($query, $conn) or die (mysql_error());
if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}
while ($row = mysql_fetch_assoc($result)) {
    echo $row["item_name"];
}