...

View Full Version : Dynamically generating where clauses



hinch
02-05-2010, 02:08 PM
Common task but I can't think of an easier way to do it than posted below.

Is there a way of simplifying the code below ?
At the moment I check the inputs create where clause segments then have a load of if's to jam them all together into a valid where clause and finally I dump them into the sql variable and return it for my query to use.

Mostly I'm after a way to cut down on the amount of if's there are there. Just can't seem to think of a way myself.


//generate sql used for list statement
function generatelistsql($filtersource,$filterstatus,$filterowner,$filtercolumn,$filterorder) {
$filtersource = mysql_real_escape_string($filtersource);
$filterstatus = mysql_real_escape_string($filterstatus);
$filterowner = mysql_real_escape_string($filterowner);
$filtercolumn = mysql_real_escape_string($filtercolumn);
$filterorder = mysql_real_escape_string($filterorder);
//create where clauses
if ($filtersource>0) {
$wc1 = "cSource=".$filtersource;
}
if ($filterstatus>0) {
$wc2 = "cStatus=".$filterstatus;
}
if ($filterowner>0) {
$wc3 = "CompanyOwner=".$filterowner;
}
if (isset($wc1)) {
$whereclause = $wc1;
if (isset($wc2)) {
$whereclause .= " AND ".$wc2;
}
if (isset($wc3)) {
$whereclause .= " AND ".$wc3;
}
} else {
if (isset($wc2)) {
$whereclause = $wc2;
if (isset($wc3)) {
$whereclause .= " AND ".$wc3;
}
} else {
if (isset($wc3)) {
$whereclause = $wc3;
}
}
}

if (isset($whereclause)) {
$finalwhere = "WHERE $whereclause";
}

// make statement
$listsql = "SELECT tblcompanies.*, tblleadstatus.LeadStatus, tblleadstatus.IsAccount, tblusers.FirstName, tblusers.Surname FROM `tblcompanies` INNER JOIN `tblleadstatus` ON tblleadstatus.ID=tblcompanies.cStatus INNER JOIN `tblusers` ON tblusers.UID=tblcompanies.CompanyOwner $finalwhere ORDER BY $filtercolumn $filterorder";
//return final created query
return $listsql;
}

SKDevelopment
02-05-2010, 04:22 PM
Maybe something like this (not checked):


<?php
//generate sql used for list statement
function generatelistsql($filtersource,$filterstatus,$filterowner,$filtercolumn,$filterorder) {
$filtersource = mysql_real_escape_string($filtersource);
$filterstatus = mysql_real_escape_string($filterstatus);
$filterowner = mysql_real_escape_string($filterowner);
$filtercolumn = mysql_real_escape_string($filtercolumn);
$filterorder = mysql_real_escape_string($filterorder);
//create where clause
$wc = array();
if ($filtersource>0) {
$wc[] = "cSource=".$filtersource;
}
if ($filterstatus>0) {
$wc[] = "cStatus=".$filterstatus;
}
if ($filterowner>0) {
$wc[] = "CompanyOwner=".$filterowner;
}
$finalwhere = implode(' AND ', $wc);

// make statement
$listsql = "SELECT tblcompanies.*, tblleadstatus.LeadStatus, tblleadstatus.IsAccount, tblusers.FirstName, tblusers.Surname FROM `tblcompanies` INNER JOIN `tblleadstatus` ON tblleadstatus.ID=tblcompanies.cStatus INNER JOIN `tblusers` ON tblusers.UID=tblcompanies.CompanyOwner $finalwhere ORDER BY $filtercolumn $filterorder";
//return final created query
return $listsql;
}

Small note: Are $filtersource, $filterstatus, $filterowner, $filtercolumn, $filterorder numbers ? If they are numbers then I think I would use intval() or floatval() to cast them to the corresponding numeric type explicitly to avoid SQL injections, not mysql_real_escape_string(). But this is my personal preference of course. mysql_real_escape_string() would provide protection too in this case.

Fumigator
02-05-2010, 04:29 PM
A little trick I use is I front-load the WHERE clause with this:



$where = "WHERE 1";


That will always evaluate to true, and now you can safely prepend each condition with "AND", and now you only need a single "if" statement for each condition you want to add.



$where = "WHERE 1";
if ($filtersource>0) {
$where .= " AND cSource=".$filtersource;
}
if ($filterstatus>0) {
$where .= " AND cStatus=".$filterstatus;
}
if ($filterowner>0) {
$where .= " AND CompanyOwner=".$filterowner;
}


Another way to make this more scalable (what if there are 10+ conditions) is to put the condition values in an array using the column name as the associative index, then build the WHERE clause using a foreach loop.

SKDevelopment
02-05-2010, 04:55 PM
Yes, approach of adding "WHERE 1" at the start of list of of AND conditions and "WHERE 0" at the start of block of OR conditions is very nice and should not affect performance in any way.

Though personally I prefer to use implode() as described only because I like to keep the query code clean of any redundant conditions (even if they do not affect anything). So if you do not mind having "WHERE 1" at the start of the query, use approach suggested by Fumigator. It is often recommended by SQL experts.

hinch
02-05-2010, 06:25 PM
some are strings some are ints.

for eaching an array sounds like an idea though didn't think about that.

this is quite a small example but some of the larger filtersets have 20+ options on them so that could be the way forward.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum