SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with PDO prepared statement function

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

    PHP Code:
    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$valPDO::PARAM_INT);
                    else
                        
    $stmt->bindParam(':'.$key$valPDO::PARAM_STRstrlen($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:

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

  2. #2
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edited the function a bit...

  3. #3
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yep, you'd want an object for this.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  5. #5
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I have made something like this:

    PHP Code:
    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_ERRMODEPDO::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$valPDO::PARAM_INT);
                        else
                            
    $this->stmt->bindParam(':'.$key$valPDO::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:

    PHP Code:
    $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
    Last edited by risoknop; Nov 18, 2008 at 19:24.

  7. #7
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i dont knwo what you mean by chagning the number of variables but i noticed this

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

  8. #8
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I have modified the executeSelect() bit more. It looks like this now:

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

    PHP Code:
    $conn->prepareSelect('posts', array('id' => 1)); 
    Then I cannot later do:

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

  9. #9
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    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.

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2008
    Posts
    1,149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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
    //

  11. #11
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):

    PHP Code:
    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_ERRMODEPDO::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$valPDO::PARAM_INT); 
                        else 
                            
    $this->stmt->bindParam(':'.$key$valPDO::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()); 
            } 
        } 

    Last edited by risoknop; Nov 19, 2008 at 16:40.


Bookmarks

Posting Permissions

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