Multiple WHERE conditions

MySQL allows you to perform more complicated queries by using AND and OR in your WHERE clause to tie conditions together. You can also use brackets to form groups of equations through two main processes - using AND/OR (plus brackets) to make your queries more specific, and using the JOIN keyword to merge tables together.

Using AND and OR as well as brackets, you can form complex queries with little fuss. Here is a basic example to find someone with FirstName "John" and LastName "Smith".

SELECT * FROM usertable WHERE FirstName = 'John' AND LastName = 'Smith';

Here MySQL will only return rows that meet both requirements. If we wanted MySQL to return any row that had "John" as FirstName or "Smith" as LastName, it is as simple as changing the AND to an OR:

SELECT * FROM usertable WHERE FirstName = 'John' OR LastName = 'Smith';

That would return records such as "John Jones", "Karen Smith". If we wanted only records that definitely had John as FirstName, but could be either "Jones" or "Smith" as LastName, we would need to use brackets, like this:

SELECT * FROM Users WHERE FirstName = 'John' AND (LastName = 'Smith' OR LastName = 'Jones');

This time the FirstName condition must match as well as either LastName "Smith" or LastName "Jones" - "John Connor" will not match. For the final example of AND and OR, here's a snippet of code that will match one of four possibilities: John Smith, John Jones, Jennifer Smith, or Jennifer Jones:

SELECT * FROM Users WHERE (FirstName = 'John' OR FirstName = 'Jennifer') AND (LastName = 'Smith' OR LastName = 'Jones');

AND and OR really aren't difficult at all, particularly seeing as MySQL will sort out bracket problems quite easily- you can use more brackets than is required, but it will still work out OK. When using complex queries with AND or OR, consider trying them out in the MySQL monitor first to make sure you have got the query right before trying them in your PHP scripts.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Grouping rows together with GROUP BY >>

Previous chapter: A working example

Jump to:

Home: Table of Contents