Mysql between operator

razmon

I need to check a range of integers in my sql database on 40 categories or columns.
I've used the between operator in the select statement but when I want to use it more than once in the select statement it doesn't give me any result yet no Mysql error. The code I'm useing is
$query = "select * from resorts where (pool between '$pool-10' and '$pool') and (sport between '$sport-10' and '$sport')";
both $pool and $sport have integer values assigned to them.
Can anyone please help me get around this problem. Greatly appreciated

phpjuggler

Hi,
I think you try to compare strings and not numbers. Please try your query without quotes.

Greetings
juggler

[deleted]

Putting quotes around numbers is never a good idea, but MySQL does interpret strings as numbers, so
BETWEEN '1-10' AND '20'
does work.

But to be surem I'd do it like this:

$query = "select * from resorts where (pool between ".($pool-10)." and ".$pool.") and (sport between ".($sport-10)." and ".$sport.")";

I also suggest you print $query to see if the variables contain what you think they contain, and check wether there really are records where both conditions are true. (that's still the number one cause of 'no results' in SQL queries)

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

razmon

Thanks for you reply. I've tried you suggested syntax and it comes up with mysql error. I think its because we're using " between the overal ".
The between statement works on its own and does return a row it's only when I use the between more than once in the same select statemet that it returns no rows, yet it should because it fits within the criteria of what's stored in the database.
Still very confusing I'm sure it's something simply yet I can't get it to work. Any other ideas would be greatly appreciated.
Kindest Regards
Ray

[deleted]

Odd... Did you try running the 'double between' query from the commandline?

Also, a fact you probably know, but which may shed some light on proper syntax:

The 'between' is a synonim for the < and > operators:

WHERE a BETWEEN 1 AND 5;

is the same as

WHERE ((a>1) AND (a<5))

Maybe that syntax willwork better?

razmon

Hi Vincent, Thanks for your help here. I have managed to get the between operator to work with the select statatement. However my problem is that there is a selection criteria of about 30 categories with radio buttons rated 10-50. I need to return a choice of three resorts closest to the users choices, and I'm now having difficulty getting those results. I am now on the path of creating temporary tables through a select statement and breaking down the values but at some point I'm going to have to merge all these temporary tables and to a sort of some kind that gives me the 3 resorts closest to their chooice. I'm having great difficulty doing this is I'm new to Mysql and I'm scripting in PHP which I'm still a novice.
I'm really at a loose end here at the moment and the deadline is a couple of days away.
My real question I guess is how do I break down my search criteria on these 30 categories which are rated 10-50 and produce a list of three results with the closest match.
Help Please - really appreciate your input
Kindest Ray

vincent wrote:

Odd... Did you try running the 'double between' query from the commandline?

Also, a fact you probably know, but which may shed some light on proper syntax:

The 'between' is a synonim for the < and > operators:

WHERE a BETWEEN 1 AND 5;

is the same as

WHERE ((a>1) AND (a<5))

Maybe that syntax willwork better?