PHP and PDO dynamic parameter binding

Question

I'm trying to fix this butchered bit of code - as you might have guessed, I'm cocking up the bind param syntax. In fact, I'm not even sure what I'm trying to do is even possible. Here's the class method...

/***
*
* @select values from table
*
* @access public
*
* @param string $table The name of the table
*
* @param array $fieldlist Fields to return in results, defaults null
*
* @param array $criteria Search criteria by keyed by fieldname
*
* @param int $limit Limit of records to return, defaults 10
*
* @return Array on success or throw PDOException on failure
*
*/
public function dbSearch($table, $fieldList = null, $criteria = null, $limit = 10)
{
    // setup $this->db to point to a PDO instance
    $this->conn();

    // build fieldlist
    if( is_null($fieldList) OR !is_array($fieldList) OR count($fieldList) == 0) {
        $returnFields = '*';
    } else {
        $returnFields = "'".implode("', '", $fieldList)."'";
    }

    // build criteria
    if( is_null($criteria) OR !is_array($criteria) OR count($criteria) == 0) {
        $whereClause = '';
    } else {
        $whereClause = array();
        foreach ($criteria as $key => $value){
           $bind_name    = 'bind_'.$key; //generate a name for bind1, bind2, bind3...
           $$bind_name   = $value; //create a variable with this name with value in it
           $bind_names[] = & $$bind_name; //put a link to this variable in array
           $whereClause[] = "'$key' = :$bind_name";
        }
        $whereClause = count($whereClause) > 0 ? ' WHERE '.implode( ' AND ' , $whereClause ) : '';
    }

    $sql = "SELECT $returnFields FROM '$table' $whereClause LIMIT $limit"; 

    $stmt = $this->db->prepare($sql);

    if( $whereClause != '') {
        call_user_func_array(array(&$stmt, 'bindParam'), $bind_names);
    }

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

... which at some point I want to call using something along these lines...

// look for users in database...
$user_recs = $crud->dbSearch('user', array('user_name'), array('user_name'=> $_POST['username']));
$users = $user_recs->fetchAll(PDO::FETCH_ASSOC);

How bonkers is this? Is it possible? Do I need to pass in the param types as well somehow? Any help gratefully received!

Could you elaborate what is the problem exactly? Of course you can bind dynamic parameters, you can use bindParam(), and of course you need to pass the datatype of the parameters, or at least guess it (but since you are passing the values, it's effortless to pass the types as well). — Eggplant
– Eggplant, Commented Aug 28, 2013 at 15:16
it is possible but personally I hate such query builders a lot. You are getting the same SQL in the end, but tortured and bound. — Your Common Sense
– Your Common Sense, Commented Aug 28, 2013 at 15:16
And as Your Common Sense wrote, such query builders usually make more harm than anything. Why don't you store all your queries in a separate and clean file (maybe a dedicated Class, also static if you like) where you can define queries, parameters and datatypes. Then in your call, it will be enough to pass an identifier for the desired query and an array containing the values for the parameters. Much cleaner, really. — Eggplant
– Eggplant, Commented Aug 28, 2013 at 15:23
Well, you're a cheerful bunch, I must say ;-) I've been asked by the client to ensure that all SQL queries use bound params, via PDO, that's why. Personally, yes, I would do it another way. So, could we leave aside the critiques. Ta. — Dycey
– Dycey, Commented Aug 28, 2013 at 16:22

Dycey · Accepted Answer · 2013-09-04 15:12:56Z

Actually, the problem was using bound parameters as opposed to bound values... doh!

Given an SQL statement and some values in an associative array, e.g.

$sql = "SELECT * FROM event
        WHERE eventdate >= :from 
        AND eventdate <= :until 
        AND ( user_name LIKE :st OR site_name LIKE :st )
        ORDER BY eventdate, start_time LIMIT 100";

$values = array( 'st'    => '%'.$searchterm.'%',
                 'from'  => $fromdate,
                 'until' => $untildate, );

then this class method ( but it could easily by a plain function) did the trick:

    public function dbBoundQuery($sql, $values, $types = false) {
        $this->conn();

        $stmt = $this->db->prepare($sql);

        foreach($values as $key => $value) {
            if($types) {
                $stmt->bindValue(":$key",$value,$types[$key]);
            } else {
                if(is_int($value))        { $param = PDO::PARAM_INT; }
                elseif(is_bool($value))   { $param = PDO::PARAM_BOOL; }
                elseif(is_null($value))   { $param = PDO::PARAM_NULL; }
                elseif(is_string($value)) { $param = PDO::PARAM_STR; }
                else { $param = FALSE;}

                if($param) $stmt->bindValue(":$key",$value,$param);
            }
        }

        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

Hope this helps someone else.

Your Common Sense · Accepted Answer · 2013-08-28 15:24:17Z

1

I really don't understand how this function is better than conventional

$stmt = $db->prepare("SELECT user_name FROM user WHERE user_name = ?");
$stmt->execute($_POST['username']);
$users = $stmt->fetchAll();

Mind you,

it keeps your query flexible. LIMIT ?,? is possible
it keeps your query readable. Almost natural English of SQL stays in place. You still can tell what does your query do, without the need of learning some brain-damaging language. As a side effect, any other developer can comprehend this code too.

answered Aug 28, 2013 at 15:24

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

2 Comments

Dycey Over a year ago

Again, not my choice. Client dictat.

Your Common Sense Over a year ago

What? What kind of client you have? Are you certainly sure he understands a single word in your program?

Vlad Preda · Accepted Answer · 2013-08-28 15:36:30Z

0

Well, there are quite a few things that can go wrong with the code.

For once, I don't see any AND / OR between WHERE clauses - which is probably why it doesn't work.

Secondly, it doesn't allow you to use SQL functions. Let's say you need to write a query like this:

SELECT * FROM `table` WHERE UNIX_TIMESTAMP(date_added) < ...;

You get the idea.

I would suggest to either use an existing ORM (Doctrine, Propel, etc), or stick to PDO.

Here is an example of how I would use PDO for a User class:

class User
{

 protected $data;
 public function __get($key) {
      return $this->data[$key];
 }
 public function __set($key, $value) {
     $this->data[$key] = $value;
 }
/**
 * @param $value
 * @param $field
 * @return $this
 */
public function loadBy($value, $field)
{
    $db = DbFactory::getInstance();
    $query = "SELECT * FROM users WHERE $field = :$field LIMIT 1";
    $stmt = $db->prepare($query);
    $stmt->execute(array(":$field" => $value));
    $result = $stmt->fetch();

    foreach ($result as $key => $value) {
        $this->$key = $value;
    }

    return $this;
}
}

You can create such functions for your entities, this you will have functions that are specialized, and efficient in what they do, and that are easy to test.

PS: Ignore the issue that appears when you have a field named data :)

edited Aug 28, 2013 at 15:36

answered Aug 28, 2013 at 15:29

Vlad Preda

9,9808 gold badges40 silver badges63 bronze badges

1 Comment

Dycey Over a year ago

Point taken re booleans - AND is what's needed and I'll amend. There are no fields named data. ;-) So I'll ignore it. Thanks.

Collectives™ on Stack Overflow

PHP and PDO dynamic parameter binding

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related