I have this issue in which I have two fields in my table called 's_power_from' and 's_power_to' (both set to int) which I use to search for results depending on a certain criteria being met.
The criteria is based on two user-entered values which is checked to see if the entered falls within the range of the fields 's_power_from' and 's_power_to'
A couple of examples so you can understand.
E.g. 1
In my Database:
s_power_from = 2000
s_power_to = 6000
User entered range to check between:
From: 1000
To: 2000
SQL: WHERE s_power_from BETWEEN 1000 AND 2000 OR s_power_to BETWEEN 1000 AND 2000
The above SQL will work fine and the record is displayed
E.g. 2
In my Database:
s_power_from = 2000
s_power_to = 6000
User entered range to check between:
From: 3000
To: 4000
SQL: s_power_from BETWEEN 3000 AND 4000 OR s_power_to BETWEEN 3000 AND 4000
The above SQL does not work as 2000-6000 does fall within the 3000-4000 range
(one of both values should should fall within this criteria, in this case s_power_to = 6000)
I sort of know to why this this is happening but cant get my head around on how to amend the SQL to cater for this requirement.
I'm using an old version of MySQL 3.23, so i'm not sure if that makes any difference to the solution?
Thanks a lot
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.