Building a multi-parameter MySQL search query

Question

I am trying to build a search query in MySQL using a few arguments, and I cannot get it right.

I get the search arguments from the form and use them to dynamically create my query.

Here is some background information:

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

• Position
• Job Type
• Work description
• Work location

Position: . This is a heading 1-5 of the word Job. It may vary depending on the input method.

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

Job Description: This is a brief description of the job and responsibilities.

Location: This is a city or city

. . , " " ""; 20 . .

. , , . , .

:

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%"

. ?

Answer 1

"LIKE '%keyword%'" . FULLTEXT INDEX ( MyISAM).

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

MATCH():

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

, "?" . BTW, , , , . ( ) .

category , , . AND :

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

( ) "?" . , .

, MySQL. , "IN BOOLEAN MODE". , , .

MySQL 5.1: .

, , , . Javascript , , , , , .

Answer 2

LIKE, % . % - :

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%'

. (. .) , , .

Answer 3

In this situation, I create a basic query without JOIN or WHERE. Then I look at the query values ​​and build my query. Something like that:

$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;