Creating a common database class/methods

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

jlehmer
Forum Newbie
Posts: 4
Joined: Wed May 13, 2009 8:22 am

Creating a common database class/methods

Post by jlehmer »

I'm trying to create a common Database class that will encapsulate as much PDO interaction that I can. At the very least it will allow me to remove redundant connection code from all my PHP files.

My main PHP script will create a new instance of "Database" and the object will automatically connect (using PDO) to MySql. The connection explicitly lists the database name to use for the connection. The main script will then invoke a method on the Database class to create a prepared statement using the same connection and returns the PDOStatement object.

The problem I'm having is whenever I try and execute the prepared statement I get a "No database selected" error. If I move all the PDO code into the main PHP script it works fine.

Any ideas what I might be doing wrong here? I can post the code if need be.

Thanks in advance.
Defiline
Forum Commoner
Posts: 59
Joined: Tue May 05, 2009 5:34 pm

Re: Creating a common database class/methods

Post by Defiline »

Nobody can answer untill you will show the class (code).
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Creating a common database class/methods

Post by pickle »

I'm wondering why you're bothering to build a wrapper around the PDO wrapper? Why not just use the mysql(i) calls directly?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Creating a common database class/methods

Post by crazycoders »

Indeed, that is what i was about to say... PDO makes all calls to different database the same (at least for the operations not for the SQL strings)... if you plan on only using MySQL database, it's useless to use PDO. PDO is used to abstract the differences in the API for each db provider...

Yet again, it doesn't help you with language differences!
jlehmer
Forum Newbie
Posts: 4
Joined: Wed May 13, 2009 8:22 am

Re: Creating a common database class/methods

Post by jlehmer »

Let me preface this thread: I have a lot of experience with OO and Java so I'm trying to apply the same concepts in PHP. Specifically, I'm still working out the most appropriate way to handle database connections in a PHP web application. From what I understand there is no concept of "connection pooling" in PHP so...

My thought was using PDO would at least give me the flexibility to move to a different database later. The other objective is to prevent each piece of functionality from having to know the details of logging into the database. In hind sight it's probably not that big a deal considering it's only one line of code to do "new PDO" (or a couple more if I use try/catch).

That said it still doesn't make sense to me why this doesn't work. I must be missing a key PHP concept here.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Creating a common database class/methods

Post by pickle »

"Why it doesn't work" has two meanings in this context.

To answer your original question, it probably isn't working because your class doesn't have access to a particular connection string it needs. Make sure your connection class has access to everything it needs. Posting code on this would really be helpful.

As for why it doesn't work in theory - it actually does. Technically you can continue doing things this way - it's just more work than necessary. Imagine you've got a spoken language that you've converted into musical notes, so that each note means a different word. You can then apply those notes to a different spoken language - all you need to know is the meaning behind the musical notes. PDO is to MySQL like the notes are to the spoken language. What you're doing now, though, is similar to converting those musical notes into numbers. Your layer is to MySQL like numbers are to spoken words. Why bother converting the meaning of the words into musical notes, then numbers - why not just convert once. In other words, why have two layers of abstraction that do pretty much the same thing?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Creating a common database class/methods

Post by crazycoders »

pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Heh sorry about that man

Includes!

Thats how most people centralize database configuration and access. For example, i use a folder called includes at the root of my website and put in it different files such as:

db.php

Code: Select all

 
//Configure some of hostbased variables (dev/prod/uat)
if($_SERVER['HTTP_HOST'] == 'some.domain.name'){
//Specific settings for this hostname configuration
}else{
//Specific settings for this hostname configuration
}
 
//Load the database
$db = mysql_connect($hostname, $username, $password);
mysql_select_db($dbname,$db);
register_shutdown_function('mysql_close');
 
So in the include file, i check for the hostname if i use a DEV/UAT/PROD environment and define in there my connection info. Then i load up the database connection directly in the include and register a shutdown function so that it gets cleaned up at anytime i would be closing the process.

Is this more like what you were searching for?


pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
jlehmer
Forum Newbie
Posts: 4
Joined: Wed May 13, 2009 8:22 am

Re: Creating a common database class/methods

Post by jlehmer »

Ok so here's my "login" code, as you can see I've tried using a class as well as a straight function:

Code: Select all

<?php
    require_once '../../includes/database_connection.class.php';
    require_once '../../includes/database.php';
    
    $username=$_POST['name'];
    $password=crypt(md5($_POST['pword']));
    
    
    //$dbConn = new DatabaseConnection();
    $dbConn = connectToDb();
        
    //$stmt = $dbConn->prepareStmt("SELECT * FROM user WHERE login_id = :login_id");
    $stmt = $dbConn->prepare("SELECT * FROM user WHERE login_id = :login_id");
    
    /*** bind the paramaters ***/
    $stmt->bindParam(':login_id', $username, PDO::PARAM_STR, 50);
        
    if ($stmt->execute()){
        echo 'success';
    }
    else {
        echo "\nPDOStatement::errorInfo():\n";
        $arr = $stmt->errorInfo();
        print_r($arr);
    }
    
    $resultStmt = $stmt->fetchAll();
    
    echo $resultStmt[0]['login_id'];
    
    echo count($resultStmt);
    
    $dbConn = NULL;
 
?>
Here is my class code:

Code: Select all

<?php
 
include_once 'config.php';
 
class DatabaseConnection {
    
    private $dbh;
    
    public function __construct() {
        $this->dbh = $this->getDBConnection(); 
    }
    
    private function getDBConnection(){
        try {
            $dbConn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
        } catch (PDOException $e){
             echo $e->getMessage();
        }
        return $dbConn;
    }
    
    public function executeSql($sql){
        $resultSet = $this->dbh->query($sql);
        return $resultSet;
    }
    
    public function prepareStmt($stmt){
        return $this->dbh->prepare($stmt);
    }
    
    public function closeDBConnection() {
        $this->dbh = null;
    }
    
    public function __destruct() {
        if (isset($this->dbh)) {
            $this->closeDBConnection();
        }
    }
}
?>
Here is my plain function:

Code: Select all

<?php
include_once 'config.php';
 
function connectToDb(){
    $dbConn = new PDO("mysql:dbname=$dbname;host=$dbhost", $dbuser, $dbpass);
    return ($dbConn);
}
?>
Thanks for the snippet of code you provided, I'll give it a try. For what it's worth I've steered away from using the native mysql functions based on the following article http://www.ibm.com/developerworks/libra ... dbmistake/. Let me know what you think
Last edited by Benjamin on Wed May 13, 2009 5:25 pm, edited 1 time in total.
Reason: Changed code type from text to php.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Creating a common database class/methods

Post by pickle »

$dbname and $dbhost aren't defined anywhere - there's your problem.

There's absolutely nothing wrong with using the mysql* or mysqli* functions. They exist so you should be able to call them. The PEAR module that article speaks of does just that. The article was referring more to how those functions are used.

What the article meant was that you shouldn't sprinkle your code with direct mysql* function calls, because that'll be hell if you ever want to move to pgsql, sqlite, or something else. That's what a database abstraction layer such as PDO, the PEAR module, or any other DBA layer is for - so you can call: DB::connect() (for example) and have your abstraction layer worry about how to connect. Then, if you change databases, you only need to change your DBA layer class file, rather than hunt through all your source code, looking for mysql_connect(), mysql_query(), mysql_fetch_assoc(), etc.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Creating a common database class/methods

Post by Eran »

You should have a look at open-source packages for database abstraction, such as Zend_Db. There are some really good ones out there and they can give you a good lead on how to create yours.
jlehmer
Forum Newbie
Posts: 4
Joined: Wed May 13, 2009 8:22 am

Re: Creating a common database class/methods

Post by jlehmer »

All of the db properties are in the config.php include file. I was just playing around and noticed that if I remove the "function wrapper" around my original code (see snippet) it works fine.

Code: Select all

  1. <?php
   2. include_once 'config.php';
   5.     $dbConn = new PDO("mysql:dbname=$dbname;host=$dbhost", $dbuser, $dbpass);
   8. ?>
Does anyone know why passing a reference to the PDO object back as a result of the function would cause this behavior?
Last edited by Benjamin on Wed May 13, 2009 10:00 pm, edited 1 time in total.
Reason: Changed code type from text to php.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Creating a common database class/methods

Post by crazycoders »

Oh, i just saw something in your code, try never to use require_once or include_once, instead mechanisms such as:

Code: Select all

 
<?php if(!defined('MYFILEPHP')){
   define('MYFILEPHP', 1);
 
...Place code here that declares the content of your file...
 
}
 
It is 10x faster if i remember to use include or require and use a defined/define pattern than using include_once or require_once! Google it to make sure...
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Creating a common database class/methods

Post by pickle »

If you put the connection call in a function, it gets put in a different namespace. Variables defined outside a function are not available inside a function because they're in a different namespace. Exceptions are globals, super globals, and constants. If you want those connection variables available in the function, I suggest you include() a config file that defines those connection strings, inside your connection function.

Declaring those variables as constants is dangerous because they are then available to all parts of your app, which may present a security risk.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Creating a common database class/methods

Post by crazycoders »

It presents a security risk only if the application accepts third party code or external pluggins or stuff like that. If the site stays closed, it is not a problem!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Creating a common database class/methods

Post by pickle »

That assumes you're perfect & never make a security mistake yourself ;)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply