Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts

    Pass in a connection type and query a database within a method?

    Hello,

    I've written a factory pattern whereby I can switch between a connection ie if it's a PDO or a Mysql connection and I'm really happy with it.

    My problem comes however when I pass that connection into a class and then use a method to query my database.

    For example, if a PDO connection is used, I'd need to do something like this syntax

    PHP Code:
     public function doQuery($table) {
       
    $QUERY $this->connection->prepare("SELECT * from $table LIMIT 1");
       
    $QUERY->execute();
       
    $RES $QUERY->fetch();
       
    print_r($RES);

    And if I used a MySQL connection, something like this

    PHP Code:
    public function doQuery($table) {
      
    $this->connection;
      
    $SQL "SELECT * from $table LIMIT 1";
      
    $RES mysql_query($SQL);
      
    $ROW mysql_fetch_array($RES);
      
    print_r($ROW);

    Is there any way I could somehow write my queries that counter for both database connections needs within the one method?

    Thank you

  • #2
    New Coder
    Join Date
    May 2012
    Posts
    50
    Thanks
    6
    Thanked 4 Times in 4 Posts
    Why do you not just use MySQL driver for pdo?

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    PDO < MySQLi if you are going for straight up MySQL - mysqli supports more native handling than the PDO driver. Since both are available in PHP, I'd recommend sticking to an aggregate and choosing one or the other. If you don't want to do the work, I'd suggest simply using PDO instead.
    Don't use the old mysql library at all. Drop that usage, and opt for MySQLi instead.

    Factorying up a connection only takes you so far. Unless you use an explicit driver for your aggregate storage, then you cannot control this. Your drivers would be responsible for interpreting what to do with doQuery, so if its running with a type of PDODriver then you would handle with a PDO, and if its a MySQLDriver you'd handle it with MySQLi. So doQuery in the Connection class would simply hand off the control to the driver running it.

  • #4
    Regular Coder Redcoder's Avatar
    Join Date
    May 2012
    Location
    /dev/null
    Posts
    333
    Thanks
    2
    Thanked 47 Times in 46 Posts
    Whats the criteria that you'll use to determine whether to use one Database driver over the other for a particular situation? Or do you need to use them both at the same time? The downgrdade from PDO to mysql and not even MySQLi doesn't make sense, at least to me.

    It does beat me why you'd use two db drivers at the same time when they do the same thing.
    Last edited by Redcoder; 10-22-2012 at 04:42 PM.

  • #5
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Thanks all for your help.

    Fou-Lu, when you say

    Factorying up a connection only takes you so far. Unless you use an explicit driver for your aggregate storage, then you cannot control this. Your drivers would be responsible for interpreting what to do with doQuery, so if its running with a type of PDODriver then you would handle with a PDO, and if its a MySQLDriver you'd handle it with MySQLi. So doQuery in the Connection class would simply hand off the control to the driver running it.
    Do you know of any example links you could suggest here please? I'm not sure I understand what you mean here and and example may help me understand.

    Thank you once again.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    The concept is exceedingly easy:
    PHP Code:
    <?php

    interface IStorage
    {
        public function 
    doQuery($s);
    }

    class 
    PDODriver implements IStorage
    {
        public function 
    doQuery($s)
        {
            print 
    __METHOD__ PHP_EOL;
        }
    }

    class 
    MySQLDriver implements IStorage
    {
        public function 
    doQuery($s)
        {
            print 
    __METHOD__ PHP_EOL;
        }
    }

    class 
    Connection
    {
        private 
    $storage;
        private function 
    __construct(IStorage $storage)
        {
            
    $this->storage $storage;
        }

        public function 
    doQuery($s)
        {
            
    $this->storage->doQuery($s);
        }

        public static function 
    createConnection($sType)
        {
            
    $sClass $sType 'Driver';
            if (!
    class_exists($sClass))
            {
                throw new 
    Exception('No drivers available for storage type: ' $sType);
            }
            return new 
    $sClass;
        }
    }

    $cPDO Connection::createConnection('PDO');
    $cMySQL Connection::createConnection('MySQL');

    $cPDO->doQuery('whatever here');
    $cMySQL->doQuery('whatever here');
    Would result in:
    Code:
    PDODriver::doQuery
    MySQLDriver::doQuery
    I'm not going to lie. Doing this properly is a lot of work. The idea is to aggregate the storage to such a generic level that you no longer want to provide a query of something like 'SELECT * FROM table'. The idea is that this should be good for *any* type of storage implemented, including filesystem files, or webservices or whatever. Whilst there is nothing wrong with SELECT field FROM table, you would then need to parse and handle it on the other side. So to fully implement a generic handling of all storage without making it excessively difficult or additional parsing requires a lot of classes and control. Things like Field would be objects, conditions - objects, joins - objects. Everything made so generic that it would mean a driver could literally assemble the pieces provided to it to end up with SELECT field, field2, fieldx FROM location WHERE condition=value LIMIT 1, or using SELECT TOP 1 field, field2, fieldx FROM location WHERE condition=value, etc, or provide a valid xpath to work with an xml file for example. This is how you literally swap storage without ever touching code that handles input or output from storage. Write a new Driver, and instantiate that (through configs), and away it goes.

  • Users who have thanked Fou-Lu for this post:

    Oatley (10-23-2012)

  • #7
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Thanks that is great and I love the idea of different storage examples. I have a few questions on this if you would be so kind to help please.

    The first is about the making a connection to either PDO or MySQL to see if this is the best way to do this that I have in my head

    So first of all I load the createConnection method

    PHP Code:
    $pdo_conn Connection::createConnection('PDO');
    $mysql_conn Connection::createConnection('MYSQL'); 
    Then in my createConnection method I then I thought maybe about using the factory pattern to loop through and connect to the appropriate connection, which a single instance of this then is then passed to the constructor for that driver when it is returned

    PHP Code:
     public static function createConnection($type) {
       
    $driver $type 'Driver'//ie PDODriver, MYSQLDriver
       
    if (!class_exists($driver)) {
         throw new 
    Exception('No drivers available for storage type: ' $type);
       }
         switch(
    $driver) {
           case 
    'PDODriver'$CONNECTION PDOConnection::getConnection(); Return new $driver($CONNECTION); break; //* Returns PDODriver object passing in connection instance
           
    case 'MYSQLDriver'$CONNECTION MYSQLConnection::getConnection(); Return new $driver($CONNECTION); break; //* Returns MYSQLDriver passing in connection instance
         
    }
       } 
    So then I could use the doQuery() method in any of the storage systems for any connection IE:

    PHP Code:
    $pdo_conn->doQuery('my_news_table');
    $pdo_conn->doQuery('my_pictures_table');

    $mysql_conn->doQuery('my_sport_table');
    $mysql_conn->doQuery('my_other_table'); 
    Is this the best way to do it?

    Thank you

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    There are several methods that you can follow to do what you want to do, but many are dependent on the PHP versions as well.
    This way is probably the most consistent making use of the reflection method for the driver's themselves. The downside is that there is no way to force a static method on an interface, so you must try/catch in order to verify that it was successful:
    PHP Code:
        public static function createConnection($sType)
        {
            
    $sClass $sType 'Driver';
            if (!
    class_exists($sClass))
            {
                throw new 
    Exception('No drivers available for storage type: ' $sType);
            }
            
    $rf = new ReflectionClass($sClass);
            return 
    $rf->getMethod('getConnection')->invoke(null);
        } 
    You can also use call_user_func to force the call to the getConnection instead of using reflection. I also believe newer versions (5.3+, I can't test above 5.2 until I get home), also allow the use of static method calls off of a variable, so if it works you can get away with using return $sClass::getConnection(); instead.

    There is nothing wrong with hard coding a set of options within a factory, but I myself would recommend trying to keep it as dynamic as possible. I'd choose a directory for it (such as __DIR__ . '/./drivers/') and put all the drivers in there so it can find them. I wouldn't iterate them, simply check if there exists a driver in there. I don't really need to do this so much anymore since I typically chain the spl autoloader to a bound class extension type (.class.php), and provide the base directory to the include path. Then I make chain uses to the use keyword which automatically includes the necessary class file.

  • Users who have thanked Fou-Lu for this post:

    Oatley (10-29-2012)

  • #9
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Sorry for the delay in replying. I've been trying to figure this out myself, without success.

    I love the idea of rather than using a factory to return a new instance I try and use the reflection class to do so to make it more dynamic

    However, if I take my factory which did connect to the database and returned a new instance when it was needed like so

    PHP Code:
    switch($driver) {
     case 
    'PDODriver'$CONNECTION PDOConnection::getConnection(); Return new $driver($CONNECTION); break;
     case 
    'MYSQLDriver'$CONNECTION MYSQLConnection::getConnection(); Return new $driver($CONNECTION); break; 

    And convert it to use the reflection class like you have in your example you kindly suggested

    PHP Code:
        $ref = new ReflectionClass($driver);
        return 
    $ref->getMethod('getConnection')->invoke(NULL); 
    I get the message

    PHP Fatal error: Uncaught exception 'ReflectionException' with message 'Method getConnection does not exist

    So I thought as the method getConnection exists in my PDO connection class as a static I tried calling it like this

    PHP Code:
     $ref  = new ReflectionClass($driver);
     return 
    $ref->getMethod(''.$type.'Connection::getConnection')->invoke(NULL);
     } 
    But that is still showing the message PHP Fatal error: Uncaught exception 'ReflectionException' with message 'Method PDOConnection::getConnection does not exist'. But I don't see why?

    Any suggestions you could kindly suggest? Also will the reflection class here return a new instance like I had in my factory?

    Sorry about all the questions, but really enjoying learning here and thanks for your help.

    Thank you
    Last edited by Oatley; 10-29-2012 at 11:03 AM.

  • #10
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    What is the $driver set as? It has to match your class' name. If you used my code its seeking a class called PDODriver, not a class called PDOConnection.
    You can't invoke statically using Class::Method in reflection. Although you can use $sClass::getConnection() in at least 5.3.

  • Users who have thanked Fou-Lu for this post:

    Oatley (10-30-2012)

  • #11
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    That's an excellent explanation and have got it working now. Thank you for clearing that up.

    Just want to ask one more thing on this, in my drivers would you recommend that I have my connection, queries method and other methods that use PDO all in one place?

    Or is it best practice to have the queries and connections in different files. For example I'm thinking of doing something like this

    PHP Code:
    class PDODriver implements Queries {

    /**
     ** Connect to the database and return a single instance of the class
     **/ 
       
    public static function getConnection() { }

    /**
     ** Run a query
     **/ 
     
    public function doQuery() { }

    /**
     ** Another method
     **/ 
     
    public function doSomething() { }

    /**
     ** Yet Another method
     **/ 
      
    public function doSomethingElse() { }


    So basically do you think that would that be reasonable/sensible to put your connection method, and other methods all in the one class together that use PDO?

    Again, as always thank you.

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Whatever interfacing you decide to do, make sure it applies to any driver and the connection as well. You can call the methods whatever you want, but implement the interface on the drivers and the connection so you can chain them and use the connection wherever a driver can be used. That's pretty much what I did in post #6 there, but forgot to add the IStorage interface to connection.
    You can break them down as you see fit. IQuerable, IDeletable, ICreatable, IModifyable are four interfaces that would make sense to me. These can all be typehinted into any other method to be try/caught. I'm not sure how useful said methods would be in an actual storage handling structure, but they'd sure be useful on objects themselves to determine if they can be deleted or modified.

  • Users who have thanked Fou-Lu for this post:

    Oatley (10-30-2012)

  • #13
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    That's great again thank you.

    Last couple of questions on this if that's Ok Please?

    (1) If I have an interface called 'Query' for example, and I implemented it across all my drivers for different types of storage for example

    PHP Code:
    Class PDO implements Query {}
    Class 
    MySQL implements Query {}
    Class 
    FileReader implements Query {}
    Class 
    XML implements Query{}
    Class 
    Webservice implements Query{} 
    Would I want to do that? It makes sense to me that my PDO class and MySQL class could have a query method in it (as they query the database), but would a FileReader and an XML storage system definitely have them, or should I only implement the Query interface as see fit? So basically should all my storage classes implement Query?

    (2) When you mentioned a storage type as filesystem files, can you explain a bit more what you mean by this please? Did you envisage this being a system to upload files to your webserver or something Or looping through a file to do something with the text in it? Or something else.

    (3) In my storage example classes above are there any other storage systems working on the web that maybe would be useful with this system?

    Again, sorry for all the questions, but really enjoying this and learning a lot so, thank you once again.

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    If its a driver, then you would implement the Query interface on all of the possible options, otherwise it defeats the ability to write drivers with a generic storage mechanism. Query isn't a term used just for SQL, but if you intend to use an SQL then you'd need to write a custom 4th gen language parser to make it work with filesystems. Or just don't use SQL during usage and let the drivers do what they need to do.

    For filesystem files, they can be as simple as reading an entire file and finding what you need, to as complex as writing a custom m-tree to represent indexing and using random access files for lookup. These files would contain the data instead of a database. Obviously indexing and RAF would be better than reading entire files.

    There are plenty of storage options to choose from. Beyond what you have there are many other types of databases such as SQLServer, Oracle and Postgres. There are other options like FTP. I'd only worry about database and webservice levels tbh; what matters would be that you *can* write a driver for another system when required. PDO is already an aggregate layer as well, so you can give storage drivers to it for multiple database engines (if available). A native option is more preferable since it will operate better than an abstraction option.
    One thing to keep in mind is doing this is only useful if you have a reason to do this. If you know you will only ever use a storage engine on SQLServer OR on Oracle, but never another dbms or filesystem, then PDO may be easier just for simplicity (although best I know the drivers for PDO on both SQLServer and Oracle are deemed experimental). Just like if I knew that I'd only write for MySQL then I'd use the MySQLi.
    Writing an abstraction layer, like anything with OOP, will reduce overall performance of the system. What you gain though is the ease of creating new drivers.

  • Users who have thanked Fou-Lu for this post:

    Oatley (11-01-2012)

  • #15
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Thank you, brilliant advice.

    Just have this one last problem now before I close this thread if you would be kind enough to help again. I can connect to the database, and output the I am successfully connected to the database message below in the getConnection() method

    But I can't seem to run my doQuery method from within the driver now and I can't see why

    PHP Code:
    $obj Connection::createConnection('My');
    $obj->doQuery(); 

    PHP Code:

    include 'storage.interface.php';

    class 
    Connection {

    public static function 
    createConnection($type) {
       
    $driver $type 'Driver';
       if (!
    class_exists($driver)) {
         throw new 
    Exception('No drivers for storage type: ' $type);
       }
       
    $ref = new ReflectionClass($driver);
         return 
    $ref->getMethod('getConnection')->invoke(NULL);
       }
    }

    class 
    MyDriver implements Storage {

      
    /**
       * Store a single instance of the database
       */
      
    private static $instance;

      
    /**
       * Private Constructor forces the Object instanciation inside of class
       */
       
    private function __construct() {}
     
     
    /**
      * Getter method for returning a single instance of the class
      */
      
    public static function getConnection() {
       if(!isset(
    self::$instance)) {
         
    //DB conn stuff removed
         
    echo 'successfully connected';
         return 
    self::$instance;
       }

      public function 
    doQuery() {
         echo 
    "This Query message however is never shown on the page and I can not see why<br />";
      }

    Thank you
    Last edited by Oatley; 11-01-2012 at 01:36 PM.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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