Multiple Textfields Search

[Alicia]

Hi guys,

 

Can some guru give me an idea how should I create a php query for my search form as below? I tried to search online for some tutorial for such searches from a form but found nothing relevant.. please help..

 

I have a form that allow user to search :

 

Class type :

Student name :

Student email :

Student gender :

 

I want to create a php mysql query that if the user only key in the name, it will only search the name column and if the user key in name and email, it will search both with AND in the query and if all are inserted, it will search all with 4 columns (AND) in the query.

 

The problem is i am not sure how to create such conditional statement so a proper query can be generated according to what textfields they user insert in the form..

 

please advise..

[akphidelt2007]

Back in the day when I did this manually I would store the info in an array and than implode it out at the end. For example

 

$where = Array();

if(!empty($_POST['student_name'])) $where[] = "student_name = '".cleanmefirst($_POST['student_name'])."'";
if(!empty($_POST['student_email'])) $where[] = "student_email = '".cleanmefirst($_POST['email'])."'";
if(!empty($_POST['student_gender'])) $where[] = "student_gender = '".cleanmefirst($_POST['gender'])."'";

$qry = "
SELECT blah,blah,blah
FROM yourtable
".((!empty($where) ? ' WHERE '.implode(' AND ',$where) : '');

 

Something along those lines. Otherwise you are going to get stuck with tons of if then and garbled messes.

Edited October 19, 2012 by akphidelt2007

[JohnTipperton]

you are trying to create an advance search right.

 

so first create an selectbox field with 4 options and a text box

the following name in the option value should be the exact name in the database field

<form method="post">
<select name="selecting" id="selecting">
<option value='Classtype' selected="selected">Class Type</option>
<option value='Studentname'>Student name</option>
<option value='Studentemail'>Student email</option>
<option value='Studentgender'>Student gender</option>
</select>
<input type="text" name="textsearch" value="">
<input type="submit" value="Search">
</form>

 

in your query it should be like this

 

$Searchfield=$_POST'textsearch'] // assuming this is the text search you input
$selection = $_POST['selecting']; // assuming this is the selected value in the option box in step 1
$SQL = "Select * from tblstudent Where ".$selection." LIKE '%".$Searchfield."%'"

Edited October 20, 2012 by JohnTipperton

[Pikachu2000]

Those option values need to be validated, and the text field string needs to be validated/sanitized/escaped before being used in a query string.

[JohnTipperton]

yes of course values need to be validated i just gave him an idea on how to do it.