Page 1 of 2
Creating a common database class/methods
Posted: Wed May 13, 2009 8:32 am
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.
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 8:58 am
by Defiline
Nobody can answer untill you will show the class (code).
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 9:43 am
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?
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 11:51 am
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!
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 2:14 pm
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.
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 2:23 pm
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?
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 2:25 pm
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:
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:
Posting Code in the Forums to learn how to do it too.
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 5:14 pm
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
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 5:35 pm
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.
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 5:50 pm
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.
Re: Creating a common database class/methods
Posted: Wed May 13, 2009 8:05 pm
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?
Re: Creating a common database class/methods
Posted: Thu May 14, 2009 8:13 am
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...
Re: Creating a common database class/methods
Posted: Thu May 14, 2009 9:36 am
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.
Re: Creating a common database class/methods
Posted: Thu May 14, 2009 9:38 am
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!
Re: Creating a common database class/methods
Posted: Thu May 14, 2009 9:41 am
by pickle
That assumes you're perfect & never make a security mistake yourself
