[SOLVED] Search form with multiple fields

[Tuk]

Hi all. This is driving me totally insane, especially since I can't find the answer anywhere online, though it seems like it should be so simple. Basically I have a form that will search the database, and it has a number of fields. I know how to display a queried field, but I want to make it so that if the user didn't fill in one or more fields in the form, the variables associated with those fields are omitted from the query, because as it is right now, when I hit the Search button, the results page comes up, but with no results listed, and no errors... so I'm assuming it's because it is looking in the database for blank spaces due to the fields not being filled in.

 

I have tried pretty much everything I know how to do... I tried unsetting the variables if they are blank, but it had no effect at all.

 

Here is the code for my search table...

<table style="border: 1px solid #a8c562;" width=100%>
			<tr>
			<form action=/searchresults.php method=post>
				<td style="border: 1px solid #a8c562;" width=30% bgcolor=#cadd99 align=right>Name :</td>
				<td style="border: 1px solid #a8c562;" width=70%><input type=text name=susername size=30></td>
			</tr><tr>
				<td style="border: 1px solid #a8c562;" width=30% bgcolor=#cadd99 align=right>ID # :</td>
				<td style="border: 1px solid #a8c562;" width=70%><input type=text name=suserid size=30></td>
			</tr><tr>
				<td style="border: 1px solid #a8c562;" width=30% bgcolor=#cadd99 align=right>Ranch :</td>
				<td style="border: 1px solid #a8c562;" width=70%><input type=text name=suserranch size=30></td>
			</tr><tr>
				<td style="border: 1px solid #a8c562;" width=30% bgcolor=#cadd99 align=right>Tag :</td>
				<td style="border: 1px solid #a8c562;" width=70%><input type=text name=susertag size=30></td>
			</tr><tr>
				<td style="border: 1px solid #a8c562;" width=30% bgcolor=#cadd99 align=right>Specialty :</td>
				<td style="border: 1px solid #a8c562;" width=70% align=center>
				<select name=suserspec>
				<option value=""></option>
				';
				include("/var/www/speciesdropdown.php");
				echo'
				</td>
			</tr><tr>
			<td style="border: 1px solid #a8c562;" colspan=2 align=center><input type=submit name=submitusersearch value="Search!"></td>
			</tr></form>
		</table>

 

 

And on the searchresults.php page, this is the section that is visible if the "submitusersearch" button is pressed...

if(isset($_POST['submitusersearch'])){
echo'
<table width=100% style="border: 1px solid #a8c562;" cellpadding=3><tr>
<td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">User</td>
<td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">ID#</td>
<td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">Ranch</td>
<td bgcolor=#cadd99 align=center style="border: 1px solid #a8c562;">Specialties</td></tr>
';

$usearchname = $_POST['susername'];
$usearchid = $_POST[suserid];
$usearchranch = $_POST['suserranch'];
$usearchtag = $_POST['susertag'];
$usearchspec = $_POST['suserspec'];


$userarray = mysql_query("SELECT * FROM players WHERE displayname = '$usearchname' AND id = '$usearchid' AND ranchname = '$usearchranch' AND ranchtag = '$usearchtag' AND speciality1 = '$usearchspec' OR specialty2 = '$usearchspec' OR speciality3 = '$usearchspec'");
while ($usrow = mysql_fetch_array($userarray))
{
echo'
<tr>
<td style="border: 1px solid #a8c562;" align=center>'.$usrow['displayname'].'</td>
<td style="border: 1px solid #a8c562;" width=10% align=center>'.$usrow['id'].'</td>
<td style="border: 1px solid #a8c562;" align=center>'.$usrow['ranchname'].'</td>
<td style="border: 1px solid #a8c562;" align=center>'.$usrow['speciality1'].'<br>'.$usrow['specialty2'].'<br>'.$usrow['speciality3'].'</td>
</tr>
';
}

echo'</table>';
}

 

 

Thank you for taking a look...

[mmarif4u]

May be this would help:

 

$userarray = mysql_query("SELECT * FROM players WHERE displayname = '$usearchname' OR id = '$usearchid' OR ranchname = '$usearchranch' OR ranchtag = '$usearchtag' OR speciality1 = '$usearchspec' OR specialty2 = '$usearchspec' OR speciality3 = '$usearchspec'");

[Tuk]

Thanks for your reply, but I need it to be able to search specifically for what fields they enter. For example if they type something in the "userranch" field and also select something from the "userspec" dropdown menu, I need the results to be all cases in the database where both of those parameters exist within the same row.

[avvllvva]

Hi,

Try this code,

 

Note : you have to add one more column to the table like a STATUS of type enum and make it default

to Y for the records whichever you wnat to display. This step is only for checking an initial serach condition. So that you can keep same value for all the records in the table. If u already have common

column like this try to use that column name in the initilal serch clause just after the where condition.

<?php
$qrystring = "SELECT * FROM players where status='Y' ";  //  Just create a filed -'status' in the table with datatype enum and make it default 'Y'  for all records

if($usearchname)
$qrystring .= " AND displayname = '$usearchname'  ";

if($usearchid)
$qrystring .= " AND id = '$usearchid'  ";

if($usearchranch)
$qrystring .= " AND ranchname = '$usearchranch'  ";

if($usearchtag)
$qrystring .= " AND ranchtag = '$usearchtag'  ";

if($usearchspec)
$qrystring .= " AND speciality1 = '$usearchspec'  ";

if($usearchspec)
$qrystring .= " AND specialty2 = '$usearchspec'  ";		

if($usearchspec)
$qrystring .= " AND speciality3 = '$usearchspec'  ";						

$userarray = mysql_query($qrystring);
?>