Validating variables & specifying SQL

[furious5]

I was wondering if someone can help me.

Basically I have created a search page with three text boxes - title, location, skill which will then query a table and return the results.

At the moment the sql query is looking solely for the title so the SQL looks something like this:

"SELECT title, location, skill FROM job WHERE title like '%$title%'"

Basically I want to expand the SQL to include location and skill, so I guess the SQL would look like this:

"SELECT title, location, skill FROM job WHERE title LIKE '%$title%' and WHERE location = '$location' and WHERE skill = '$skill'"

My problem is how do I check to see if the user has entered values in the relevant text boxes or assign these null values if they are empty.

[kevinsequeira]

$query = SELECT title, location, skill FROM job WHERE title LIKE '%$title%' ";
if($location!="")
$query_where = " AND location = '$location'";

if($skill!="")
$query_where = " AND skill = '$skill'";

$query = $query.$query_where;

reg
kevin

[furious5]

Wow thanks for such a quick response.

I'll try the code when I get home this evening. I had not thought of doing it that way but it seems logical now you have pointed it out.

I was thinking of using a series of if, elseif statements but looks far more compact.

Cheers

Mike

[furious5]

Tried the code that was suggested.

However if I search by title or sector or a combination of title and sector or title, sector and location - 0 records are returned.

If I search for location the search returns results.

I have tried searching for the exact job title in the database and still no joy.

Any ideas why the code is failing to search for title or sector?

Heres the code I'm using:

<?php
//Get variables from search.php
$title = $HTTP_POST_VARS['title'];
$industry = $HTTP_POST_VARS['sector'];
$location = $HTTP_POST_VARS['location'];

//Load Database Connection & DB details
include("config.php");

//SQL query
$sql = "SELECT jobid, date, title, description, location, industry, salary FROM job WHERE title LIKE '%$title%' ";

if($industry != "")
$sql_where = " AND industry = '$industry'";

if($location != "")
$sql_where = " AND location = '$location'";

$sql = $sql.$sql_where;

//Execute SQL and exit if failure
$sql_result = mysql_query($sql) or die('SQL Error');
$num_rows = mysql_num_rows($sql_result);
if ($num_rows == 0)
{
echo 'Sorry your search did not return any results. Please return to the search page <a href=search.php>here</a>';
}

while ($row = mysql_fetch_array($sql_result)){
$jobid = $row["jobid"];
$date= $row["date"];
$title = $row["title"];
$desc = $row["description"];
$location = $row["location"];
$skills = $row["skills"];
$salary = $row["salary"];
$industry = $row["industry"];
$contact = $row["contactid"];

print "<table><tr><td>Job ID: $jobid</td></tr>";
print "<tr><td>Date: $date</td></tr>";
print "<tr><td>Description: $desc</td></tr>";
print "<tr><td>Location: $location</td></tr>";
print "<tr><td>Salary: $salary</td></tr></table>";
print "For more information, please click <a href=result_details.php?jobid=$jobid>here</a>";
}
?>