#1
  1. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487

    Unable to access multiple data sources with PDO


    Hi all
    I am using an excellent 'db.php' PDO utility that (I think) was sent to me / written by Northie from the forums here, I have used it for ages without any problem but today I needed to add a connection to a MS SQL system. I have customised the class and it connects ok but the problem I am having is it's not letting me call multiple sources within one script.

    Ok, here's the DB.PHP

    PHP Code:
    <?php
    class DefaultDMO {

        public 
    $conn;
        private 
    $SQL;
        private 
    $RSarray;
        private 
    $RSrow;
        private 
    $RSval;

        public function 
    __construct() {

        }

        
    /**
         * Set connection
         */

        
    public function setConnection(&$link) {
            
    $this->conn = &$link;
        }

        
    /**
         * Execute SQL
         */
        
        
    public function Execute($sql,$args=array()) {
            
            
            
    /**
             * Prepare SQL
             */
            
            
    try {        
                if(
    $this->conn != null) {
                    try {
                        
    $this->SQL $this->conn->prepare($sql);                    
                    } catch( 
    PDOExecption $e ) {
                        echo 
    $e->getMessage();
                        die();
                    }
                } else {
                    throw new 
    Exception('SQL Execution failed - Connection Closed');
                }
            } catch(
    Exception $e ) {
                echo 
    $e->getMessage();
                die();
            }

            
    /**
             * Execute prepared statement with supplied arguments, $args
             */

            
    try {
                
    $this->SQL->execute($args);
                
                
    $error_info $this->SQL->errorInfo();
                
                if(
    $error_info[1] > 0) {
                    throw new 
    Exception('SQL Error => <pre>'.print_r($error_info,1).'</pre>');
                }        
                
            } catch( 
    Exception $e ) {
                echo 
    $e->getMessage();
                
                
    $f $this->formatSQL($sql,$args);
                
                echo 
    '<pre>'.$f['tabbed']."</pre>\n\n";
                
                echo 
    '<pre>'.preg_replace('/\s+/',' ',str_replace(array("\n","\t"),' ',$f)).'</pre>';
                
                
    $errors++;
                
                
            } catch( 
    PDOExecption $e ) {
                echo 
    $e->getMessage();
                
                
    $f $this->formatSQL($sql,$args);
                
                echo 
    '<pre>'.$f['tabbed']."</pre>\n\n";
                
                echo 
    '<pre>'.preg_replace('/\s+/',' ',str_replace(array("\n","\t"),' ',$f)).'</pre>';
                
                
    $errors++;
            }
            
            if(
    $errors 0) {
                die(
    'Script Ended Early');
                return 
    false;
            }

            return 
    $this;
        }

        
    /**
         * Get an associative array of ALL ROWS from the SQL and store in local variable RSArray
         */

        
    public function returnArray() {
            return 
    $this->SQL->fetchAll(PDO::FETCH_ASSOC);
        }
        
        
    /**
         * Get an associative array of ONE ROW values from the SQL and store in local variable RSArray
         */

        
    public function returnRow($row=0) {
            if(
    $row == 0) {
                
    $rs $this->SQL->fetch(PDO::FETCH_ASSOC);
            } else {
                
    $t $this->fetchArray();
                
    $rs $t[$row];
            }
            return 
    $rs;
        }
        
        
    /**
         * Get a single column valaue
         */

        
    public function returnVal($col,$row=0) {
            if(
    $row == 0) {
                
    $t $this->SQL->fetch(PDO::FETCH_ASSOC);
            } else {
                
    $array $this->fetchArray();
                
    $t $array[$row];
            }        
            
    $rs $t[$col];
            return 
    $rs;
        }

        
    /**
         * Put the value of RSArray into output variable, passed by reference
         */

        
    public function fetchArray(&$output) {        
            
    $output $this->returnArray();
            return 
    $this;
        }

        
    /**
         * Put the value of RSRow into output variable, passed by reference
         */

        
    public function fetchRow(&$output,$row=0) {
            
    $output $this->returnRow($row);
            return 
    $this;
        }

        
    /**
         * Put the value of RSVal into output variable, passed by reference
         */

        
    public function fetchVal(&$output,$col,$row=0) {
            
    $output $this->returnVal($col,$row);
            return 
    $this;
        }

        
    /**
         * get the number of affected rows and put into output variable, passed by reference
         */

        
    public function returnNumAffectedRows() {
            return 
    $this->SQL->rowCount();
        }

        public function 
    fetchNumAffectedRows(&$count) {
            
    $count $this->returnNumAffectedRows();
            return 
    $this->CommonReturn();
        }

        
    /**
         * get the last insert ID it put into output variable, passed by reference
         */    

        
    public function returnLastInsertID() {
            return 
    $this->conn->lastInsertId();
        }

        public function 
    fetchLastInsertID(&$id) {
            
    $id $this->returnLastInsertID();
            return 
    $this->CommonReturn();
        }

        public function 
    returnQueryString() {
            return 
    $this->SQL->queryString;
        }
        
        public function 
    formatSQL($sql=false,$args=false) {
        
            if(!
    $sql) {
                
    $sql $this->_sql;
            }
            
            if(!
    $args) {
                
    $args $this->_args;
            }
            foreach(
    $args as $key => $val) {
                
    $find[] = ':'.$key;
    //            if (substr($key,0,5)=="like_") {
    //                $replace[] = "'%".$val."%'";
    //            } else {
                    
    $replace[] = "'".$val."'";
    //            }
            
    }
            
            
    $sql_string str_replace($find,$replace,$sql);
            return array(
    'tabbed'=>$sql_string,'no-tabs'=>str_replace("\t",'',$sql_string));
        }
    }

    class 
    PDOMySql {
        private 
    $db_host;
        private 
    $db_name;
        private 
    $db_user;
        private 
    $db_pass;
        
        public 
    $conn;

        
    /**
         * get settings, noramalise database credentials attempt to connect
         */

        
    public function __construct($host,$db,$user,$pass) {
            
    $this->db_host $host;
            
    $this->db_name $db;
            
    $this->db_user $user;
            
    $this->db_pass $pass;
        }

        
    /**
         * Attempt to Connect
         */
        
    public function Connect() {
        
            try {
                
    $this->conn = new PDO("mysql:host=".$this->db_host.";dbname=".$this->db_name$this->db_user$this->db_pass);
            } catch (
    PDOException $e) {
                echo 
    "ERROR: " $e->getMessage();
                
    $this->conn false;
                die();
            }
            
            return 
    $this->conn;
                
        }
        
        public function 
    DisConnect() {
            
    $this->conn null;
        }
    }

    class 
    PDOMsSql {
        private 
    $db_host;
        private 
    $db_name;
        private 
    $db_user;
        private 
    $db_pass;
        
        public 
    $conn;

        
    /**
         * get settings, noramalise database credentials attempt to connect
         */

        
    public function __construct($host,$db,$user,$pass) {
            
    $this->db_host $host;
            
    $this->db_name $db;
            
    $this->db_user $user;
            
    $this->db_pass $pass;
        }

        
    /**
         * Attempt to Connect
         */
        
    public function Connect() {
            try {
                
    $this->conn = new PDO('dblib:host='.$this->db_host.';dbname='.$this->db_name$this->db_user$this->db_pass);
            } catch (
    PDOException $e) {
                echo 
    "ERROR: " $e->getMessage();
                
    $this->conn false;
                die();
            }
            
            return 
    $this->conn;
                
        }
        
        public function 
    DisConnect() {
            
    $this->conn null;
        }
    }

    class 
    DB {

        private static 
    $instance;
        private 
    $settings = array();
        private 
    $connections = array();

        private function 
    __construct($db) {
    showdata $db );
            switch ( 
    $db ) {
                default:
                    
    $this->settings[$db]['host'] = 'mysql_server';
                    
    $this->settings[$db]['user'] = 'mysql_username';
                    
    $this->settings[$db]['pass'] = 'mysql_password';
                    
    $this->settings[$db]['name'] = 'mysql_table';
                    
    $this->settings[$db]['type'] = 'mysql';
                break;
            case 
    "mssql_label":
                    
    $this->settings[$db]['host'] = 'mssql_server';
                    
    $this->settings[$db]['user'] = 'mssql_username';
                    
    $this->settings[$db]['pass'] = 'mssql_password';
                    
    $this->settings[$db]['name'] = 'mssql_table';
                    
    $this->settings[$db]['type'] = 'sqlsrv';
                break;
            }
        }

        
    /**
         * $rs = DB::Load('zest')->Execute($sql,$args)->returnArray();
         */

        
    public static function Load($db=false) {
            if(!isset(
    self::$instance)) {
                
    $c __CLASS__;
                
    self::$instance = new $c($db);
            }
            return 
    self::$instance->getConnection($db);
        }
            
        public function 
    getConnection($dsn) {
        
            
    //wrap in try-catch block?
            
            
    if(!isset($this->connections[$dsn])) {
                
                switch(
    strtoupper($this->settings[$dsn]['type'])) {
                    case 
    'MYSQL':
                        
    //create new connection object, and pass in credentials
                        
    $c = new PDOMySQL(
                            
    $this->settings[$dsn]['host'],
                            
    $this->settings[$dsn]['name'],
                            
    $this->settings[$dsn]['user'],
                            
    $this->settings[$dsn]['pass']
                        );
                        break;
                    case 
    'SQLSRV':
                        
    $c = new PDOMsSQL(
                            
    $this->settings[$dsn]['host'],
                            
    $this->settings[$dsn]['name'],
                            
    $this->settings[$dsn]['user'],
                            
    $this->settings[$dsn]['pass']
                        );
                        break;
                }
                
    //connect, $link passed by reference so is 'returned' with the db link in it
                
    $link $c->Connect();

                
    //create new DMO
                
    $dmo = new DefaultDMO;

                
    //pass link to DMO
                
    $dmo->setConnection($link);

                
    //keep track of link
                
    $this->resources[$dsn] = &$c;

                
    //keep track of DMO
                
    $this->connections[$dsn] = &$dmo;
                
    //$this->connections[$dsn] = &$c;
            
    }
    showdata $this );
            return 
    $this->connections[$dsn];
        }

        
    //CleanUp
        //DB::Load('zest')->closeConnections();
        
    public function closeConnections() {
            foreach(
    $this->resources as $key => $val) {
                
    $val->DisConnect();
            }
        }
    }
    I call it using something like this:-
    $result = DB::Load()->Execute("SELECT * FROM mytable;")->returnArray(); // MySQL

    or

    $result = DB::Load("mssql_label")->Execute("SELECT * FROM mytable;")->returnArray(); // MS SQL

    Now, individually, they both work but if I call one after the other, only the first called one works.
    eg:

    PHP Code:
    // Shows me the result of the MY SQL query
    $result DB::Load()->Execute("SELECT * FROM mytable;")->returnArray();
    $result DB::Load("mssql_label")->Execute("SELECT * FROM mytable;")->returnArray(); 
    PHP Code:
    // Shows me the result of the MS SQL query
    $result DB::Load("mssql_label")->Execute("SELECT * FROM mytable;")->returnArray();
    $result DB::Load()->Execute("SELECT * FROM mytable;")->returnArray(); 
    I've seen that there is a "closeConnections" action, called using DB::Load()->closeConnections;, but even if sticking that in between them doesn't help matters and I still only get data from the first-called one.

    Now, I've posted here instead of e-pestering Northie individually as I'm certain that you experts out there will be able to help me understand what's going on!
    (Plus, I think others may be using his DB.php so may have discovered/fixed this or are still to encounter this!)

    Cheers for reading, hope someone can help!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  2. #2
  3. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Hi guys
    Since my post above I have made some changes and seen a little bit of progress ...

    1. Changed the DB class __construct function to PUBLIC and added some debugging:
    PHP Code:
    class DB {

        private static 
    $instance;
        private 
    $settings = array();
        private 
    $connections = array();

        public function 
    __construct($db) {

    echo 
    __FUNCTION__ " : DB: " $db "\n";

            switch ( 
    $db ) {
                default:
                    
    $this->settings[$db]['host'] = 'mysql_host';
                    
    $this->settings[$db]['user'] = 'mysql_user';
                    
    $this->settings[$db]['pass'] = 'mysql_pass';
                    
    $this->settings[$db]['name'] = 'mysql_database';
                    
    $this->settings[$db]['type'] = 'mysql';
                break;
            case 
    "mssql_label":
                    
    $this->settings[$db]['host'] = 'mssql_host';
                    
    $this->settings[$db]['user'] = 'mssql_user';
                    
    $this->settings[$db]['pass'] = 'mssql_password';
                    
    $this->settings[$db]['name'] = 'mssql_database';
                    
    $this->settings[$db]['type'] = 'sqlsrv';
                break;
            }

    echo 
    "SETTINGS: " print_r $this -> settings1) . "\n";

        }

        
    /**
         * $rs = DB::Load('zest')->Execute($sql,$args)->returnArray();
         */

        
    public static function Load($db=false) {
            if(!isset(
    self::$instance)) {
                
    $c __CLASS__;
                
    self::$instance = new $c($db);
            }

    echo 
    __FUNCTION__ " : DB: " $db "\n";

            return 
    self::$instance->getConnection($db);
        }
            
        public function 
    getConnection($dsn) {
        
            
    //wrap in try-catch block?
            
            
    if(!isset($this->connections[$dsn])) {

    echo 
    __FUNCTION__ " : "  print_r $this->settings) . "\n";
    echo 
    __FUNCTION__ " : DSN : "  print_r $dsn) . "\n";
                
                switch(
    strtoupper($this->settings[$dsn]['type'])) {
                    case 
    'MYSQL':
                        
    //create new connection object, and pass in credentials
                        
    $c = new PDOMySQL(
                            
    $this->settings[$dsn]['host'],
                            
    $this->settings[$dsn]['name'],
                            
    $this->settings[$dsn]['user'],
                            
    $this->settings[$dsn]['pass']
                        );
                        break;
                    case 
    'SQLSRV':
                        
    $c = new PDOMsSQL(
                            
    $this->settings[$dsn]['host'],
                            
    $this->settings[$dsn]['name'],
                            
    $this->settings[$dsn]['user'],
                            
    $this->settings[$dsn]['pass']
                        );
                        break;
                }

                
    //connect, $link passed by reference so is 'returned' with the db link in it
                
    $link $c->Connect();

                
    //create new DMO
                
    $dmo = new DefaultDMO;

                
    //pass link to DMO
                
    $dmo->setConnection($link);

                
    //keep track of link
                
    $this->resources[$dsn] = &$c;

                
    //keep track of DMO
                
    $this->connections[$dsn] = &$dmo;
                
    //$this->connections[$dsn] = &$c;
            
    }
            return 
    $this->connections[$dsn];
        }

        
    //CleanUp
        //DB::Load('zest')->closeConnections();
        
    public static function closeConnections() {
            foreach(
    $this->resources as $key => $val) {
                
    $val->DisConnect();
            }
        }


    2. Create the object with this ...

    $my = new DB("");
    $ms = new DB("mssql_label");

    ... and get some results with:-
    $result = $my -> Load()->Execute("SHOW TABLES;")->returnArray();
    $result = $ms -> Load("mssql_label")->Execute("SELECT * FROM mytable;")->returnArray();



    Ok, so still am only getting the 'first' resultset but I shoved some debugging/output to screen in the db.php and I see the following:-

    PHP Code:
    $my = new DB("");
    $result $my -> Load()->Execute("SHOW TABLES;")->returnArray();
    echo 
    ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>\n";
    $ms = new DB("mssql_label");
    $result $ms -> Load("mssql_label")->Execute("SELECT * FROM mytable;")->returnArray(); 
    Code:
    __construct : DB:
    SETTINGS: Array
    (
        [] => Array
            (
                [host] => mysql_server
                [user] => mysql_user
                [pass] => mysql_password
                [name] => mysql_database
                [type] => mysql
            )
    
    )
    
    __construct : DB:
    SETTINGS: Array
    (
        [0] => Array
            (
                [host] => mysql_server
                [user] => mysql_user
                [pass] => mysql_password
                [name] => mysql_database
                [type] => mysql
            )
    
    )
    
    Load : DB:
    getConnection : Array
    (
        [0] => Array
            (
                [host] => mysql_server
                [user] => mysql_user
                [pass] => mysql_password
                [name] => mysql_database
                [type] => mysql
            )
    
    )
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    __construct : DB: mssql_label
    SETTINGS: Array
    (
        [mssql_label] => Array
            (
                [host] => mssql_host
                [user] => mssql_user
                [pass] => mssql_password
                [name] => mssql_database
                [type] => sqlsrv
            )
    
    )
    
    Load : DB: mssql_label
    getConnection : Array
    (
        [0] => Array
            (
                [host] => mysql_host
                [user] => mysql_user
                [pass] => mysql_password
                [name] => mysql_database
                [type] => mysql
            )
    
    )
    
    getConnection : DSN : mssql_label
    PHP Notice:  Undefined index: mssql_label in db.php on line 350
    PHP Notice:  Undefined variable: c in db.php on line 371
    PHP Fatal error:  Call to a member function Connect() on a non-object in db.php on line 371
    So somewhere along the lines it is losing the $dsn variable/value and sending the wrong parameters into the SWITCH statement (or, at least, I THINK that is what is happening!) and because of the inability to match $dsn to one of the two values (MYSQL or SQLSRV), it's falling over.

    Like I said, I only *THINK* that is what's happening.
    Now, I don't want someone to provide me with the fix but if someone can see what mistake I am making and give me a hint, that would be great - I know there's some total wizards out there so I hope one of them reads this!!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Hi,

    I'm sorry to say this, and I don't wanna hurt anybody's feelings. But this code is a mess. It's like the author couldn't really decide what the DB class is supposed to do. On the one hand, it pretends to be a database connection manager which can hold multiple instances of itself, each representing a connection. On the other hand, it's written like a singleton to be only instanciated once. There's also a lot of confusion about when to use the class and when to use an instance.

    In your case, this is what happens:

    1. You create an instance of the DB class and pass a string to the constructor. By the way, this string is completely pointless. It isn't used anywhere. The instance itself is also more or less useless. Its only role is to provide access to the methods.
    2. You call the Load() method and again pass a string to it. This string is relevant. A new instance of DB is created with certain connection options (which depend on the string). This instance is also stored in the class.
    3. You create another instance of DB for the MSSQL database. This is actually pointless. You might as well reuse the instance from above, because the settings from the constructor aren't used.
    4. You again call Load(). But this won't create yet another instance for an MSSQL connection but rather use the existing MySQL instance stored in the class. So you end up with the old settings. Yet still the old instance will be searched for the new settings, so the code finally blows up.

    The classes are also very inefficient and filled with lots of duplicate code and useless and insecure try-catch blocks.

    I don't think the code can be fixed without rewriting most of it. So I suggest you throw it away and use plain PDO instead. To be honest, I don't really see the point of the code, anyway. The features are mostly trivial and might as well be done with PDO itself.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Jacques1
    Thank you for the reply; I really appreciate you looking over this for me and your feedback. I have used this as an include for some time so am familiar with the useage but if the way forward is to drop it and start over then so be it.

    As I said, I'm not after someone writing the code for me but if you've any links to decent tutorials that would really help (I'll search google of course but I prefer recommendations if possible!)

    Cheers!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  8. #5
  9. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Ok, here is my first attempt!
    (Please be gentle!)

    PHP Code:
    <?php

    define 
    ("DEBUG_DATABASE"1);
    date_default_timezone_set("Europe/London");

    function 
    logthis $message NULL ) { if ( $message ) echo date("d-m-Y H:i:s") . " : " $message "\n"; }

    /*
     * mkcon : Function to establish connection to a database
     * @param    server    string        The sever to connect to
     * @return    db            The DB connection object
     */
    function mkcon $servername ) {
        if ( 
    DEBUG_DATABASE logthis __FUNCTION__ " : " $servername );
        switch ( 
    $servername ) {
            case 
    "mssql_server":
                
    $dbname 'sqldb';
                
    $dbuser 'sqluser';
                
    $dbpass 'sqlpassword';
                
    $dbtype 'sqlsrv';
                break;
            default:
                
    $dbname 'mysqldb';
                
    $dbuser 'mysqluser';
                
    $dbpass 'mysqlpassword';
                
    $dbtype 'mysql';
                break;
        }

        try {
            switch ( 
    strtolower $dbtype ) ) {
                case 
    "mysql":
                    
    $db = new PDO('mysql:host=' $servername ';dbname=' $dbname$dbuser$dbpass);
                    break;
                case 
    "sqlsrv":
                    
    $db = new PDO('dblib:host=' $servername ';dbname=' $dbname$dbuser$dbpass);
                    break;
            }
        } catch ( 
    Exception $e ) {
            if ( 
    DEBUG_DATABASE logthis __FUNCTION__ " This error happened : " $e );
        }
        return 
    $db;
    }

    /*
     * DBLoad : Function to send a query to the selected server and return the resultset as an array
     * @param    sql        string        Valid SQL
     * @param    args        array        An array of matching arguments
     * @param    servername    string        The server to connect to
     */
    function DBLoad $sql$args NULL$servername NULL ) {
        if ( !
    $sql || $sql == "" ) { return "SQL was not sent"; }
        
    $servername = ( !$servername ) ? "localhost" $servername;

        if ( 
    DEBUG_DATABASE logthis __FUNCTION__ " Servername : " $servername " ~ Query : " $sql );
        
    // Create the connection to the DB server
        
    $db mkcon $servername );

        if ( 
    DEBUG_DATABASE logthis __FUNCTION__ " DB : " print_r $db) );

        if ( 
    $args ) {
            
    $query $db -> prepare $sql );
            
    $query -> execute $args );
        } else {
            
    $query $db -> query $sql );        
        }
        
    $result $query -> fetchAll PDO::FETCH_ASSOC );

        return 
    $result;
    }

    $sql "SELECT * FROM myTable WHERE id = :val1";
    $args = array ( ":val1" => 1);
    $result DBLoad$sql$args );
    if ( 
    DEBUG_DATABASE logthis " RES : " print_r $result) );

    $sql "SELECT * FROM myTable";
    $result DBLoad$sql );
    if ( 
    DEBUG_DATABASE logthis " RES : " print_r $result) );

    $result DBLoad"SELECT * FROM myOtherTable;""" ,"mssql_server");
    if ( 
    DEBUG_DATABASE logthis " RES : " print_r $result) );

    ?>
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    This will establish a new database connection for every single query, so you'll quickly exceed the connection limit. And of course this creates a lot of unnecessary stress for both the application and the database server.

    My question would be: Why make things so complicated? This looks like a damn lot of code for a relatively trivial task: Establishing two database connections and sending queries to them.

    Why not simply create two PDO instances?

    PHP Code:
    <?php

    $mysql_db 
    = new PDO(...);
    $mssql_db = new PDO(...);
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Well, the function that creates the actual PDO object, I appreciate may be a little overkill but for the app I am writing, the data it needs is all over the place, different servers and different DB types (well, MySQL and MS SQL anyway) so I will re-write mkcon now.

    As for connections, the app includes this file in the "header" and kills the connection in the "footer" ...

    Of course, am open to further suggestions and critique!
    Last edited by badger_fruit; November 22nd, 2013 at 06:13 AM. Reason: typos
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    I still don't get what you're trying to do.

    What's the problem you wanna solve with your code? Do you wanna be able to get a database connection through a function call so that you don't have to pass objects around? Do you want a different API for queries?

    I think you should first define the concrete goal. Then we can help you implement a solution.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Oh yeah, the goal is to create a function that someone can call which will establish a connection to the DB server of choice, perform the query and return the results as an array.

    Previously with my DB::Load, I'd use $result = DB::Load()->Execute( $sql, $anyargs ) -> returnArray(); and that was nice and simple, anyone could come along, copy/paste/edit and get a result set.

    Of course, then came along the manager "I need to get that app to talk to MySQL and MS Sql" so I attempted to modify the DB::Load to my needs and failed lol (hence the OP).

    It never even occurred to me to re-write which is what I've now done (as seen above) and another developer can come along and use $result = DBLoad ( $sql, $anyargs ); to get a result set in the same format as previously.

    As far as I am concerned, the problem is solved but since you mentioned there may be an even simpler way to go.

    My final solution is this by the way, okay, it may not be so pretty but it's pretty neat, compact and does what we need it to do!

    PHP Code:
    <?php

    define 
    ("DEBUG_DATABASE"0);
    define ("DEBUG_LOGFILE""/tmp/" basename __FILE__ ) . ".log" );

    /*
     * mkcon : Function to establish connection to a database
     * @param    server    string        The sever to connect to
     * @return    db            The DB connection object
     */
    function mkcon $servername ) {
        if ( 
    DEBUG_DATABASE logthis DEBUG_LOGFILE__FUNCTION__ " : Connecting to : " $servername );
        switch ( 
    $servername ) {
            case 
    "hydrogen":
                
    $dbname 'dbname';
                
    $dbuser 'dbuser';
                
    $dbpass 'dbpass';
                
    $dbtype 'sqlsrv';
                
    $db = new PDO('dblib:host=' $servername ';dbname=' $dbname$dbuser$dbpass);
                break;
            case 
    "localhost":
                
    $dbname 'dbname';
                
    $dbuser 'dbuser';
                
    $dbpass 'dbpassword';
                
    $dbtype 'mysql';
                
    $db = new PDO('mysql:host=' $servername ';dbname=' $dbname$dbuser$dbpass);
                break;
            default:
                if ( 
    DEBUG_DATABASE logthis DEBUG_LOGFILE__FUNCTION__ " : Unknown server, unable to connect");
                die;
        }
        return 
    $db;
    }

    /*
     * DBLoad : Function to send a query to the selected server and return the resultset as an array
     * @param    sql        string        Valid SQL
     * @param    args        array        An array of matching arguments
     * @param    servername    string        The server to connect to
     */
    function DBLoad $sql$args NULL$servername NULL ) {
        if ( !
    $sql || $sql == "" ) { return "SQL was not sent"; }
        
    $servername = ( !$servername ) ? "localhost" $servername;

        
    // Create the connection to the DB server
        
    $db mkcon $servername );
        if ( 
    $args ) {
            
    $query $db -> prepare $sql );
            
    $query -> execute $args );
        } else {
            
    $query $db -> query $sql );        
        }
        
    $result $query -> fetchAll PDO::FETCH_ASSOC );
        
    $db NULL;

        if ( 
    DEBUG_DATABASE logthis DEBUG_LOGFILE__FUNCTION__ " : Servername : " $servername );
        if ( 
    DEBUG_DATABASE logthis DEBUG_LOGFILE__FUNCTION__ " : Query      : " $sql );
        if ( 
    DEBUG_DATABASE logthis DEBUG_LOGFILE__FUNCTION__ " : Result     : " print_r $result) );

        return 
    $result;
    }
    ?>
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    This doesn't work. You're still creating a new connection for every single query. If your application has 100 queries, you establish 100 connections -- for a single user. If you have 100 users at a time, that's 10,000 concurrent database connections. The database system will simply blow up.

    What you're trying to do isn't quite that trivial. Functions in particular aren't well-suited for caching values. While this is theoretically possible with the static keyword, it's very weird and confusing.

    I understand you wanna get this done as quickly as possible, but I strongly recommend you take some time to design a proper class. I can write down my own suggestion in a few hours.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Thank you, that's appreciated, it'd be great to see how an expert would work this!!

    Just one thing though:

    Originally Posted by Jacques1
    You're still creating a new connection for every single query. If your application has 100 queries, you establish 100 connections
    .. even though there's the $db = NULL; statement in there?
    Would you suggest that I create the connection at the start of the script instead?

    Consider me slapped on the wrist for 'cheating' with the pre-written original script and settling on that instead of learning this ages ago!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Originally Posted by badger_fruit
    .. even though there's the $db = NULL; statement in there?
    This does not close the connection. It merely tells PHP to destroy the object at the next opportunity. As far as I know, the PDO class doesn't have a method for immediately closing a connection.

    Either way, creating new connections over and over again is not an option. You need to store the connection so that you can reuse it -- just like you do it in classical PHP code.

    Like I already said above, you cannot really do this with functions. It's not impossible, but it requires either the exotic static keyword or a global variable (ugh).

    This asks for an object. The approach is actually pretty simple: You create a class with a static attribute holding an array. This array is used as a cache for database connections. Whenever a certain connection is requested, you check if it's already in the cache. If it is, you use it. Otherwise, you establish a new connection and store it in the cache.

    That's also what the class above was supposed to do (but it didn't work out).

    As an example class for holding multiple connections:

    PHP Code:
    <?php


    class DBConnection
    {

        protected static 
    $connection_definitions;
        protected static 
    $connections;

        protected 
    $connection;

        public static function 
    addParameters($connection_definitions)
        {
            foreach (
    $connection_definitions as $id => $parameters)
            {
                if (isset(
    self::$connection_definitions[$id]))
                    throw new 
    InvalidArgumentException('Connection ID ' $id ' already taken.');

                
    self::$connection_definitions[$id] = $parameters;
            }
        }

        public static function 
    load($id)
        {
            if (!isset(
    self::$connection_definitions[$id]))
                throw new 
    InvalidArgumentException('Unknown connection ID ' $id);

            if (!isset(
    self::$connections[$id]))
            {
                
    $parameters self::$connection_definitions[$id];

                
    $dsn $parameters['dsn'];
                
    $user = isset($parameters['user']) ? $parameters['user'] : null;
                
    $password = isset($parameters['password']) ? $parameters['password'] : null;
                
    $options = isset($parameters['options']) ? $parameters['options'] : null;

                
    self::$connections[$id] = new self($dsn$user$password$options);
            }

            return 
    self::$connections[$id];
        }

        protected function 
    __construct($dsn$username null$password null$options null)
        {
            
    // define proper default values
            
    $options_defaults = array(
                
    PDO::ATTR_EMULATE_PREPARES => false
                
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            
    );

            
    $effective_options $options_defaults;
            if (
    $options)
                
    $effective_options $options $options_defaults;

            
    $this->connection = new PDO($dsn$username$password$effective_options);
        }

        public function 
    query($sql$parameters null)
        {
            
    $statement null;

            if (
    $parameters)
            {
                
    $statement $this->connection->prepare($sql);
                
    $statement->execute($parameters);
            }
            else
                
    $statement $this->connection->query($sql);

            return 
    $statement;
        }

    }
    It's then used like this:

    PHP Code:
    // set connection parameters
    DBConnection::addParameters(array(
        
    'foo_connection' => array(
            
    'dsn' => 'mysql:host=localhost;dbname=DB;charset=utf8'
            
    'user' => 'USER'
            
    'password' => 'PASS'
        
    )
        , 
    'bar_connection' => array(
            
    'dsn' => 'mysql:host=localhost;dbname=DB;charset=utf8'
            
    'user' => 'USER'
            
    'password' => 'PASS'
        
    )
    ));

    // load connection by ID
    $foo_connection DBConnection::load('foo_connection');

    // perform query
    $answer_query '
        SELECT
            :num AS the_answer_to_everything
    '
    ;
    $query $foo_connection->query($answer_query, array(
        
    'num' => 42
    ));

    // fetch result
    $answer $query->fetchColumn();
    echo 
    htmlspecialchars('The answer to everything is ' $answer '.'ENT_QUOTES'UTF-8'); 
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  24. #13
  25. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,047
    Rep Power
    487
    Ah, that's great, a very nice class and useage example, thank you.
    I suppose if I really wanted to (or needed to), I could functionalise this a little bit (as it is, there's a certain amount of code to re-write to use this suggestion already) with something like:

    PHP Code:
    function getdata $sql$args NULL$dsn "foo_connection" ) {

      
    // load connection by ID (assuming that they're defined already somewhere else)
      
    $foo_connection DBConnection::load($dsn); 

      
    // perform query 
      
    $answer_query $sql
      
    $query $foo_connection->query($answer_query$args); 

      
    // fetch result 
      
    $answer $query->fetchArray(); 
      return 
    $answer;

    And call this with something like:

    $sql = "SELECT COUNT(*) FROM myTable WHERE column= :val1";
    $args = array (":val1" => "badger_fruit");
    $result = getdata ( $sql, $args );

    or if I wanted to use bar_connection, simply add an argument to the getdata call
    $result = getdata ( $sql, $args, 'bar_connection' );

    Sorry, I don't mean to sound obsessed with functions, but as others may be reading the code one day then I'd like to keep it as simple as I can for them (and me lol!) - plus, as I'm using my current DBLoad function, I don't need to actually change any of that code

    ps, I presume that I would only call $foo_connection = DBConnection::load('foo_connection'); once per script (or perhaps have it in an included file such as the actual db.php which defines the class)?

    pps. There was another unrelated question I had, may be a quick answer so not deserved of it's own thread but why do some coders use ' and others " ?
    e.g.

    $result = getdata ( $sql, $args, 'bar_connection' ); vs $result = getdata ( $sql, $args, "bar_connection" );
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984

IMN logo majestic logo threadwatch logo seochat tools logo