CODEWITHSUNDEEP
mysqlsql
user2981393
user2981393

Reputation: 519

Adding multiple WHERE .. AND statements to the same SQL query

I need to write a query that combines a variable number of AND WHERE statements.

One statement would look like

SELECT name
FROM users
WHERE field_id=16
AND value BETWEEN 1 AND 100

but I need to return one array of results from a variable amount of fields so field_id's of 16,18,20,25 with each field_id having a specific "AND value..." criteria.

How could I go about doing this to return on set of values?

I am also getting the criteria for the search from a html form and processing it using php (wordpress)

Upvotes: 0

Views: 121

Answers (2)

crthompson
crthompson

Reputation: 15865

If field_id needs to relate to the value field criteria, you need to AND your field_id and value values together while ORing each set of criteria.

SELECT name
FROM users
WHERE (field_id=16 AND value BETWEEN 1 AND 100)
OR (field_id=17 AND value between 101 and 199)
OR (field_id=18 AND value between 201 and 299)
OR (...

You may also be looking for an IN statement. Which would look like this.

SELECT name
FROM users
WHERE (field_id IN (16,17,18) AND value IN (100,200,300))
...

Fiddle

Upvotes: 1

Pulkit
Pulkit

Reputation: 137

You can use combination of AND and OR conditions:

SELECT name
FROM users
WHERE (field_id=16 AND value BETWEEN 1 AND 100) 
  OR (field_id=18 AND value BETWEEN 1 AND 150)
  OR (field_id=20 AND value BETWEEN 10 AND 50)
  OR (field_id=25 AND value BETWEEN 100 AND 500)
....

Upvotes: 0

Related Questions