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.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Feb 2006
    Location
    Texas
    Posts
    80
    Thanks
    1
    Thanked 8 Times in 8 Posts

    Simple MySQL PDO Wrapper

    I wrote this for a project I've been working on to help simplify database queries and keep the code clean. Also read that binding the parameters in your queries is great protection against injection hacks.

    First, code examples

    INSERT/UPDATE

    PHP Code:
    $db = new Database();
    $data = array( "John""Smith" );
    $sql "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
    $db->insert$data$sql ); 
    Selecting a single row
    PHP Code:
    $data = array( 102 );
    $sql "SELECT * FROM names WHERE id = ? LIMIT 1";
    $obj $db->fetch_single_row$data$sql );
    echo 
    "{$obj->firstname} {$obj->lastname}"
    Selecting Multiple Rows
    PHP Code:
    $data = array( 'M'27 );
    $sql "SELECT * FROM names WHERE sex = ? AND age = ?";
    $result $db->fetch_multiple_rows$data$sql );

    while( 
    $row $result->fetch() ) {
        echo 
    "{$row->firstname} {$row->lastname}\n<br/>";

    And finally the class
    PHP Code:
    class Database {
        private 
    $host;
        private 
    $username;
        private 
    $password;
        private 
    $con;
            private 
    $pdo;
        
        public function 
    __construct$database "yourdb" )
        {
            
    $this->host "localhost";
            
    $this->username $_SERVER['DB_USER'];
            
    $this->password $_SERVER['DB_PASSWORD'];
                    
    $conStr "host={$this->host};dbname={$database}";
                    try {
                        
    $this->pdo = new PDO"mysql:$conStr"$this->username$this->password );
                        
    $this->pdo->setAttributePDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION );
                    }
                    catch( 
    PDOException $e ) {
                        echo 
    "error "$e->getMessage();
                    }
        }
            
        public function 
    fetch_single_row$data$sql )
        {
            if( 
    $data !== null )
                
    $data array_values$data ); // Incase it's an associative array
            
    $sel $this->pdo->prepare$sql );
            
    $sel->execute$data );
            
    $sel->setFetchModePDO::FETCH_OBJ );
            
    $obj $sel->fetch();
            return 
    $obj;
        }
        
        public function 
    fetch_multiple_rows$data$sql )
        {
            if( 
    $data !== null )
                
    $data array_values$data ); // Incase it's an associative array
            
    $sel $this->pdo->prepare$sql );
            
    $sel->execute$data );
            
    $sel->setFetchModePDO::FETCH_OBJ );
            return 
    $sel;
        }
        
        public function 
    insert$data$sql )
        {
            if( 
    $data !== null )
                
    $data array_values$data ); // Incase it's an associative array
            
    $ins $this->pdo->prepare$sql );
            
    $ins->execute$data );
        }

        public function 
    close()
        {
            
    $this->pdo null;
        }
            
        public function 
    __destruct()
        {
            
    $this->pdo null;
        }

    Last edited by Truffle; 11-17-2011 at 06:08 PM.

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    In this example, since you are having to write out the entire INSERT query, it would make more send to turn rename the insert() method query():
    PHP Code:
    $data = array( "John""Smith" );
    $sql "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
    $db->query$data$sql ); 
    It would also seem more intuitive to have the query as the first parameter, and the array of values as an optional second parameter.

  • #3
    New Coder
    Join Date
    Feb 2006
    Location
    Texas
    Posts
    80
    Thanks
    1
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by kbluhm View Post
    In this example, since you are having to write out the entire INSERT query, it would make more send to turn rename the insert() method query():
    PHP Code:
    $data = array( "John""Smith" );
    $sql "INSERT INTO names ( firstname, lastname) VALUES( ?, ? )";
    $db->query$data$sql ); 
    It would also seem more intuitive to have the query as the first parameter, and the array of values as an optional second parameter.
    Yeah you're right. I actually us $db->insert() when doing any query that doesn't require a return so it's more of a generic function than just insert

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,243
    Thanks
    12
    Thanked 340 Times in 336 Posts
    you can simplify your class by using Dependency Injection:
    PHP Code:
    class Database {

        private 
    $pdo;
        
        public function 
    __constructPDO $database )
        {
            
    $this->pdo $database;
        
    //  $this->pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        

    though you would have to define the PDO object outside the Database class, it now works with every database supported by PDO.

    another problem the class currently has (may have) is that it only supports string type data (the default data type for lazy bind)

    the other thing is that try … catch inside the constructor doesn’t make sense. firstly you shouldn’t print from a constructor. secondly, if your connection fails, the object is still created and will throw further errors if you try one of the methods ($this->pdo is not an object). use try … catch in your script where you can (programmatically) fix the problem (even if that means to skip the whole database section).
    Last edited by Dormilich; 11-18-2011 at 09:33 AM.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer


  •  

    Posting Permissions

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