CODEWITHSUNDEEP
phpmysqlsqlstring
Aaron
Aaron

Reputation: 11693

SQL for job search feature issue

There is a text input for the string and select input with the location, When they search, it queries a database with this query. I am using PHP to construct the string

$sql = "SELECT * 
          FROM job_request 
         WHERE job_province = '$location' 
           AND job_title LIKE '%$string%' 
            OR job_description LIKE '%$string%' 
            OR job_skills LIKE '%$string%' 
            OR job_experience LIKE '%$string%'"

When I get the results it will show me jobs from other locations that don't match the location input string? WHY? It will show all the jobs with that string in it and I don't want that.

Upvotes: 0

Views: 157

Answers (5)

Nathan Ostgard
Nathan Ostgard

Reputation: 8406

You need to use parenthesis. Right now you are saying:

WHERE job_province = 'x' AND job_title LIKE '%y%' OR job_description LIKE '%y%' ...

The OR statements are causing the query to pass. If you wrap all the OR statements in parenthesis, it will mean both province and any one of the LIKE statements must match:

WHERE job_province = 'x' AND (job_title LIKE '%y%' OR job_description LIKE '%y%' ...)

Side note: your code is probably vulnerable to SQL injection. Make sure you are at least escaping your variables with mysql_real_escape_string(). Ideally, you should use PDO and prepared statements. Something like:

$statement = $dbh->prepare('
    SELECT *
    FROM job_request
    WHERE job_province = :location
        AND (job_title LIKE :search OR
             job_description LIKE :search OR
             job_skills LIKE :search OR
             job_experience LIKE :search)');
$statement->execute(array(
    ':location' => $location,
    ':search' => "%$search%"
));
$rows = $statement->fetchAll();

Upvotes: 1

kjl
kjl

Reputation: 912

Because you are searching from job_province = value OR job_description = string

From the brief description it sound like your OR statements need to be grouped:

$sql = "SELECT * 
          FROM job_request 
         WHERE job_province = '$location' 
           AND (job_title LIKE '%$string%' 
            OR  job_description LIKE '%$string%' 
            OR  job_skills LIKE '%$string%' 
            OR  job_experience LIKE '%$string%')"

Upvotes: 1

Gabe
Gabe

Reputation: 86768

When you have A AND B OR C OR D OR E, it gets interpreted as (A AND B) OR C OR D OR E. It appears as though you actually want A AND (B OR C OR D OR E).

Upvotes: 2

Jaydee
Jaydee

Reputation: 4158

You need to bracket the "OR"s

  $sql = "SELECT * 
     FROM job_request 
     WHERE job_province = '$location' 
       AND (job_title LIKE '%$string%' 
        OR job_description LIKE '%$string%' 
        OR job_skills LIKE '%$string%' 
        OR job_experience LIKE '%$string%')"

and read up about "SQL injection"

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332661

Use brackets for proper expression interpretation:

SELECT * 
  FROM job_request 
 WHERE job_province = '$location' 
   AND (   job_title LIKE '%$string%' 
        OR job_description LIKE '%$string%' 
        OR job_skills LIKE '%$string%' 
        OR job_experience LIKE '%$string%')

Frankly, you should be looking at using Full Text Search functionality for the job_* field searching.

Upvotes: 4

Related Questions