Between value in query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mayo23
Forum Newbie
Posts: 4
Joined: Thu Nov 22, 2007 3:52 pm

Between value in query

Post by mayo23 »

Still learning, but need some help.

I want to create a form with a number of price ranges, where the results can be included in a query. For instance, checkbox 1 will be between 10 and 20, checkbox 2 will be between 20 and 30, etc.

I have been able to parse the form results, but am coming up with a problem when I try to put the values in a query. It works OK if one range is selected, but fails when two ranges are passed on. It doesn't seem to like running two between conditions in the query. For example;

Code: Select all

select * from products WHERE price BETWEEN '10' AND '20' AND price BETWEEN '30' AND '40'
Don't take the code too literally, but it gives you an idea of what I'm trying to achieve. It appears that you cannot put two BETWEEN conditions on the same field. Is this true, and if so, any suggestions on how to get around it.

Any experts out there that can point me in the right direction? Your help would be greatly appreciated.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Between value in query

Post by John Cartwright »

You can infact use between on multiple fields, however your logic is wrong. How can a price be between 10-20 and also be between 30-40? If you want to capture rows with a price between 10-20 or 30-40, then..

WHERE price BETWEEN '10' AND '20' AND price BETWEEN '30' AND '40'

to

WHERE price BETWEEN '10' AND '20' OR price BETWEEN '30' AND '40'
mayo23
Forum Newbie
Posts: 4
Joined: Thu Nov 22, 2007 3:52 pm

Re: Between value in query

Post by mayo23 »

Nothing wrong with my logic.

If you have a shop, and there are 100 products in a specific category, I want to be able to provide the customer with filter options (eg. manufacturer, price, etc.). If I provide a range of checkbox options, with the first saying "under £10", the second saying "£10 to £20", and so on, and the customer ticks 2 checkboxes because they only want to see products that are between £10 - £20, and also want to see products that are between £50 and £100 (for whatever reason), then I want the corresponding query to select all the products that are within those ranges, and that requires two different range look ups in the query.

Done some testing with MySQL queries, and it seems that MySQL doesn't like it when you do two "BETWEEN" conditions, but is OK if you do two "NOT BETWEEN" conditions on the same field.

Anyone have any suggestions on how to get round this problem.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Between value in query

Post by Eran »

Read again John's suggestion, he gave you the solution. What sounds logical to you using natural language does not necessarily translate to computer logic - http://en.wikipedia.org/wiki/Logic_gate#Logic_gates (look at the AND and OR meanings)
Post Reply