Seraching Problems

[xeidor]

I'm trying to make a search that has multiple fields. For example I want the user to enter the Date and the Person they are searching for and have it return only the results that match that Date and that person.

 

The problem is that there are 5 fields and I want them to be able to search by just 1 field or 2 or 3 etc. and each search field limit the results to match all of the entries. However, what happens is when they only enter one field, and leave the others blank; they don’t get any results because the other fields are left blank. (Or at least I think that what’s happening).

 

 

 

Anyone know a way around this?

 

Much appreciated,

Thanks

 

[per1os]

Without any code its like the blind leading the blind, maybe this will help:

 

<?php
$searchArr = array("search1", "search2", "search3"); // replace this with the field names
foreach ($_POST as $key => $val) {
        if (in_array($key, $searchArr)) {
               $whereClause .= $key . "='".mysql_real_escape_string($val)."' AND ';
        }
}

$whereClause = substr($whereClause,0,-5);
$sql = "SELECT * FROM TABLE WHERE " . $whereClause;

?>

 

--FrosT

[pocobueno1388]

Here is the basis of my search scripts...you can change it up to match whatever you are doing.

 

<?php
include "header.php";

if($_GET['action'] == "search"){

$breed=$_POST['breed'];
$name=$_POST['name'];
$age=$_POST['age'];
$gender=$_POST['gender'];
$forsale=$_POST['forsale'];
$color = $_POST['color'];
$min = $_POST['min'];
$max = $_POST['max'];
$level=$_POST['level'];

$query = "SELECT id, name, breed, age, sex, sale_price, level FROM dragons WHERE 1";

if($breed) $query .= " AND breed='$breed'";
if($name) $query .= " AND name='$name'";
if($age) $query .= " AND age='$age'";
if($gender) $query .= " AND sex='$gender'";
if($min) $query .= " AND sale_price >= '$min'";
if($max) $query .= " AND sale_price <= '$max'";
if($color) $query .= " AND color='$color'";
if($forsale == 'Yes') $query .= " AND sale_price > '0'";
if($forsale == 'No') $query .= " AND sale_price <= '0'";
if($level) $query .= " AND level='$level'";

$result = mysql_query($query);
$num = mysql_num_rows($result);

if($num == "0"){
print "No dragons meet that search query.";
include "footer.php";
exit;
}

print "
<p><center><table width=600 class=tstyle6><tr>
<td class=tstyle2><center> S E A R C H  </td></tr><tr>
<td><center>
<table id=menue class=tstyle5 width=\"100%\"><tr>
<td><center><font color=2A305A>$num results found<p></font></table id=topic></td></tr><tr><td><center><br>
<table class=tstyle1 width=500><tr>

<td class=tstyle3>Name</td>
<td class=tstyle3>Breed</td>
<td class=tstyle3>Age</td>
<td class=tstyle3>Gender</td>
<td class=tstyle3>For Sale?</td>
<td class=tstyle3>Level</td><tr>";

while ($row = mysql_fetch_assoc($result)){

if ($row['sale_price'] > 0){
$sale = '$'.$row['sale_price'];
} else {
$sale = 'no';
}

print "<tr>
<td class=tstyle5><a href='dragon.php?dragonid=$row[id]'>$row[name]</a></td>
<td class=tstyle5>$row[breed]</td>
<td class=tstyle5>$row[age]</td>
<td class=tstyle5>$row[sex]</td>
<td class=tstyle5>$sale</td>
<td class=tstyle5>$row[level]</td>
</tr>";

}

print "</tr></table></table>";
include "footer.php";
exit;
}
?>


<form method="post" action="searchdragons.php?action=search"><br>

Name: <input type=text name="name" maxlength=75><p>
Age: <input type=text name="age" maxlength=2><p>

Color: <input type="text" name="color">
<p>

For sale?
<select name="forsale">
<option value="">All</option>
<option>No</option>
<option>Yes</option>
</option></select><p>

Min Price: $<input type=text name="min" size=9><br>
Max Price: $<input type=text name="max" size=9><p>

Gender: 
<select name="gender">
<option></option>
<option>Male</option>
<option>Female</option></select><p>

Breed: <select name="breed">
<option></option>
<option>Fire</option>
<option>Water</option>
<option>Ice</option>
<option>Earth</option>
<option>Air</option>
<option>Poison</option>
<option>Light</option>
</select><br>
Level: <input type=text name=level>
<p>

<input type=submit name=submit value='Search Dragons'></form>



<?php
include "footer.php";
exit;
?>

[xeidor]

Thanks for the replies and look into it.

[per1os]

Edit to mine:

 

<?php
$searchArr = array("search1", "search2", "search3"); // replace this with the field names
foreach ($_POST as $key => $val) {
        if (in_array($key, $searchArr)) {
               $whereClause .= $key . "='".mysql_real_escape_string($val)."' AND ";
        }
}

$whereClause = substr($whereClause,0,-5);
$sql = "SELECT * FROM TABLE WHERE " . $whereClause;

?>

 

Damn quotes.

 

--FrosT