Database Search Form

[silvermoondragon]

I have a Form with the code <form action="search.php" method="post"> in a search.html page. When you click on the submit button, it runs search.php, querying the database and displaying the results.
This is the (I believe)relevant code I have in the search.php

Code: [Select]

$result= mysql_query("SELECT * FROM list WHERE
id = '$_POST[id]' AND gender = '$_POST[gender]' ORDER BY id");
Currently it searches fine, if you enter correct data in all fields. However if you leave a field blank you receive no results as it is essentially searching for entries that have no data for the field that was left blank. Is there a way to set it so that if nothing is entered into the form, or some parts of the form, then that condition is ignored?

For example, if you enter in the id 001 and male you get the one result, however if you just put male, you receive no results whereas I would like it to return with all the entries that have the gender listed as male.

Also, I have been having some difficulties with wild cards and where to put them. For example, I would like to be able to put 1 in the id form and get results where the id is 001 or 010 or 014, etc.

Thanks!

[Sergey Popov]

Here is how you can solve first problem (conditional criteria) and also second problem (wildcards):

Code: [Select]

$where = "";
if($_POST['id']!='') $where.=($where==''?'':' AND ')." id LIKE '%".$_POST['id']."%' ";
if($_POST['gender']!='') $where.=($where==''?'':' AND ')." gender = '".$_POST['gender']."' ";
if($where!='') $where = ' WHERE '.$where;
$result= mysql_query("SELECT * FROM list ".$where." ORDER BY id");

[silvermoondragon]

Oh thank you! That was exactly what I needed.

[silvermoondragon]

New question, but related to this.

How would I add in a count function that counts how many results are found from the query and displays that number before the results?

[Sergey Popov]

You can use this function if you need number of results:

Code: [Select]

$n = mysql_num_rows($result);

[silvermoondragon]

Thanks again for all your help!