When to use the IN operator in your SQL WHERE statement

Sooner or later you will have to create a SQL statement with multiple WHERE conditions, usually AND and OR’s.

If you have three or more conditions your SQL can easily become difficult to read and difficult to understand the logic.

Your brain (and mine) can start to hurt as you wonder…

Are all those WHERE conditions returning the expected results?

Consider the following SQL query a reader recently sent to me.

(Not the actual query, but modified here by me for privacy for this example)

SELECT id, car, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND (engine != 'V12' OR engine != 'V10' OR engine != 'V8' OR engine != 'V6')
ORDER BY make, model

The reader wants all cars for sale at ‘Crumps cars’ dealership but don’t have a certain engine type, V12, V10, V8, V6.

Immediately I find the multiple conditions difficult to read and understand if the conditions are going to work together.

If there were more OR’s than above it’s only going to get worse…

What if you could save the brain ache and make your WHERE statement easier to write and read!

Well there is a simple way in my my mind and that’s using the IN comparison operator in your WHERE clause.

Let’s use it to rescue this SQL statement and rewrite it like this:

SELECT id, car, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine NOT IN('V12','V10','V8','V6')
ORDER BY make, model

Now you can simply have a comma seperated list of multiple values.
Each of which we don’t want the engine column to match with, by using the NOT IN operator.

Alternatively if you do want them to match just remove the NOT

Then every result will have a “V” engine from the list

SELECT id, car, make, model, price, description
FROM cars_for_sales
WHERE for_sale = 1
AND dealer = 'Crumps cars'
AND engine IN('V12','V10','V8','V6')
ORDER BY make, model

How to transfer data from one PHP page to another

How to transfer data from one PHP page to another

If you are starting out in PHP and just learning the basics, you’ll probably just start with one PHP page, but pretty soon you will want to add a second page. If you have a form on the first page, how do you pass that data to a second page.

This was illustrated recently by a question from a reader who emailed me to help with this problem:

Dear Jon, I’m struggling with getting data in my survey from one page to the next page in the survey, I don’t want to have one massively long page, so wanted to break the survey up into multiple pages, but then I need to store or pass the user data entered from one page to the next and finally to the end to save it, any ideas?
Thanks,
Reader A

In PHP you have three options, but typically you will probably end of using all three options:

  1. Sessions
  2. Databases
  3. POST / GET

Sessions are great to use if you aren’t passing a lot of data.
They do use up memory on the server however, so in the above case if you have a large survey and multiple users are using the application, a lot of memory could get consumed.

Databases are great for saving the data and having it persist beyond the end of the session.

POSTing the data between forms and storing it in hidden fields is another option, but again you may end with a large amount of data being posted between multiple pages.

The best option for a multipage survey will be to store it in the database as each page is submitted.