How can I select multiple IDs on one MySQL query?

Databases
1

Please I am a MySQL novice and I will like to know why

SELECT * FROM mydb WHERE id = 5 AND id = 6;

fails? And what can I do instead!

2

Hi nkarimbicho, welcome to the forum

What does the mydb table schema look like?

Can an id really be both 5 AND 6 ?

1 Like
3

No. I am trying to access two IDs upon one SELECT. Once the user chooses a couple of items on the web page, each item will be referenced by its name, and I will be identifying them via their ID and this will always be more than 1 item. Each item has a UNIQUE ID and I will like MySQL to process them as a whole.

4

Thanks for explaining. Yes, logical operators can seem rather illogical until you get used to them

AND means “this is true” and “this is true” which in this case can never be both.

Try
SELECT * FROM mydb WHERE id = 5 OR id = 6;
OR means “this is true” or “this is true” which in this case can be both.

1 Like
5

You could also use IN (5, 6)

Using IN you avoid classical AND/OR precedence errors and IMO it keeps the query easier to understand when the number of values to check increases

2 Likes
6

I have been to forums, but I must confess this is the first I will give a 5/5 for quality and spontaneity! You are awesome and thanks for letting me be a member!

1 Like
7

Hello out there, using IN() is pretty cool, I was thinking of using an array to store the integers and later pass them into the MySQL query but this is not possible, I think probably due to the fat that the array indices are not being referred…

How can I go about that? or even if there is another way I will be so glad.

8

Why is it not possible. You simply need to loop that array to construct the string that you will add to your query

9

No need to loop. Assuming you’re using PHP there is this neat function called implode :smiley:

10

Sorry if I am out of the scope of this thread Databases, it is in php!
Great I did this:

$num = array( 5, 6, 1);
foreach ($num as $number)
{
$eNum = implode(’ ,‘,$num);
}
$e = "SELECT * FROM test WHERE id IN(’“.$eNum.”')";

while ($data = mysql_fetch_array($e))
{
echo $data[‘id’].’ ‘.$data[‘name’].’
';
}

and I get this big PHP warning:
Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in C:\wamp\www\tutos\test\index.php on line 46
Call Stack

11

  1. Don’t loop through the $num array. Just implode it. The implode() function does all the work for you.

  2. Don’t put quotes around the $enum in your query. It’s supposed to be a list of values, not a string.

  3. You forgot to actually run the query, that’s why you get that error

  4. Don’t use mysql_ it’s deprecated (see the php manual). Use mysqli_ instead, or PDO

1 Like
12

You are right guido2004, I am just so scared that it won’t work such that I even forget to do it right.

13

I use PDO, but being a ‘novice- started with mysql_’ I still run my scripts there before implementing! Double work but I will win the phobia one day

1 Like
14

use “id=5 OR id=6”
or “id IN (5,6)”

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.