checking if WHERE is already in query

[stijn0713]

I don't know precisely how to put it so i will give an example:

 

suppose you have a search form with input fields for:

 

-an age range,

-a data range,

-for the sex,

-postal code

 

as soon as the user would fill in one of these input fields, a query should be generated including a condition doing something with the inputted value.

 

For example, the user fills in maxAge, the the query should say: SELECT * FROM table WHERE age >= maxAge;

 

Filling more than one criterion then would do .= " AND criteria (condition) inputvalue " etc...

 

My question comes to the part about: how to check if there is already a WHERE in the clause and how to put WHERE or AND for every criterion.

 

Since doing like above, i won't be able to search for another criterion if i don't select maxAge first because then there would be no WHERE in the sql clause.

 

Hopefully i've put it clear

thanks in advance

[kicken]

Put all your conditions into an array then implode it with whatever operator you need (AND or OR)

 

$where = array();
if ($blah){  $where[] = ' field >= whatever '; }
if ($bleh){ $where[] = ' this=that';}
if ($blargh){ $where[] = ' thisway!=thatway '; }

$sql = 'select ...';
if (count($where) > 0){
   $sql .= 'WHERE '.implode(' AND ', $where);
}

 

[scootstah]

Damnit, I just typed 20 lines and you beat me to it kicken.

[Psycho]

Damnit, I just typed 20 lines and you beat me to it kicken.

 

What's worse is he was able to answer the question in only 9 lines of code (one of which is blank), whereas you needed 20.

 

I jest of course - no ill intent meant.

[stijn0713]

Ok thanks for the replies!

 

[scootstah]

Damnit, I just typed 20 lines and you beat me to it kicken.

 

What's worse is he was able to answer the question in only 9 lines of code (one of which is blank), whereas you needed 20.

 

I jest of course - no ill intent meant.

 

But his was more or less pseudo code, and he collapsed the conditionals.

[stijn0713]

Put all your conditions into an array then implode it with whatever operator you need (AND or OR)

 

$where = array();
if ($blah){  $where[] = ' field >= whatever '; }
if ($bleh){ $where[] = ' this=that';}
if ($blargh){ $where[] = ' thisway!=thatway '; }

$sql = 'select ...';
if (count($where) > 0){
   $sql .= 'WHERE '.implode(' AND ', $where);
}

 

 

What if i sometimes need a OR operator and sometimes a AND or whatever other SQL syntax?

[scootstah]

May not be the best solution, but this is what I came up with off the top of my head and before my coffee.

$where = '';

$age        = $_POST['age'];
$date       = $_POST['date'];
$sex        = $_POST['sex'];
$postalcode = $_POST['postalcode'];

if ($age != '') {
$where .= " AND age='$age'";
}

if ($date != '') {
$where .= " OR date='$date'";
}

if ($sex != '') {
$where .= " AND sex='$sex'";
}

if ($postalcode != '') {
$where .= " AND postalcode='$postalcode'";
}

$where = preg_replace('/^AND|OR/i', '', trim($where));