Search Problems

[furious5]

I am currently trying to search a job table.

On the search page I have title, industry and location search fields. However at the moment I can only get results returned if I specify the location.

If I search for the specific job title, or industry or both I cannot get any results returned. I guess this is a problem with my SQL statement - but I cannot see anything wrong with it.

Any ideas? Here is the code:

<?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>";
}
?>

[kevinsequeira]

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

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

$sql = $sql.$sql_where

echo $sql;

PS: dont forget the . before =
ie $sql_where .=

also put the while loop in the else part of
if($num_rows==0){

}else{

// while loop here
}

so that it will loop only if there are results

reg
kevin

[furious5]

Kevin thanks for answering that.

Printed the SQL and amended the code to read:

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

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

$sql = $sql.$sql_where;

This seems to be working fine now - cheers

[furious5]

Erm still seem to be returning results even if I do not add any information in the text boxes:

Here is the full code at present, I have amended the code as you advised...

<?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>';
} else {

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 Results
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>";
}
}

?>

[kevinsequeira]

add this
$sql_where = "";

before

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

post the html code for the form that submits to this page.
did u try echo $sql each time to see what the query is ??

reg
kevin

[furious5]

Printing the SQL with no entries in the text boxes returns:

SELECT jobid, date, title, description, location, industry, salary FROM job WHERE title LIKE '%%'

HTML for search page:

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<form action="result.php" method="post"><br>
Title:<input name="title" type="text"><br>
Sector: <select name="sector">
<option></option>
<?php
//load database connection
include "config.php";

//SQL query
$sql = "SELECT industry FROM industry";
$sql_result = mysql_query($sql, $connection) or die ("Sorry the query could not be executed");
while ($row = mysql_fetch_array($sql_result)){
$industry = $row["industry"];

print "<option value=$industry>$industry</option>";

}
?>
</select><br>
Location:
<input name="location" type="text">
<br>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
</body>
</html>