function to query DB with multiple filters

I'm trying to write a single function that queries a database, with results dependent on what is passed. So far I have the following, but I can't help thinking there's a more succinct way to achieve this.

Additionally if I use a value of 0 all results get returned - like the 0 is a value of false, even if I pass it as a string.

I notice within WordPress strings are passed into functions (e.g. cat=1&posts_per_page=5). I couldn't quite figure out how this worked, but it looks like a nicer way of doing things.

Any help appreciated.

public static function sub_packages($subname_id = false, $region_id = false, $recurrence = false) {
    global $wpdb, tbl_packages;  

    if ( $subname_id ) $subname_id = " `package_subname` = $subname_id";
    if ( $region_id ) $region_id = " `package_region` = $region_id";
    if ( $recurrence ) $recurrence = " `package_recurrence` = $recurrence";

    // one
    if ( $subname_id && !$region_id && !$recurrence )
        $filter = " WHERE $subname_id";
    elseif ( !$subname_id && $region_id && !$recurrence )
   开发者_C百科     $filter = " WHERE $region_id";
    elseif ( !$subname_id && !$region_id && $recurrence )
        $filter = " WHERE $recurrence";
    // two
    elseif ( $subname_id && $region_id && !$recurrence )
        $filter =  " WHERE $subname_id AND $region_id";
    elseif ( $subname_id && !$region_id && $recurrence )
        $filter =  " WHERE $subname_id AND $recurrence";
    elseif ( !$subname_id && $region_id && $recurrence )
        $filter =  " WHERE $region_id AND $recurrence";
    // three
    elseif ( $subname_id && $region_id && $recurrence )
        $filter =  " WHERE $subname_id AND $region_id AND $recurrence";

    return $wpdb->get_results( "SELECT * FROM ". tbl_packages . $filter, ARRAY_A);

}

Check out how DataTables does this:

$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ ) 
{
    $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';

Of course there is.

• What do you want sql query to return? (single record, multi-record (array like), all data)
• Which cells you want to select?
• What is the table name you want to do your query on?
• Where are the DB Table cell names & values to do query?
• How do you order returned results?
• How do you limit (paginate) results?

These are the first questions you have to ask yourself when you try to create universal query functions / methods.

Here is a simple example;

function getData ($tableName= "members", $type = "array", $select = "*", $data = NULL, $order = array("id"=>"DESC"), $pagination = array("100"=>NULL)) { }

Now code above has some features;

$tableName

Obvious (:

$type

Array = Helps you to do query with an array and returns results as an array. $data must be an array.

id = Helps you to do a query with specific ID. $data must be integer.

all = Returns all data. You just need to use $type = all and $select = "*" (or any cell you want to select)

single = When you don't know ID but you need single result. $data must be an array.

$select

If you have big database and if you use all the time * to select all cells which you don't even use, this will slow your pages down. If you want to select only the cells you need, you can use this.

$data

This can be array or integer depending on $type. You can use it this way;

array("username" => "admin");

Here you can use some extras with regex; array("!username" => "LIKE!admin");

so when you are looping $data to put it in your query, if you see in the beginning of array key !, this might mean, don't use AND, use OR.

If you see in the beginning of array value LIKE! use it this way;

WHERE username LIKE 'admin'

$order

This is also obvious.

$pagination

array(100 => 0) Array key is limit, array value is offset.

Believe me you want to do something like this in order to make your life easy.

Of course I will suggest you to use PHP 5.3's feature static and make a databaseQuery class. This way you don't need to type $tableName in the first place and easier to work with OOP.

Simply your imagination is limit (:

I hope this helps.

After the examples above I needed something a little more specific to the queries I wanted to perform.

http://professionaldilettante.com/how-to-pass-parameters-as-query-string-to-your-php-functions/2010.11.22 runs though parsing arguments as a string, and as such I came up with this

private function parse_args($args) {

    // error check
    if( !is_string($args) )
        throw new Exception("\n\nfunction parse_args() expects a string, ". gettype($args). " passed\n\n");

    if( strpos($args,"&")  ):
    // if str contains &, assume multiple params
        $parts = explode("&",$args);
        for( $i=0, $len=count($parts); $i<$len; $i++ ):
            $tmp = explode("=",$parts[$i]);
            $parsed[$tmp[0]] = $tmp[1];
        endfor;
    else:
        $tmp = explode("=",$args);
        $parsed[$tmp[0]] = $tmp[1];
    endif;

    return $parsed;
}

 public static function sub_packages($args) {
    global $wpdb, $tbl_packages;

    $args = Packages::parse_args($args);

    // build query.
    $sql = "SELECT * FROM $tbl_packages";

    if ( count($args) > 0 ) {
        $i = 0;
        foreach ( $args as $arg => $value) {
            if ( $i == 0 ) $sql .= " WHERE";
            elseif ( $i != $count ) $sql .= " AND";
            $sql .= " `$arg` = $value";
            $i++;
        }

    }

    return $wpdb->get_results( $sql, ARRAY_A);
 }