Search filtering

[tet3828]

Here is a toughie.  I am making a database that keeps track of all training sessions our department provides for people within my company.

 

So I have some forms that allow the user to seach different fields in a table filled with entries of training sessions.

 

Training Session Table

first name

last name

trainee's position

clinic the trainee works in

software the trainee was trained on

the name of the person the trainee was trained by

 

so if I have all these forms displayed on one php page. how should I go about performing the search if two three or even all but one field is left blank... should I make a TON of if statements? or is there a more logical way of doing this with funcitons.... STUMPED. I can't think of how to code this. pleas help.

 

[GingerRobot]

Ok, well the first thing i suggest you do is to name all or your form elements with the same name as the field they refer to in the database. You can then use code similar to:

 

<?php
$fields = array('firstname','lastname','position');
$sql = 'SELECT * FROM tbl';
$where = '';
foreach($fields as $v){
if(isset($_POST[$v] && strlen($_POST[$v]) > 0){
	$where.=' '.mysql_real_escape_string($v).' = '.mysql_real_escape_string($_POST[$v]).' AND';
}
}
if($where != ''){//if there is a where clause
$where = substr($where,0,strlen($where)-4);//strip out last AND
$sql .= $where;
}
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
//echo out results
}
?>

 

You'll need to add to the array of fields and change the table name etc, but apart from that, it should all work.

[tet3828]

Great suggestion. I actually can almost dicipher what this code snippit is doing

 

but I am getting a parse error

 

 

Parse error: syntax error, unexpected T_BOOLEAN_AND, expecting ',' or ')' in /var/www/result.php on line 53

 

from this line of code

 

if(isset($_POST[$v] && strlen($_POST[$v]) > 0){

 

I tried adding and removing a few brackets and using a single & sign instead of a double... same error.

[GingerRobot]

<?php
$fields = array('firstname','lastname','position');
$sql = 'SELECT * FROM tbl';
$where = '';
foreach($fields as $v){
if(isset($_POST[$v]) && strlen($_POST[$v]) > 0){
	$where.=' '.mysql_real_escape_string($v).' = '.mysql_real_escape_string($_POST[$v]).' AND';
}
}
if($where != ''){//if there is a where clause
$where = substr($where,0,strlen($where)-4);//strip out last AND
$sql .= $where;
}
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
//echo out results
}
?>

 

I should really stop trying to help before i go to bed - i'd make so many less mistakes.