Drop down box search help

[PRodgers4284]

I am working on a search facility on a website that looks up a mysql database, i have managed to get it working with a dropdown box and it outputs the results in a form. I want to add another dropdown box to the search so that it looks up two search criteria. The search at present does a search on a field in the database table called "jobcategory", i want to add another drop down box to search by "joblocation" aswell as the "jobcategory". Can anyone help?

 

My code at present is:

 

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

$query = mysql_query("SELECT * FROM job WHERE jobcategory LIKE '%" . $_POST["jobcategory"] . "%'");

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

<form class="jobform" action="">  
<fieldset>  
<p class="edit"> 
</fieldset> 
<fieldset>  
<label for="jobtitle">Job Title:</label>  
<input readonly name="jobtitle" type="text" id="jobtitle" value="<?php echo $job["jobtitle"]; ?>" /><br />  
</fieldset>
<fieldset style="width: 602; height: 58">  
<label for="jobcategory">Job Category:</label>  
<input readonly name="jobcategory" type="text" id="jobcategory" value="<?php echo $job["jobcategory"]; ?>" />  
</fieldset> 
<fieldset>  
</fieldset> 
</form>  
<?php
  } 
?>

[fnairb]

So here is the million dollar question...

 

Which describes the rules for the submitted form?

a) require both jobcategory and location be selected

b) require jobcategory with location optional

c) require either jobcategory or location

d) both jobcategory and location are optional

[PRodgers4284]

I want the user to be able to select one or the other and also be able to carryout a search using both of the dropdown boxes, is this going to be difficult to do?

[PRodgers4284]

How can i add another query to search by joblocation?

[wrave]

I'm gonna take a stab at this one. If I understand your question, you want to have two selection boxes on your page. You want to select from either both or only one. So one is independent and one is dependent.

 

Initially you only need to display the independent selection. After that value is set, you can display the second value's selection box.

 

I think I'd build one form with potentially three form elements. Element one (a selection box) would display and allow the first selection and execute an action to re-call the page the form resides in ($SERVER('PHP_SELF')). It would pass the first selected value back to itself and then I would build a second element that does essentially the same thing but is dependent on the first value being chosen. It wouldn't even show up in the page until the independent value was set. Then I'd build a submission element to pass the variables to the query code.

 

If both selectors are independent, I'd set up a default value of NULL for them and show both and pass them both to a script that made decisions based on what was un/set.

 

Sorry if this isn't any help. Sometimes I think writing about code is like dancing about architecture.

[fnairb]

Cool!

 

 

There is more than one way to do this but I believe the easiest would be:

 

$sql = "SELECT * FROM job";

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

$query = mysql_query($sql);

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

 

By default (if no location or category are provided) all results will be returned.

 

 

NOTE:  I don not know if you are going for a like or = on the location but I figure you can work that out as you need it.

[PRodgers4284]

Cool!

 

 

There is more than one way to do this but I believe the easiest would be:

 

$sql = "SELECT * FROM job";

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

$query = mysql_query($sql);

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

 

By default (if no location or category are provided) all results will be returned.

 

 

NOTE:  I don not know if you are going for a like or = on the location but I figure you can work that out as you need it.

 

Hey thanks for that, appreciate ur help 

 

 

[PRodgers4284]

Im having a few problems with the code, im getting an error on line 19: "while ($sql = mysql_fetch_array($query)){"

 

The error is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\*****\search.php on line 19

 

Can anyone help?

 

 

My code is:

 

<?php 
include("database.php"); 
$sql = "SELECT * FROM job"; 
$jobcategory = mysql_real_escape_string(trim($_POST['jobcategory']));
$joblocation = mysql_real_escape_string(trim($_POST['joblocation']));

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

$query = mysql_query($sql);

while ($sql = 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 
  }  
?>

 

 

 

 

[PRodgers4284]

I have tried doing an error check on the query and i get the following error:

 

Invalid query: 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.Down%'' at line 1

 

My code is now:

 

<?php 
include("database.php"); 
$sql = "SELECT * FROM job"; 
$jobcatergory = mysql_real_escape_string(trim($_POST['jobcatergory']));
$joblocation = mysql_real_escape_string(trim($_POST['joblocation']));

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

$query = mysql_query($sql);
if (!$query) {
    die('Invalid query: ' . mysql_error());
}

while ($sql = @mysql_fetch_array($query)){ 
    $jobtitle=$job["jobtitle"];  
    $jobcatergory=$job["jobcatergory"];  
   ?> 
       
<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["jobcatergory"]; ?></font></td> 
        <td width="148"><font face="Verdana" size="2"><?php echo $job["description"]; ?></font></td> 
    </tr> 
</table> 
   
<?php 
  }  
?>

 

 

[fnairb]

It appears that the string within your query may be getting an extra set of single quotes.

 

Change your error statement to include printing out the $sql variable.

if (!$query) {
    die("Invalid query: \n$sql\n\n" . mysql_error());
}