searching a number range using BETWEEN

[An7hony]

I have read that i can search for a number within a mysql database using BETWEEN

 

i.e: SELECT  * FROM `table_name` WHERE `field_name` BETWEEN 99 AND 200

 

How would i do this with the code below?

 

<?php
$salary = explode(":", $_GET['salary_to']);
	 }
$types[] = (isset($_GET['salary_to']) && strlen(trim($_GET['salary_to'])) > 0) ?
     "`salary_from` LIKE '%". mysql_real_escape_string(trim($salary[0])) ."%' AND `salary_to` LIKE '%". mysql_real_escape_string(trim($salary[1])) ."%'" : ''; 
?>

 

Thanks for any help

[Adam]

You can't use BETWEEN in that situation because you don't have an exact value. You need to return results where `salary_from` is more than or equal to $salary_from, and `salary_to` is less than or equal to $salary_to:

 

if (!empty($_GET['salary_to']))
{
    list($salary_from, $salary_to) = explode(':', $_GET['salary_to']);

    if (!empty($salary_from) && !empty($salary_to))
    {
        $types[] = "`salary_from` >= " . intval($salary_from)
                 . " AMD `salary_to` <= " . intval($salary_to);
    }
}

** I was assuming here by the way that your values would be integers.

[An7hony]

cheers,

 

I have one more question

 

Say i make a search like this:

 

SELECT id, title, description, salary_from, salary_to FROM jobs WHERE `title` LIKE '%fund%' AND `salary_from` >= 15001 AND `salary_to` <= 20000 ORDER BY id DESC

 

Why doesnt it pull a result for:

 

16000 - 22000

 

[Adam]

If you remove the other WHERE clauses, just leaving the salary_from/to stuff, does it work?

[Psycho]

Say i make a search like this:

 

SELECT id, title, description, salary_from, salary_to FROM jobs WHERE `title` LIKE '%fund%' AND `salary_from` >= 15001 AND `salary_to` <= 20000 ORDER BY id DESC

 

Why doesnt it pull a result for:

 

16000 - 22000

 

Simple, you query states the "salary from" must be equal to or greater than 15001 (which 16000 is) AND that the "salary to" must be less than or equal to 20000 (which 22000 is NOT)

 

Perhaps you want an OR statment - not sure since I don't know exactly what you are trying to find.

[Adam]

Wow.. overlooked that one. Putting it down to being the end of the day! 

[An7hony]

ok that makes sense, if its between 0 and 15000 it will find jobs with say 1000 - 15000.

But not jobs like the ones above

 

So if i replace AND with OR it should work, i'll give it a go

[Adam]

Wha? That's not what he was saying. He's saying the values you were trying were outside of the allowed min/max you set. Changing it to an OR operator will return everything, because any number is more than x OR less than y.

[An7hony]

yeah, your right

 

How would i find these middle values 

[Psycho]

Wha? That's not what he was saying. He's saying the values you were trying were outside of the allowed min/max you set. Changing it to an OR operator will return everything, because any number is more than x OR less than y.

 

OK, I think you are again having end of day confusion. The query is testing TWO separate, independant values. So, an OR does not mean all records will be returned. The "From" value can be above below some arbitrary value and that doesn't tell you if the To value will be above or below another arbitrary value.

 

@An7hony:

 

Why don't you exlain what you are trying to achieve. I suspect you are trying to find one of the two:

 

1. Records where the From and/or To values are within the range or

2. Records where the From / To values overlap the range (partially or entirely)

 

There is a difference. If the search value are 10,000 and 15,000 a record with 5,000 and 20,000 would not start/end within the range, but it would overlap the range.

[An7hony]

values which overlap the range

 

as if someone searches for a job and select 15000 - 20000 and there are jobs on there for 16000 - 22000. These should show up as 16000 is within the range even if 22000 overlaps

[An7hony]

is this possible?

[An7hony]

will it be ok to do:

 

if(isset($_GET['salary_to'])) {
		 $salary = explode(":", $_GET['salary_to']);
	 }
$types[] = (isset($_GET['salary_to']) && strlen(trim($_GET['salary_to'])) > 0) ?
     "`salary_from` >= " . intval($salary[0])
                 . " AND `salary_to` >= " . intval($salary[1]) ." " : '';

[An7hony]

it seemed to work but say my search is 15001 - 20000 it wont show a job which is 15000 - 22000

 

[Psycho]

Here is the logic you need to use. It might not look right, but this will find any records where the from/to range in the record completely or partially overlaps the from/to range you are searching on:

 

SELECT *
FROM table
WHERE salary_from <= $salary_to
AND salary_to >= $salary_from

[An7hony]

Thanks a lot!!!

 

Works perfectly

 

if(isset($_GET['salary_to'])) {
		 $salary = explode(":", $_GET['salary_to']);
	 }
$types[] = (isset($_GET['salary_to']) && strlen(trim($_GET['salary_to'])) > 0) ?
     "`salary_from` <= " . intval($salary[1])
                 . " AND `salary_to` >= " . intval($salary[0]) ." " : '';