Constructing a multi-argument MySQL Search Query

Question

Hi,

I'm attempting to construct a search query in MySQL using multiple arguments, and I can't quite get it right.

I'm receiving the search arguments from a form, and using them to dynamically build my query.

Here is some background info:

I have a MySQL database containing job information. There are four columns I want to search:

1. Job Title
2. Job Type
3. Job Description
4. Job Location

Job Title: This is a 1-5 word title of the Job post. It can be different based on the way it was input

Job Type: This is a three letter code for the job type.

Job Description: This is a short description of the Job and duties

Location: This is a city or town

The user will search using a search form. The search form consists of three parts. Two Text fields, "keywords", and "location"; and a dropdown box listing about 20 Job Types. This will allow them to choose between the three letter codes for the job listings.

I would like to take the information from these three sections of the form and use them to search my database and return the most accurate records. I've tried several iterations of my query and it's either returning a lot of unrelated records, or not returning enough of the related ones. I've also been unable to account for the user intentionally leaving portions of the form blank.

My current query looks something like this:

select * FROM my_jobs_table
WHERE category = 'User specified job type'
OR job_title LIKE 'User specified Job keyword'
OR job_description LIKE 'User specified Job keyword '
OR location LIKE "% User Specified Job Location%"

This just isn't working for me. Any Suggestions?

Answer 1

If you're using LIKE, you should place % on both ends of the string. % is a wildcard character:

SELECT * FROM my_jobs_table
WHERE category = 'User specified job type'
OR job_title LIKE '%keyword%'
OR job_description LIKE '%keyword%'
OR location LIKE '%location%'

You should really be using parameterized queries, though. (See here for some examples.) At the very least, make sure you're escaping the user-supplied portions of the query.

Answer 2

What I do in this situation is set up a basic query with no JOINs or WHEREs. Then I loop through the request values and build my query. Something like this:

$q="SELECT * FROM my_jobs_table";

foreach($post as $key=>$value) { //assuming you cleaned & validated the $_POST into $post
  switch($key)
     case 'category':
       $wheres[]="$key ='$value'";
       break;
     case 'job_title':
     case 'job_description':
     case 'location':
       $wheres[]="$key LIKE '%{$value}%'";
       break;
  }
}
$where='WHERE ' . implode(' AND ', $wheres); //put on a condition to check for empty($wheres)
$q .= $where;

Answer 3

The various "LIKE '%keyword%'" solutions don't have good performance. I suggest you use a FULLTEXT INDEX instead (provided your table uses the MyISAM storage engine).

CREATE FULLTEXT INDEX jobsearch_idx ON my_jobs_table (job_title, job_description, location);

Then you can search using the MATCH() operator:

SELECT * FROM my_jobs_table
WHERE MATCH(job_title, job_description, location) AGAINST( ? );

Substitute the user-specified keywords where I have "?" above. BTW, I'm assuming a reasonable design would be to allow location keywords to be found if they occur in the title or description. So in your application, concatenate the keywords with the location choice (space separated) and use that for the query parameter.

I deliberately left the category out of that example, because I assume you want category names to match exactly. So add it with an AND operator like this:

SELECT * FROM my_jobs_table
WHERE category = ?
  AND MATCH(job_title, job_description, location) AGAINST( ? );

Substitute the two user choices (category and keywords) for the two "?" placeholders above. Use query parameters, not variable interpolation.

There are of course a lot of other options for MySQL fulltext indexes. You might want to use the search modifier "IN BOOLEAN MODE" for example. I won't describe them fully here, you should go read the manual sections to learn more about it.

MySQL 5.1 Reference Manual: Full-Text Search Functions.

As for handling cases when users leave form fields empty, that's a different issue, separate from searching the database. You should solve that issue in Javascript or in application code, either setting a default value if the user doesn't give one, or else returning them to the form and telling them that they can't proceed without giving a value for required fields.