Proper use of where in mysql?

Question

I am beginning to study php and my sql.

I don't know what is wrong with this code.

$result = mysql_query("SELECT * FROM tbl where field1 (1,2,3)");

I am getting this error.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in test.php on line 52

When I replace that particular line with this

$result = mysql_query("SELECT * FROM tbl");

It works fine but it returns all rows.

I am trying to filter the rows display using where command in mysql.

What is the proper syntax for that?

Thanks

Answer 1

You need an IN clause:

$result = mysql_query("SELECT * FROM tbl where field1 IN (1,2,3)");

This query is analogous to:

SELECT * FROM tbl where field1=1 OR field1=2 OR field1=3

Also, please keep in mind mysql_query has been deprecated and its use is discouraged.

Answer 2

First off.. stop using the mysql extension as it's deprecated...

Aside from that, you do something like this:

$result = mysqli_query("SELECT * FROM tbl WHERE field IN (1,2,3)");

And here are a few other examples that aren't exactly for your case...

$result = mysqli_query("SELECT * FROM tbl WHERE date BETWEEN "2012-06-01 00:00:00" AND "2012-06-12 23:59:59");

http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html will give you some information on the WHERE clause itself and how to properly utilize and optimize it..

Hope it helps.

Answer 3

Try this:

$result = mysql_query("SELECT * FROM tbl where field1 IN (1,2,3)");