SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 26
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)

    Extended PDO API

    I've been playing with extensions to PDO and PDOStatement, trying to get database statements working even more smoothly. Without going through the exact code, this is what I'm doing in the extension. I'm wanting help spotting potential problems because this is so low level that tons of code are going to end up sitting on it and I don't want to be rewriting it.

    Gazelle\Database extends PDO

    override __construct:
    @param mixed (string or array)

    Extended to accept a configuration array rather than just a DSN - however it will still accept a DSN. Also sets PDOAttribute defaults for the framework - placing PDO into Throw Exceptions mode and setting the Statement class below as the default statement object.


    Gazelle\Statement extends PDOStatement

    override __construct:
    @param Database object

    Accept a reference to the creating db object? At the moment I haven't needed this. The plan was to have the statement update the main database object with performance information like query execution time. With PHP 5.4 on the horizon though I've decided to hold off to a later version of the framework and farm such debug related tasks to Traits that can be attached to the object only when the Debug mode is invoked explicitly. For the moment I've left this alone.

    override execute
    [@param array]
    @return this

    Parent returns Bool. The override function returns this for chaining statements and throws an error if the parent method returned false.

    bindArray
    @param array

    Bind an array by its keys to the statement. The draft of this function has me uneasy because it can get messed up by the calling code pretty easy and I'd like to harden it. Currently if the array has named keys then the statement should have named parameter tokens. If the array has numeric keys the statement should have question mark tokens. I'd like to tolerate receiving an array with numeric keys even if the statement has named tokens, but to do this I need to be able to retrieve a token list in order. PDOStatement has no public method that does this, does it have an undocumented protected method that provides this?

    The function returns $this for chaining purposes.

    key
    [@param mixed (string or integer)]

    This statement causes the statement to index the return on the argument (if present) or first column (if not) instead of generating a key. return self for chaining. If this function never gets called the first column is used when indexed functions are explicitly requested.

    fetchMixed
    @return mixed

    Fetches the data based on the nature of the return. The using programmer should be able to predict what this is going to return based on the structure of the query. For example, "SELECT `name` FROM `table` WHERE `id` = 1 LIMIT 1" will return a single value, so fetchMixed returns just that value, not an array of 1 row with an array of 1 column with the value. fetchMixed is a very powerful shorthand, but if queries aren't watched it can cause trouble. There are times when we need to force the return to stay an array, and other functions exist for this as we go down the list.

    fetchAllAssoc
    @return container array

    This started life as an alias to fetchAll( PDO::FETCH_ASSOC ), but when I wrote the key function I realized this should be affected by that. If key is called then this function will move the contents of the key column into the key position of the return array.

    fetchIndexedResults
    Returns the results with the key set on the return. If there's only two columns this function will return a single array as if you'd called fetchAll(PDO::FETCH_COLUMN) but with the keys being one of the columns (by default the first) and the values being the other.

    fetchCollection
    @parameter Collator

    This function collates the results into a structured return. By default the structure is inferred from the key names. Collation of sql returns into multidimensional arrays is perhaps one of the most common tasks in PHP. The function can accept a collator object to do the collation - otherwise it creates one of class TierCollator.

    fetchTree
    This function uses two collators - first collating the rows into multidimensional array, and then passes that to a tree collator that creates a result tree. This is used by the core framework, but its use is infrequent enough I'm considering pulling it out of the base statement object.

    The addition of chaining allows for these statements;
    PHP Code:
    $ret $db->prepare("SELECT * FROM table WHERE name = :name")->bindArray($array)->execute()->fetchMixed();

    $ret $db->query("SELECT * FROM table WHERE name = '{$name}'")->fetchMixed(); 
    Thoughts?

  2. #2
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tend to favour a more simplistic approach, using closures rather than extending PDO.

    PHP Code:
    class t
    {
        function 
    prepare($sql)
        {
            
    $q $this->pdo->prepare($sql);
            return function(array 
    $parameters) use ($q
            {
                if (
    $q->execute($parameters))
                    return function() use (
    $q)
                    {
                        return 
    $q->fetch();
                    };
                return 
    false;
            };
        }


    PHP Code:
        $f $t->prepare("SELECT * FROM table WHERE name = :name");
        
    $i $f(['name' => 'foo']);
        while (
    $row $i())
            echo 
    json_encode($row), "\n"

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    Tend to favour a more simplistic approach, using closures rather than extending PDO.
    And yet you'll have to write those closures over and over would you not?

    Besides, the goal here is, externally, this would be a simplistic approach. Currently fetching an array from PDO is like this at a minimum.

    PHP Code:
    $result $pdo->query($query)->fetchAllPDO::FETCH_ASSOC ); 
    Chaining can already be done, but it's pretty limited in scope. This doesn't allow you to bind parameters or alter the array. If you need that it balloons

    PHP Code:
    $statement $pdo->prepare($query);

    foreach (
    $array as $key => $value) {
      
    $statement->bindValue($key$value);
    }

    $statement->execute();

    $result $statement->fetchAllPDO::FETCH_ASSOC ); 
    By comparison

    PHP Code:
    $result $pdo->prepare($query)->bindArray($array)->execute()->fetchAllAssoc(); 
    That is much, much simpler. The old functionality isn't lost for those corner cases when it is needed, but most use cases should be able to use these new functions.

  4. #4
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    And yet you'll have to write those closures over and over would you not?
    Not sure I understand


    Quote Originally Posted by Michael Morris View Post
    Besides, the goal here is, externally, this would be a simplistic approach. Currently fetching an array from PDO is like this at a minimum.

    PHP Code:
    $result $pdo->query($query)->fetchAllPDO::FETCH_ASSOC ); 
    Chaining can already be done, but it's pretty limited in scope. This doesn't allow you to bind parameters or alter the array. If you need that it balloons

    PHP Code:
    $statement $pdo->prepare($query);
    /*
    foreach ($array as $key => $value) {
      $statement->bindValue($key, $value);
    }
    */
    $statement->execute($array);

    $result $statement->fetchAllPDO::FETCH_ASSOC ); 
    By comparison

    PHP Code:
    $result $pdo->prepare($query)->bindArray($array)->execute()->fetchAll(PDO::FETCH_ASSOC); 
    That is much, much simpler. The old functionality isn't lost for those corner cases when it is needed, but most use cases should be able to use these new functions.
    Not sure there is any advantage of a fluent interface in this case.

  5. #5
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    I would suppose a fluid interface is it's own reward - that and code reuse. I'd worry about using closures to tack functionality onto the statement object because it feels like a kludge. I can understand using it if you have to maintain compatibility with existing code, but that isn't the case here.

    Still, maybe this will make it clearer. This is one of the functions out of the new class (Actually, I've written all of the functions but haven't completed writing the corresponding tests and running them. This is a rare instance of writing the test second for me).

    Code php:
    	public function fetchIndexedResults() {
    		$k = $this->key ? $this->key : 0;
     
    		$return = array();
     
    		// If the key is numeric, fetch the results with numeric keys, else fetch using named keys.
    		while ($row = $s->fetch( is_numeric($k) ? \PDO::FETCH_NUM : \PDO::FETCH_ASSOC )){	
    			// Set the key
    			$key = $row[$k];
    			// Remove the key from the row result.
    			unset($row[$k]);
     
    			// If this leaves the row with one column only, reduce down to just that value.
    			if (count($row) == 1) {
    				$row = array_shift($row);
    			}
     
    			// Now bind to the return.
    			$return[$key] = $row;
    		}
     
    		return $return;
    	}

    While not the world's longest function, it does provide a clean way to get this common use case - a SQL query of values off one column, with the keys being the primary key of the table. So the results we're looking for look like this

    PHP Code:
    array (
      
    => 'James',
      
    => 'John',
      
    => 'Jerry'
    ); 
    PDO has no way to do this as written - if you fetch the name column there is no association retained with the primary keys in the database. And calling fetchAll gives this structure.

    PHP Code:
    array (
      
    => array (
        
    'id' => 1
        
    'name' => 'James'
      
    )
    ... 
    So this extending class saves a lot of time with this very common use case. The query is

    Code php:
    $nameList = $pdo->prepare("
      SELECT id, name
      FROM owners
      WHERE status = ?
    ")->execute(array($status))->fetchIndexedResults();

    And that's it.

  6. #6
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $pdo = new PDO('sqlite::memory:''''');

    $pdo->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
    $pdo->exec('CREATE TABLE Test(id INTEGER NOT NULL PRIMARY KEY, name TEXT)');

    $pdo->exec('INSERT INTO Test(name) VALUES(\'Rod\')');
    $pdo->exec('INSERT INTO Test(name) VALUES(\'Jane\')');
    $pdo->exec('INSERT INTO Test(name) VALUES(\'Freddy\')');
    $pdo->exec('INSERT INTO Test(name) VALUES(\'Tom\')');
    $pdo->exec('INSERT INTO Test(name) VALUES(\'Dick\')');
    $pdo->exec('INSERT INTO Test(name) VALUES(\'Harry\')');

    $q $pdo->prepare('SELECT id, name FROM Test ORDER BY name ASC');
    $q->execute();
    $r $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

    var_dump($r); 
    Code:
    array(6) {
      [5]=>
      array(1) {
        ["name"]=>
        string(4) "Dick"
      }
      [3]=>
      array(1) {
        ["name"]=>
        string(6) "Freddy"
      }
      [6]=>
      array(1) {
        ["name"]=>
        string(5) "Harry"
      }
      [2]=>
      array(1) {
        ["name"]=>
        string(4) "Jane"
      }
      [1]=>
      array(1) {
        ["name"]=>
        string(3) "Rod"
      }
      [4]=>
      array(1) {
        ["name"]=>
        string(3) "Tom"
      }
    }

  7. #7
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    ? What is that supposed to be?

  8. #8
    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)
    It shows how to do exactly what your function does, already built into PDO:
    PHP Code:
    $q $pdo->prepare('SELECT id, name FROM Test ORDER BY name ASC'); 
    $q->execute(); 
    $r $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC); 
    PDO's FETCH_ flags are doorways to some very useful things.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  9. #9
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Except it doesn't do what my function does...

    Outputs of each are...

    Code:
    // Ren's example...
    Array
    (
        [5] => Array
            (
                [name] => Dick
            )
    
        [3] => Array
            (
                [name] => Freddy
            )
    
        [6] => Array
            (
                [name] => Harry
            )
    
        [2] => Array
            (
                [name] => Jane
            )
    
        [1] => Array
            (
                [name] => Rod
            )
    
        [4] => Array
            (
                [name] => Tom
            )
    
    )
    
    // My function's output...
    Array
    (
        [5] => Dick
        [3] => Freddy
        [6] => Harry
        [2] => Jane
        [1] => Rod
        [4] => Tom
    )
    I wasn't aware of the unique and group flags or mixing them with bitwise operators. That I could use to optimize what I'm writing.

  10. #10
    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)
    Ah I see what you mean. Well yeah I suppose, though the difference is small. It'd just be a case of array_walking it; don't know how that would benefit in terms of optimisation but yeah.

    I extend PDO simply to return customised PDO Statements, so that on error it's saved to a log with the information I require to fix it. However, I use a wrapper class which handles every type query which I'll use, because I can't stand using queries in the middle of code. Because only one class handles the queries, adding functionality to PDO is usually at an unnecessary cost.

    But if we all programmed the same, there would be no progress in the world.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2006
    Location
    Poland
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jake Arkinstall View Post
    Ah I see what you mean. Well yeah I suppose, though the difference is small. It'd just be a case of array_walking it; don't know how that would benefit in terms of optimisation but yeah.
    PHP Code:
    $r $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_COLUMN); 
    That would do the job, no need for array_walk

  12. #12
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dan7 View Post
    PHP Code:
    $r $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_COLUMN); 
    That would do the job, no need for array_walk
    NO, It will not. That statement returns:

    Code:
    Array
    (
        [5] => Array
            (
                [name] => Dick
            )
    
        [3] => Array
            (
                [name] => Freddy
            )
    
        [6] => Array
            (
                [name] => Harry
            )
    
        [2] => Array
            (
                [name] => Jane
            )
    
        [1] => Array
            (
                [name] => Rod
            )
    
        [4] => Array
            (
                [name] => Tom
            )
    
    )
    The desired output is:

    Code:
    Array
    (
        [5] => Dick
        [3] => Freddy
        [6] => Harry
        [2] => Jane
        [1] => Rod
        [4] => Tom
    )

    Quote Originally Posted by Jake Arkinstall View Post
    I extend PDO simply to return customised PDO Statements, so that on error it's saved to a log with the information I require to fix it. However, I use a wrapper class which handles every type query which I'll use, because I can't stand using queries in the middle of code. Because only one class handles the queries, adding functionality to PDO is usually at an unnecessary cost.

    But if we all programmed the same, there would be no progress in the world.
    Most of my frequently used read queries exist in the database as views. Write queries have a rudimentary ORM to compose them. This extender class doesn't have the responsibility of managing that though.

  13. #13
    SitePoint Zealot
    Join Date
    Aug 2006
    Location
    Poland
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    NO, It will not. That statement returns:

    Code:
    Array
    (
        [5] => Array
            (
                [name] => Dick
            )
    
        [3] => Array
            (
                [name] => Freddy
            )
    
        [6] => Array
            (
                [name] => Harry
            )
    
        [2] => Array
            (
                [name] => Jane
            )
    
        [1] => Array
            (
                [name] => Rod
            )
    
        [4] => Array
            (
                [name] => Tom
            )
    
    )
    The desired output is:

    Code:
    Array
    (
        [5] => Dick
        [3] => Freddy
        [6] => Harry
        [2] => Jane
        [1] => Rod
        [4] => Tom
    )
    You sure you tried my version here? Because the output did not change at all, while the flags did... I'm quite sure it works, because I use it in my projects quite often..

  14. #14
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Off Topic:


    Quote Originally Posted by Michael Morris View Post
    Most of my frequently used read queries exist in the database as views.
    Now if only MySQL would support materialized views, that would really speed stuff up a lot. Without them I don't really see the need for views unless the queries are extremely complex. Unless you're using another DBMS of course.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Screw materialized views – hierarchical queries like Oracle is what MySQL needs!
    The only code I hate more than my own is everyone else's.

  16. #16
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dan7 View Post
    PHP Code:
    $r $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_COLUMN); 
    That would do the job, no need for array_walk
    Ah, FETCH_COLUMN didn't think to try that. Rarely use PDO.

  17. #17
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    I didn't catch the last argument being changed from FETCH_ASSOC to FETCH_COLUMN. Hmm... I don't think that means my function is irrelevant, but it can be optimized.

    Code php:
    public function fetchIndexedResults() {
    	return $this->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|(  $this->columnCount() == 2 ? PDO::FETCH_COLUMN : PDO::FETCH_ASSOC)); 
    }

    This does lose the ability to specify the key to index on, but that's a minor concern as, in any event, there should not be more than one unique keys for the query.

  18. #18
    SitePoint Evangelist
    Join Date
    Aug 2005
    Location
    Winnipeg
    Posts
    498
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats a neat feature I wonder if my own DB abstraction classes support such functionality that if only a single field per record is returned it merges and returns a single dimensional array. If it doesn't consider this idea borrowed

    Cheers,
    Alex
    The only constant in software is change itself

  19. #19
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Couple questions. First, I for another function idea I found I need to return a new statement. To do that I need a reference to the database. The cleanest way to do that is to have the database object provide a reference to itself as a construct argument.

    However, If I so much as define a __construct function (even an entirely empty one "public function __construct(){}" ) for the PDOStatement, PDO stops using it as the statement object and instead falls back to using PDOStatement!!! This is infuriating since the documentation indicates it should be possible. Using the refection API I verified that there isn't an internal construct statement. I'm stumped and a little angry here.

    EDIT: Ok, I discovered the problem. PDOStatement::__construct is PROTECTED!!!!!!

    What the Hell PHP team??? What the Hell???

    I thought __construct was ALWAYS supposed to be public???

  20. #20
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    This came from the comments section in the manual. The assumption is that statements are only created from the pdo object itself.
    PHP Code:
    class Database extends PDO {
        function 
    __construct($dsn$username=""$password=""$driver_options=array()) {
            
    parent::__construct($dsn,$username,$password$driver_options);
            
    $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('DBStatement', array($this)));
        }
    }
    class 
    DBStatement extends PDOStatement {
        public 
    $dbh;
        protected function 
    __construct($dbh) {
            
    $this->dbh $dbh;
        }


  21. #21
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that's where I spotted it, but it doesn't answer why? No other PHP class has a protected constructor nor can they.

  22. #22
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    Yeah, that's where I spotted it, but it doesn't answer why? No other PHP class has a protected constructor nor can they.
    It doesn't even have a __constructor.

    Code:
    php --rc PDOStatement

  23. #23
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    Yeah, that's where I spotted it, but it doesn't answer why? No other PHP class has a protected constructor nor can they.
    Can you give an example of how you planned on using it? There just doesn't seem to be any useful public methods for actually initializing the object once you new'ed it. Adding some additional read methods to the class, sure. But it seems like you would really have to go pretty far outside the box to create one of these outside of the PDO object.

  24. #24
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    It doesn't even have a __constructor.

    Code:
    php --rc PDOStatement
    Its children are allowed to have one.

    http://us3.php.net/manual/en/pdo.setattribute.php

    Revlevant line "PDO::ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requires array(string classname, array(mixed constructor_args)). "

    The weird thing is this constructor must be protected instead of public like all other constructors in PHP. :\

    Quote Originally Posted by ahundiak View Post
    Can you give an example of how you planned on using it? There just doesn't seem to be any useful public methods for actually initializing the object once you new'ed it. Adding some additional read methods to the class, sure. But it seems like you would really have to go pretty far outside the box to create one of these outside of the PDO object.
    Certainly. The need grew out of this function which lives in a class called "DataDispatcher"

    Code php:
    public function view ( $name, array $params = array()) {
      if (empty($this->views)) {
        $this->setupViews();
      }
     
      $s = $this->db->prepare("SELECT * FROM {$this->views[$name]['view']}")->match($params)->execute();
     
      switch ($this->views[$name]['collation']) {
        case 'tree':return $s->fetchTree();
        case 'tier': return $s->fetchCollection();
        default: return $s->fetchIndexed();
      }
    }

    The object's collection of views are mysql views which essentially are prestored but repeatedly used queries. Eventually the datadispatcher will cache this information and only execute a database query to build the cache. Fairly low level stuff, but I'm not ready to worry about the caching yet. When a view is selected from we call it by name and display all of its fields (which is why this is one of the only times I use the sql wildcard). I wanted to pass in parameters for the view in case I want to filter the view to a matching set of parameters. That would involve adding a where clause to the statement. But to really do that I think I need to start with a new query. Hence the match function was added to the statement class

    Code php:
    /**
     * Append a where condition to the query string where the keys of the array
     * are the field names and the values they hold are what should they should
     * match to.  This function doesn't check for the validity of doing this to
     * the statement.
     * 
     * @param array
     * @return Statement (A new one. The current statement object dies).
     */
    public function match( $array ) {
      if (empty($array)) {
        return $this;
      } else {
        $sql = $this->queryString.' WHERE 1';
     
        foreach (array_keys($array) as $value) {
          $sql .= " AND {$value} = :{$value} ";
        }
     
        return $this->db->prepare($sql)->bindArray($array);
      }
    }

    As can be seen, match needs to create a new statement - to do that it needs a reference to the main pdo db object, something it doesn't normally have. So that is why it needs to have a constructor.

    (Note, I can see that match has drawbacks and problems and I'm not sure yet whether I'm going to keep it for this reason yet or not. For example, what if the user already bound vars to the statement? These will be lost in translation and unless the programmer is familiar with what match really does this might not be obvious. That's why the comments point out that match creates a new statement object from the query string of the old one, losing anything bound to the old).

    Code php:
    protected function __construct( $db ) {
      $this->db = $db;
    }

    Meanwhile the constructor of the database object itself passes a reference of itself to the statement. Indeed, at the moment constructor redefining is the only thing I've done to the root pdo object

    Code php:
    class Database extends \PDO {
      /**
       * CONSTRUCT. Convert the configuration array into a DSN and pass that down to
       * PDO.
       * @param array $config
       */
      public function __construct( array $config ) {
        assert (
          ((isset($config['database']) && $config['database']) || 
            (isset($config['dsn']) && $config['dsn'])) && 
            isset($config['user']) && isset($config['password'])
          );
     
        if (isset($config['dsn'])) {
          // If a DSN is set use it without question.
          parent::__construct($config['dsn'], $config['user'], $config['password']);
        } else {
          // Otherwise hash the vars we were given into a DSN and pass that.
          $params = array(
            'driver' => isset($config['driver']) ? $config['driver'] : 'mysql',
            'database' => $config['database'],
            'user' => $config['user'],
            'password' => $config['password'],
            'server' => isset($config['server']) ? $config['server'] : 'localhost',
            'port' => isset($config['port']) ? $config['port'] : '3306'
          );
     
        // Start underlying PDO library.
          parent::__construct("{$params['driver']}:dbname={$params['database']};host={$params['server']};port={$params['port']}", 
            $params['user'],
            $params['password']
          );
        }
     
        // Now set the standard behaviors of the Gazelle Framework
        $this->setAttribute(self::ATTR_STATEMENT_CLASS, array('Gazelle\Statement', array($this)));
        $this->setAttribute(self::ATTR_ERRMODE, self::ERRMODE_EXCEPTION);
      }
    }
    Last edited by Michael Morris; Sep 21, 2011 at 07:35. Reason: merging posts.

  25. #25
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    As can be seen, match needs to create a new statement - to do that it needs a reference to the main pdo db object, something it doesn't normally have. So that is why it needs to have a constructor.
    But the code snippet from the manual I posted shows exactly how to pass $db to the derived statement class. Just need to declare the constructor as protected. Clearly pdo does some majic in actually calling creating the statement and calling the constructor but it should still work as advertised.

    In any event I suspect you will talk yourself out of putting match() in the statement class.
    $statement = $db->prepareWithMatchingParams("SELECT * FROM $viewName",$matchParams);


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
  •