Help with PDO prepared statement function

[risoknop]

I have written the following function for querying object arrays from the database with prepared statement (PDO):

function preparedSelect($table, $data = NULL)
{
    try
    {
        if (isset($data))
        {
            foreach ($data as $key => $val)
            {
                if (!isset($cond))
                    $cond = 'WHERE '.$key.' = :'.$key;
                else
                    $cond .= ' AND '.$key.' = :'.$key;
            }
            $stmt = $this->dbh->prepare("SELECT * FROM $table $cond");
            foreach ($data as $key => $val)
            {
                if (is_int($val))
                    $stmt->bindParam(':'.$key, $val, PDO::PARAM_INT);
                else
                    $stmt->bindParam(':'.$key, $val, PDO::PARAM_STR, strlen($val));
            }
            $stmt->execute($data);
            while ($obj = $stmt->fetch())
                $result_array[] = $obj;
            return $result_array;
        }
        else
        {
            $data = array();
            $stmt = $this->dbh->prepare("SELECT * FROM $table");
            $stmt->execute($data);
            return $stmt->fetchAll();
        }
    }
    catch (PDOException $e)
    {
        printf("<p>Error: %s</p>\n", $e->getMessage());    
    }
} 

Which in practical use looks like this for instance:

$results = preparedSelect('posts', array('id' => 1, 'title' => 'Some title')); 

It works but I am, at the moment, struggling with figuring out how could I use the function several times in a row (for example 5 queries) without repreparing the statement, just with changing the variables that are binded to query.

I'm not sure if this is possible with the function, maybe I should make a class for this?

[risoknop]

Edited the function a bit...

[Jake Arkinstall]

Yep, you'd want an object for this.

[risoknop]

So, basically, the class should contain one method for preparing the statement and one method for executing it? Then I would just prepare the statement once and I could execute it more times in a row..

[Jake Arkinstall]

Well, the construct method takes the statement as the parameter.

It then stores the outcome of the prepare() function as an object property, and creates an object property holding an array of each placemarker and its current value. Bind these values to the PDOStatement object generated by prepare().

You can then execute the query numerous times by setting these array values in another function. If you use regex to find the placemarkers, it will store them in array-order, so you'd only need to place the values individually in the function parameters, then use func_get_args() in the function which runs the prepared query and set the array values by integer key rather than string key.

Hope that makes sense

[risoknop]

Ok, so I have made something like this:

class MyDb
{
    private $dbh; # database connection
    private $data; # array of values for prepared statement
    private $stmt; # prepared statement
    
    function __construct()
    {
        // Database settings
        $host = 'localhost'; # won't need to change this 99% of the time
        $user = 'root'; # database user
        $pass = ''; # database password
        $name = 'test'; # name of the database
    
        // Connect to the database
        try
        {
            $this->dbh = new PDO("mysql:host=$host;dbname=$name", $user, $pass, array(PDO::ATTR_PERSISTENT => true));
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); # set the error reporting attribute
        }
        catch (PDOException $e)
        {
            printf("<p>Error: %s</p>\n", $e->getMessage());
            die();
        }
    }

    // Remove object
    function __destruct()
    {
        $this->dbh = NULL;
    }

    // Prepare a SELECT query
    function prepareSelect($table, $data = NULL)
    {
        try
        {
            if (is_array($data))
            {
                $this->data = $data;
                foreach ($data as $key => $val)
                {
                    if (!isset($cond))
                        $cond = 'WHERE '.$key.' = :'.$key;
                    else
                        $cond .= ' AND '.$key.' = :'.$key;
                }
                $this->stmt = $this->dbh->prepare("SELECT * FROM $table $cond");
                foreach ($data as $key => $val)
                {
                    if (is_int($val))
                        $this->stmt->bindParam(':'.$key, $val, PDO::PARAM_INT);
                    else
                        $this->stmt->bindParam(':'.$key, $val, PDO::PARAM_STR);
                }
            }
            else
            {
                $this->data = array();
                $this->stmt = $this->dbh->prepare("SELECT * FROM $table");
            }
        }
        catch (PDOException $e)
        {
            printf("<p>Error: %s</p>\n", $e->getMessage());    
        }
    }
    
    // Execute the prepared SELECT query
    function executeSelect($data = NULL)
    {
        try
        {
            if (is_array($data))
                $this->data = $data;
            $this->stmt->execute($this->data);
            return $this->stmt->fetchAll();
        }
        catch (PDOException $e)
        {
            printf("<p>Error: %s</p>\n", $e->getMessage());
        }
    }
} 

Which can be used as:

$conn = new MyDb();

$conn->prepareSelect('posts', array('id' => 1));
$result1 = $conn->executeSelect(); // Now the first execute will take the values from prepareSelect()
$result2 = $conn->executeSelect(array('id' => 2));

// etc 

The problem is, I cannot change the number of variables on the run

[galen]

i dont knwo what you mean by chagning the number of variables but i noticed this

    // Execute the prepared SELECT query
    function executeSelect($data = NULL)
    {
            if (is_array($data))
                $this->data = $data;

            $this->stmt->execute($this->data);
            while ($obj = $this->stmt->fetch())
                $result_array[] = $obj;
            return $result_array;
    } 

i added an if statement. Otherwise if you call it with no $data it's being set to null

[risoknop]

Thanks. I have modified the executeSelect() bit more. It looks like this now:

    // Execute the prepared SELECT query
    function executeSelect($data = NULL)
    {
        try
        {
            if (is_array($data))
                $this->data = $data;
            $this->stmt->execute($this->data);
            return $this->stmt->fetchAll();
        }
        catch (PDOException $e)
        {
            printf("<p>Error: %s</p>\n", $e->getMessage());
        }
    } 

What I meant by changing the number of variables was that if I prepare the statement like, for example this:

$conn->prepareSelect('posts', array('id' => 1)); 

Then I cannot later do:

$results = $conn->executeSelect(array('id' => 1, 'title' => 'Post title')); 

I.e. I cannot bind more variables to the query without preparing it again. Not sure if it makes sense.

[risoknop]

Well, the construct method takes the statement as the parameter.

It then stores the outcome of the prepare() function as an object property, and creates an object property holding an array of each placemarker and its current value. Bind these values to the PDOStatement object generated by prepare().

You can then execute the query numerous times by setting these array values in another function. If you use regex to find the placemarkers, it will store them in array-order, so you'd only need to place the values individually in the function parameters, then use func_get_args() in the function which runs the prepared query and set the array values by integer key rather than string key.

Hope that makes sense

Not sure if the statement can be in construct because I will probably add more methods for INSERT and UPDATE queries and possibly some transactions. I think it's alright now - it works more or less as I wanted.

[sk89q]

The problem is that you lose the reference to $data once you exit out of MyDb::prepareSelect(). You have references to three entirely different arrays here, because arrays are not passed by reference by default. The array you passed in is not the same as the array you used to prepare the statement and neither of those arrays are the same as $this->data.

When you run MyDB::executeSelect, you are replacing $this->data with a new array, so you would have lost that reference. However, $this->data is not a reference to the bounded array to begin with, so it wouldn't work anyway.

To fix your class, you'd have to store $this->data as a reference to $data in MyDb::prepareSelect, and change the values of $this->data (by reference) key by key in MyDB::executeSelect (instead of replacing the array).

Using PDOStatement::bindValue for later iterations would make the code less convoluted, but you can do it your original way as well.

EDIT: Here's what I mean:

<?php
// ======================
// this is what you are doing
// ======================

$a = array('a' => 'test', 'b' => 't');
$z = &$a['a']; // $z is the reference that bindParam has
echo "$z\n";
$b = $a; // $b is $this->data; note: no reference
$b = array('a' => 'zing', 'b' => 't'); // replacing the old array with a new one
echo "$z\n"; // output: test

// ======================
// this is half what you should be doing
// ======================

$a = array('a' => 'test', 'b' => 't');
$z = &$a['a']; 
echo "$z\n";
$b = &$a; // now as a reference
$b = array('a' => 'zing', 'b' => 't'); // still replacing the old array with a new one
echo "$z\n"; // output: test

// ======================
// this is what you should be doing
// ======================

$a = array('a' => 'test', 'b' => 't');
$z = &$a['a'];
echo "$z\n";
$b = &$a; // still as a reference
$b['a'] = "zing"; // we change the values of the OLD array key by key
echo "$z\n"; // output: zing
//

[risoknop]

I'm not sure I understand... I have set up the test database and table (and populated it with some data) and I have tested the class and it works as expected. It's returning values it should be.

[risoknop]

sk89q: I have tried to do it as you suggested but I'm not sure I got it right (I don't understand references very well):

class MyDb 
{ 
    private $dbh; # database connection 
    private $data; # reference to $data from prepareSelect() method
    private $stmt; # prepared statement 
     
    function __construct() 
    { 
        // Database settings 
        $host = 'localhost'; # won't need to change this 99% of the time 
        $user = 'root'; # database user 
        $pass = ''; # database password 
        $name = 'test'; # name of the database 
     
        // Connect to the database 
        try 
        { 
            $this->dbh = new PDO("mysql:host=$host;dbname=$name", $user, $pass, array(PDO::ATTR_PERSISTENT => true)); 
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); # set the error reporting attribute 
        } 
        catch (PDOException $e) 
        { 
            printf("<p>Error: %s</p>\n", $e->getMessage()); 
            die(); 
        } 
    } 
     
    function __destruct() 
    { 
        // Remove object 
        $this->dbh = NULL; 
    } 
     
    // Prepare a SELECT query 
    function prepareSelect($table, $data = NULL) 
    { 
        try 
        { 
            if (isset($data) && is_array($data)) 
            { 
                foreach ($data as $key => $val) 
                { 
                    if (!isset($cond)) 
                        $cond = 'WHERE '.$key.' = :'.$key; 
                    else 
                        $cond .= ' AND '.$key.' = :'.$key; 
                } 
                $this->stmt = $this->dbh->prepare("SELECT * FROM $table $cond"); 
                foreach ($data as $key => $val) 
                { 
                    if (is_int($val)) 
                        $this->stmt->bindParam(':'.$key, $val, PDO::PARAM_INT); 
                    else 
                        $this->stmt->bindParam(':'.$key, $val, PDO::PARAM_STR); 
                } 
            } 
            else 
            { 
                $data = array(); 
                $this->stmt = $this->dbh->prepare("SELECT * FROM $table"); 
            } 
            $this->data =& $data; 
        } 
        catch (PDOException $e) 
        { 
            printf("<p>Error: %s</p>\n", $e->getMessage());     
        } 
    } 
     
    // Execute the prepared SELECT query 
    function executeSelect($data = NULL) 
    { 
        try 
        { 
            if (isset($data) && is_array($data)) 
                $this->stmt->execute($data); 
            else 
                $this->stmt->execute($this->data);                 
            return $this->stmt->fetchAll(); 
        } 
        catch (PDOException $e) 
        { 
            printf("<p>Error: %s</p>\n", $e->getMessage()); 
        } 
    } 
}