Advice on Search Form

[Dalsor]

Hi all,

I'm looking for some advice on creating a search form. I have no code done yet except for the html search form and getting the post variables. The search form has 13 fields, and one or all of them could be used to narrow a search down. This is going to be used to search a database that is used to track calls for an ISP. The language is PHP and the database is MySQL. Without having a massively long if else && || script, can anyone offer advice on how to keep the number of lines of code to handle the form down?

Thanks!

Eric

[trib4lmaniac]

$result=mysql_query("SELECT * FROM table WHERE whatever LIKE '%$query%'");

[Dalsor]

Hi, thanks for the reply.

The problem there is 'whatever' could be 13 different things, or a combination of 13 different things.

If it helps, I've posted the form.

<?php
$db_connection = mysql_connect('host','db','password') or die (mysql_error());
$db_select = mysql_select_db('ucnsb_ontrak') or die (mysql_error());
?>
<form action="page.php?page=issue.search_issues.php" name="issue_search" method="post">
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#99CCFF" colspan="2" align="center">
<font style="font-size:16pt; color:#990000;"><b>Search Issues</b></font>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" width="150" align="right">Issue Status:</td>
<td bgcolor="#CCCCCC">
<select name="issue_status">
<option name="-1"></option>
<option name="0">Closed</option>
<option name="2">Open</option>
</select>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Submitted By:</td>
<td bgcolor="#CCCCCC">
<select name="issue_submitted_by">
<option name="0"></option>
<?php
$result = mysql_query("select real_name from users order by real_name") or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
echo "<option name=\"".$row['real_name']."\">".$row['real_name']."</option>";
}
mysql_free_result($result);
?>
</select>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">ISP:</td>
<td bgcolor="#CCCCCC">
<select name="isp_name" required>
<option name="0" value="0" selected>&nbsp;</option>
<?php
$result = mysql_query("select * from ISP order by isp_name") or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
?>
<option value="<?=$row['isp_name'];?>" name="<?=$row['isp_name'];?>"><?=$row['isp_name'];?></option>
<?php
}
mysql_free_result($result);
?>
</select>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Subject:</td>
<td bgcolor="#CCCCCC"><input type="text" name="subject" size="50"></td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Description:</td>
<td bgcolor="#CCCCCC"><input type="text" name="description" size="50"></td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Notes:</td>
<td bgcolor="#CCCCCC"><input type="text" name="notes" size="50"></td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Issue Type:</td>
<td bgcolor="#CCCCCC">
<select name="issue_type">
<option selected value="0" name="0">&nbsp;</option>
<?php
$result = mysql_query("select * from trouble_type order by long_name") or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
?>
<option name="<?=$row['lookup_name'];?>"><?=$row['long_name'];?></option>
<?php
}
mysql_free_result($result);
?>
</select>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Customer Name:</td>
<td bgcolor="#CCCCCC"><input type="text" name="full_name" size="50"></td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Customer Email:</td>
<td bgcolor="#CCCCCC"><input type="text" name="email" size="50"></td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Customer Phone:</td>
<td bgcolor="#CCCCCC">
<input type="text" name="npa" size="3" maxlength="3"> -
<input type="text" name="nxx" size="3" maxlength="3"> -
<input type="text" name="line" size="4" maxlength="4">
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" align="right">Escalation Status:</td>
<td bgcolor="#CCCCCC">
<select name="assignment">
<option name="0"></option>
<option name="unassigned">Unassigned</option>
<option name="escalated">Escalated</option>
</select>
</td>
</tr>
<tr>
<td bgcolor="#99CCFF" colspan="2" align="center"><input type="submit" name="search" value="Search"></td>
</tr>
</table>
</form>
<?php
mysql_close($db_connection);
?>

[sad69]

This is currently what I'm using for my search script to generate my where clause:

$query = 'select * from table1 a, table2 b where 1=1 ';

$search_items = array();
$search_items[] = 'u.user_fname';
$search_items[] = 's.l_name';
$search_items[] = 'p.street_number';
$search_items[] = 'ap.home_phone';
$search_items[] = 'a.application_id';
$search_items[] = 'b.name';

if(isset($_POST['search_criteria']) && !empty($_POST['search_criteria'])) {
$sc = explode(' ', $_POST['search_criteria']);
foreach($sc as $i=>$word) {
$query .= 'and (';
foreach($search_items as $i=>$keyword) {
$query .= $keyword.' like \'%'.$word.'%\' or ';
}
$query = substr($query, 0, strlen($query)-4); //get rid of last ' or ' (4 chars..)
$query .= ') ';
}
}

//add group by, having, order by here if you want


My search doesn't take AND and OR into account, just the key words.. and I've only got the one search field, not 13.

Another possibility could be to name all your search fields in the html form the same name as an array (like search_criteria[]) so that you can construct a loop to create your query.

Using a loop is the only way I can think of that will shorten the code, otherwise you basically need to have the AND and OR. Unless someone else can offer some other insight.

Hope that's of some help,
Sadiq.

[Dalsor]

I had not considered the array approach. That does seem to be the way to approach this. Thanks, Sadiq.