Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    Dynamically generating where clauses

    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.

    PHP Code:
    //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;

    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Maybe something like this (not checked):
    PHP Code:
    <?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.

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    A little trick I use is I front-load the WHERE clause with this:

    PHP Code:
    $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.

    PHP Code:
    $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.

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    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.

  • #5
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •