Jump to content

searching a number range using BETWEEN


An7hony

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]) ." " : '';

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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]) ." " : '';

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.