Search for "blank" by "blank" (keyword with drop-down for category/column)

[nwells]

Hey everyone!

I'm having a really tough time figuring out what I would imagine is a pretty simple MySQL database search. I'd like to be able to search for a keyword and use a drop down to designate which column to look in. For example:

Search for keyword: ________________ (text field) by Name, Policy #, or Email Address (drop-down).

Here is the code that I'm currently using:

HTML Form

<table border="0" cellpadding="0" cellspacing="0">
<form action="search.php" method="post">		
<tr>
<td><input type="text" value="Search" name="term" onblur="if (this.value=='') { this.value='Search'; }" onfocus="if (this.value=='Search') { this.value=''; }" class="top-search-inp" /></td>
<td>
<select class="styledselect" name='searchtype'>
<option value="*"> All</option>
<option value="Recipient"> Name</option>
<option value="Code"> Policy #</option>
<option value="Email">Email</option>
</select> 
</td>
<td>
<input type="image" src="images/shared/top_search_btn.gif"  />
</td>
</tr>
</form>		
</table>

MySQL Query & Display Results

				
<?php
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());

$term = $_POST['term'];
$filter = $_POST['searchtype'];
					
$query = "SELECT * FROM wp_gcl_certificates where Recipient like '%$term%'"; 
					
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "<tr class=\"alternate-row\">";
echo "<td><input  type=\"checkbox\"/></td>";
echo "<td>{$row['recipient']}</td>";
echo "<td><a href=\"mailto:{$row['email']}\">{$row['email']}</a>";
echo "<td>\${$row['price']}</td>";
echo "<td>{$row['status']}</td>";
echo "<td><a href=\"/gift-certificates-lite/gcl_show.php?cid={$row['code']}\">{$row['code']}</a>";
echo "<td class=\"options-width\">";
echo "<a href=\"/gift-certificates-lite/gcl_show.php?cid={$row['code']}\" title=\"Edit\" class=\"icon-1 info-tooltip\"></a>";
echo "<a href=\"\" title=\"Edit\" class=\"icon-2 info-tooltip\"></a>";
echo "<a href=\"\" title=\"Edit\" class=\"icon-3 info-tooltip\"></a>";
echo "<a href=\"\" title=\"Edit\" class=\"icon-4 info-tooltip\"></a>";
echo "<a href=\"\" title=\"Edit\" class=\"icon-5 info-tooltip\"></a>";
echo "</td>";
echo "</tr>";
}
?>

This works great for searching the table 'wp_gcl_certificates' by 'Recipient' but I can't seem to figure out how to make the column I search by adjustable.

I tried this line:

$query = "SELECT * FROM wp_gcl_certificates where '%$filter%' like '%$term%'";

with no luck

I'm new to this forum, so I'm sorry if I posted in the wrong category. Any help would be GREATLY appreciated!
Thanks in advance.

[hastx]

If your MySql DB is using case sensitive table and column names, you need to make sure to pass the appropriate column name filter...so if your column name is 'email' you need to do either:

<option value="email">Email</option>

or:

$filter = strtolower($_POST['searchtype']);//of course this method wont work if some column names use caps and others dont 

Then:

$query = "SELECT * FROM wp_gcl_certificates where $filter like '%$term%'";