dynamic search problem

marke1415

i have 4 textboxes:
id number
lastname
firstname
middlename

if the user input on the id number, the select statement will be:
-select * from employee where idnumber = '123456'
if the user input on the id number and firstname, the select statement will be:
-select * from employee where idnumber = '123456' and firstname ='abc'
if the user input on the lastname and firstname, the select statement will be:
-select * from employee where firstname = 'abc' and firstname = 'xyz'

......and so on

so:
i want my select statement to be dynamic, depending on what the user inputs.

joe2

use

$query = "select * from table_name where ";

if ($first_condition != ''){
$query .= "first condition";
}

if ($second_condition != ''){
$query .= "second condition";
}

and so on

Hope it helps.

marke1415

-- what if he input only the lastname, i should eliminate the AND clause.
-- what if he input all the fields, i should include the AND in between them.
-- actually my problem is how to determine if more than 1 field was inputted. so i can put the AND clause

here is my code:

$mecno = @$_POST['mecno']; 
$lastname = @$_POST['lname']; 
$firstname = @$_POST['fname']; 
$midname = @$_POST['mname']; 
$TmpQry = '';
if ($mecno != ''){
   $TmpQry1 = ' mecno like ' ."'$mecno%'";
}
if ($lastname != ''){
   $TmpQry2 = ' patlname like ' ."'$lastname%'";
}
if ($firstname != ''){
   $TmpQry3 = ' patfname like ' ."'$firstname%'";
}
if ($midname != ''){
   $TmpQry4 = ' patmname like ' ."'$midname%'";
}

$TmpQry = $TmpQry1.' and '.$TmpQry2.' and '.$TmpQry3.' and '.$TmpQry4.';

$s = OCIParse($c, "select mecno,patlname,patfname,patmname
from mytable where '.$TmpQry' " );

joe2

First make a general select statement .

Check all the condition, if any of the condition is filled-in append where with the query.

then append query on the basis of user filled-in textboxes.

if ( $mecno != '' || $lastname != '' || $firstname != '' || $midname != '' ){
$TmpQry .= 'where ';
}

if ($mecno != ''){
$TmpQry .= 'and mecno like ' ."'$mecno%'";
}
if ($lastname != ''){
$TmpQry .= 'and patlname like ' ."'$lastname%'";
}
if ($firstname != ''){
$TmpQry .= 'and patfname like ' ."'$firstname%'";
}
if ($midname != ''){
$TmpQry .= 'and patmname like ' ."'$midname%'";
}

//$TmpQry = $TmpQry1.' and '.$TmpQry2.' and '.$TmpQry3.' and '.$TmpQry4.';

leatherback

Or place the sections in an array, and implodew by and:

eg:

if($condityion1)
  {
  $conditionlist[$i] = "mecno like ' ."'$mecno%'";
  $i++;
   }

if($condition2) 
  {
  $conditionlist[$i] = 'patlname like ' ."'$lastname%'";
  $i++;
  }

implode($conditionlist, " and ");