Ignoring null form search results when querying mySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
davidjones53
Forum Newbie
Posts: 1
Joined: Fri Aug 27, 2010 5:05 am

Ignoring null form search results when querying mySQL

Post by davidjones53 »

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
ChrisBull
Forum Commoner
Posts: 42
Joined: Fri Aug 20, 2010 7:43 am

Re: Ignoring null form search results when querying mySQL

Post by ChrisBull »

i might be wrong but i would use an if statement to decide which query to use.

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( 

Probably not what your looking for but i couldn't really understand the question
Post Reply