Hi experts,
I'm a relative php novice trying to display some data from a mySQL database using search criteria from a webform. So far I've managed to connect to the DB, perform a search using the criteria entered in the webform, and display the results in a table.
However, I have several search criteria and want to be able to ignore any null fields. For example, a user may want to search by type, or sponsor, or type and sponsor. So far I can get results when both criteria are present using the AND statement, but can't seem to be able to get it to display any results when only one of the criteria is complete. I tried using OR, which will ignore the null field, but not provide the correct results when both criteria are present.
Following a google search I've used some code I found which was supposed to do the trick, but doesn't work for me.
My HTML form is:
<form action="riom_search.php" method="post"><p>Please choose your search options:</p>
<table border="1" width="100%">
<tr>
<td>Type:</td>
<td><select name="Ftype">
<option Value=""></option>
<option Value="Collaboration">Collaboration</option>
<option Value="Research">Research</option>
<option Value="Fellowships">Fellowships</option>
<option Value="Knowledge Transfer">Knowledge Transfer</option>
<option Value="Training">Training</option>
</select> </td>
</tr>
<tr>
<td>Sponsor</td>
<td><select name="Fsponsor">
<option Value=""></option>
<option Value="EPSRC">EPSRC</option>
<option Value="MRC">MRC</option>
<option Value="NERC">NERC</option>
</select></td></tr>
</table>
and my php extract:
// collects the search term from search box
$termType = $_POST['Ftype'];
$termSponsor = $_POST['Fsponsor'];
// Retrieve all the data from the "calls" table
$result = mysql_query("SELECT * FROM calls
WHERE ('$termType' IS NULL OR Sponsor ='$termSponsor') AND ('$termSponsor' IS NULL OR Type='$termType')")
or die(mysql_error());
Any help would be appreciated as it's driving me crazy!
Thanks
Ignoring null form search results when querying mySQL
Moderator: General Moderators
-
davidjones53
- Forum Newbie
- Posts: 1
- Joined: Fri Aug 27, 2010 5:05 am
Re: Ignoring null form search results when querying mySQL
i might be wrong but i would use an if statement to decide which query to use.
Probably not what your looking for but i couldn't really understand the question
Code: Select all
if (isset($termType) && isset($termSponsor)) $result = mysql_query(
else if (!isset($termType) && isset($termSponsor)) $result = mysql_query(
else if (isset($termType) && !isset($termSponsor)) $result = mysql_query(