Using PHP to construct SQL

lecsiy · #1 (**permalink**) 12-13-11, 17:03

Hey,

I'm sure this is an easy question.

If you want to produce SQL with php for a search query. So you have say 5 criteria which are all optional and may or may not be inputted by the user. You cannot guarantee any of them.

When it comes to making the SQL in php you can use :

So if they exist then you can use AND for the 4 last criteria.

But for the first criteria if you have that as a WHERE if that one is not selected then the SQL just is a list of AND's with no starting WHERE.

Am i being really stupid and is there an easy answer?

Thanks

Alex

healdem · #2 (**permalink**) 12-13-11, 17:18

there is a sneaky trick to set up your where clause using a quirk of SQL

PHP Code:


			
$WhereClause = " where 1=1";
if (abooleancondition)
{  $WhereClause .= " and anumericcolumn = ".$anumericvalue;
}
if (anotherbooleancondition)
{  $WhereClause .= " and astringcolumn = '".$astringcolumn."'";
}

by using where 1=1 means you alwasy have something in your where clause, but it doesn't affect row selection.
then add your if statements to build the fully formed (or not) where clause