...

View Full Version : Drop down box search



PRodgers4284
03-07-2008, 03:51 PM
I am working on a search facilty that looks up a mysql database, I have two dropdown boxes "jobcategory" and "joblocation", i have some code for this but im wondering if im on right track and is this the best method of doing it?

My search code:


<?php
include("database.php");

$sql = "SELECT * FROM job";

if ($_POST["jobcategory"] && $_POST["jobcategory"]) {
$sql .= " WHERE jobcategory LIKE '&#37;{$_POST["jobcategory"]}%' AND joblocation '%{$_POST["joblocation"]}%'");
}
else if ($_POST["jobcategory"]) {
$sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'");
}
else if ($_POST["joblocation"]) {
$sql .= " WHERE joblocation like '%{$_POST["joblocation"]}%'");
}

$query = mysql_query($sql);

while ($job = mysql_fetch_array($query)){
$jobtitle=$job["jobtitle"];
$jobcategory=$job["jobcategory"];
?>

<table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
<tr>
<td width="131"><font face="Verdana" size="2">Job Title</font></td>
<td width="131"><font face="Verdana" size="2">Job Category</font></td>
<td width="131"><font face="Verdana" size="2">Job Description</font></td>
</tr>
<tr>
<td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
</tr>
</table>

<?php
}
?>

_Aerospace_Eng_
03-07-2008, 04:19 PM
You still aren't taking the proper security measures. Your code is succeptable to mysql injection. I posted a url a while back on one of your threads about writing secure php. I suggest you read it.

PRodgers4284
03-07-2008, 04:29 PM
You still aren't taking the proper security measures. Your code is succeptable to mysql injection. I posted a url a while back on one of your threads about writing secure php. I suggest you read it.

I know the code is not secure atm, im only testing it for now, just wanted to know if im on the right lines for developing the search facilty.

_Aerospace_Eng_
03-07-2008, 04:47 PM
Something like this might be better

<?php
include("database.php");

$sql = "SELECT * FROM job";
$jobcat = mysql_real_escape_string(trim($_POST['jobcategory']));
$jobloc = mysql_real_escape_string(trim($_POST['joblocation']));
if ($jobcat != '' && $jobloc != '') {
$sql .= " WHERE jobcategory LIKE '$jobcat&#37;' AND joblocation '%$jobloc%'";
}
else if ($jobcat != '' && $jobloc == '') {
$sql .= " WHERE jobcategory LIKE '%$jobcat%'";
}
else if ($jobloc != '' && $jobcat == '') {
$sql .= " WHERE joblocation LIKE '%$jobloc%'";
}

$query = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($query) > 0)
{
while ($job = mysql_fetch_array($query))
{
$jobtitle=$job["jobtitle"];
$jobcategory=$job["jobcategory"];
?>

<table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
<tr>
<td width="131"><font face="Verdana" size="2">Job Title</font></td>
<td width="131"><font face="Verdana" size="2">Job Category</font></td>
<td width="131"><font face="Verdana" size="2">Job Description</font></td>
</tr>
<tr>
<td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
</tr>
</table>

<?php
}
}
else
{
echo '<p>There are no search results with the search criteria you entered.</p>';
}
?>
Pay attention to what you actually code. This made no sense

if ($_POST["jobcategory"] && $_POST["jobcategory"]) {
Also your queries were incorrect. You should have been getting errors but AGAIN you have NO error checking so you don't know.

$sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'");
Don't need the ) on the end. I suggest you turn on php error reporting (search that).

PRodgers4284
03-07-2008, 05:12 PM
Something like this might be better

<?php
include("database.php");

$sql = "SELECT * FROM job";
$jobcat = mysql_real_escape_string(trim($_POST['jobcategory']));
$jobloc = mysql_real_escape_string(trim($_POST['joblocation']));
if ($jobcat != '' && $jobloc != '') {
$sql .= " WHERE jobcategory LIKE '$jobcat%' AND joblocation '%$jobloc%'";
}
else if ($jobcat != '' && $jobloc == '') {
$sql .= " WHERE jobcategory LIKE '%$jobcat%'";
}
else if ($jobloc != '' && $jobcat == '') {
$sql .= " WHERE joblocation LIKE '%$jobloc%'";
}

$query = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($query) > 0)
{
while ($job = mysql_fetch_array($query))
{
$jobtitle=$job["jobtitle"];
$jobcategory=$job["jobcategory"];
?>

<table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
<tr>
<td width="131"><font face="Verdana" size="2">Job Title</font></td>
<td width="131"><font face="Verdana" size="2">Job Category</font></td>
<td width="131"><font face="Verdana" size="2">Job Description</font></td>
</tr>
<tr>
<td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
</tr>
</table>

<?php
}
}
else
{
echo '<p>There are no search results with the search criteria you entered.</p>';
}
?>
Pay attention to what you actually code. This made no sense

if ($_POST["jobcategory"] && $_POST["jobcategory"]) {
Also your queries were incorrect. You should have been getting errors but AGAIN you have NO error checking so you don't know.

$sql .= " WHERE jobcategory LIKE '%{$_POST["jobcategory"]}%'");
Don't need the ) on the end. I suggest you turn on php error reporting (search that).

Hey thanks for the help, im getting an error stating:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%Co.Antrim%'' at line 1".

Is this to do with the version of mysql im using?

_Aerospace_Eng_
03-07-2008, 05:15 PM
if ($jobcat != '' && $jobloc != '') {
$sql .= " WHERE jobcategory LIKE '{$jobcat}&#37;' AND joblocation '%{$jobloc}%'";
}
else if ($jobcat != '' && $jobloc == '') {
$sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
}
else if ($jobloc != '' && $jobcat == '') {
$sql .= " WHERE joblocation LIKE '%{$jobloc}%'";
}

PRodgers4284
03-07-2008, 05:39 PM
if ($jobcat != '' && $jobloc != '') {
$sql .= " WHERE jobcategory LIKE '{$jobcat}&#37;' AND joblocation '%{$jobloc}%'";
}
else if ($jobcat != '' && $jobloc == '') {
$sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
}
else if ($jobloc != '' && $jobcat == '') {
$sql .= " WHERE joblocation LIKE '%{$jobloc}%'";
}


The script now works, if i go directly to the search.php script, it displays all the records in the database. Im using the following basic test form to carryout the search:


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Job Search</title>
</head>

<body>
<form method="post" action="search.php">
<select name="jobcategory">
<option value="Construction">Construction</option>
<option value="Banking and Insurance">Banking and Insurance</option>
<option value="Sales">Sales</option>
<option value="Other">Other</option>
</select>
<select name="joblocation">
<option value="Co.Antrim">Co.Antrim</option>
<option value="Co.Down">Co.Antrim</option>
</select><input type="submit" />
</form>

</body>

</html>

When i run the script from this page i get the same error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%Co.Antrim%'' at line 1"

_Aerospace_Eng_
03-07-2008, 05:41 PM
Post your current search.php page please. Reason it displays all of the records is because you have this as your first sql statement

$sql = "SELECT * FROM job";
and then you run the query.

PRodgers4284
03-07-2008, 05:45 PM
Post your current search.php page please.


<?php
include("database.php");

$sql = "SELECT * FROM job";
$jobcat = mysql_real_escape_string(trim($_POST['jobcategory']));
$jobloc = mysql_real_escape_string(trim($_POST['joblocation']));
if ($jobcat != '' && $jobloc != '') {
$sql .= " WHERE jobcategory LIKE '{$jobcat}&#37;' AND joblocation '%{$jobloc}%'";
}
else if ($jobcat != '' && $jobloc == '') {
$sql .= " WHERE jobcategory LIKE '%{$jobcat}%'";
}
else if ($jobloc != '' && $jobcat == '') {
$sql .= " WHERE joblocation LIKE '%{$jobloc}%'";
}

$query = mysql_query($sql) or die(mysql_error());

if(mysql_num_rows($query) > 0)
{
while ($job = mysql_fetch_array($query))
{
$jobtitle=$job["jobtitle"];
$jobcategory=$job["jobcategory"];
?>

<table border="1" width="51%" id="table1" bgcolor="#FFFFFF">
<tr>
<td width="131"><font face="Verdana" size="2">Job Title</font></td>
<td width="131"><font face="Verdana" size="2">Job Category</font></td>
<td width="131"><font face="Verdana" size="2">Job Description</font></td>
</tr>
<tr>
<td width="131"><font face="Verdana" size="2"><?php echo $job["jobtitle"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["jobcategory"]; ?></font></td>
<td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td>
</tr>
</table>

<?php
}
}
else
{
echo '<p>There are no search results with the search criteria you entered.</p>';
}
?>

_Aerospace_Eng_
03-07-2008, 05:46 PM
Check your first line. There should probably be a LIKE in there somewhere.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum