sql and php question

[pmcshane]

Hi

I'm new to sql and php so hopefully someone can help me out with this one.

I have 2 search fields js_location and js_keyword querying a table called jobs in a MySQL database.

What I want to happen is :

If a keyword and a location are both selected then select all relevant jobs based on the keyword and location.

If either of the fields is left blank then only select records from the database using the field that has been filled in.

The sql is I'm using at the moment is (it's simple and does not achieve what I am trying to do!) is:

SELECT *
FROM jobs

WHERE (JobLocation = 'js_location') and ((JobTitle LIKE '%js_keyword%') or (JobType LIKE '%js_keyword%') or (Description LIKE '%js_keyword%'))

I'd be grateful for any advice.

Thanks in advance.

[coopster]

Welcome to WebmasterWorld, pmcshane!

I'll assume the variables are coming from a form using

method="post"

. I would check the form variables first, then build my query based on the data supplied.

$js_location = (isset [php.net]($_POST['js_location'])) ? [php.net] $_POST['js_location'] : ''; 
$js_keyword = (isset [php.net]($_POST['js_keyword'])) ? [php.net] $_POST['js_keyword'] : ''; 
$sql = 'SELECT * FROM jobs'; 
$where_statement = ' WHERE '; // initialize 
if [php.net] ($js_location and $js_keyword) { 
 $where_statement .= [php.net] "JobLocation = '$js_location' ... "; 
} elseif [php.net] ($js_location) { 
 $where_statement .= [php.net] " ... "; 
} elseif [php.net] ($js_keyword) { 
 $where_statement .= [php.net] " ... "; 
} else [php.net] { 
 // both fields are blank 
} 
$sql .= [php.net] $where_statement; 

You didn't specify what should happen if both fields were left blank?

You may also want to have a look at MySQL Full-text Search [mysql.com].