I have a few classes that perform some MySQL queries and prepared statements. However, I am lost in how to incorporate my PDO object within those classes. For example, I want to do something like this:
<?php
$dbh = new PDO(...);
class Foo extends PDO {
public $dbh;
public function bar() {
$this->dbh->prepare('SELECT * FROM table');
$this->dbh->execute();
}
}
?>
Unfortunately, it doesn't work. Can anyone suggest an elegant way to do this? Thanks for your time. Sorry I'm new to this, please leave any comments if you are unclear about anything and I'll do my best to respond!
The PDO class ¶ Represents a connection between PHP and a database server.
Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.
Should I Use MySQLi or PDO? If you need a short answer, it would be "Whatever you like". Both MySQLi and PDO have their advantages: PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.
You can instantiate your connection to the database in a class that implement the singleton pattern. The connection will be done once and this class will be easily accessible by all of your other objects / scripts.
i use a class called "Core" in the following example;
class Core
{
public $dbh; // handle of the db connexion
private static $instance;
private function __construct()
{
// building data source name from config
$dsn = 'pgsql:host=' . Config::read('db.host') .
';dbname=' . Config::read('db.basename') .
';port=' . Config::read('db.port') .
';connect_timeout=15';
// getting DB user from config
$user = Config::read('db.user');
// getting DB password from config
$password = Config::read('db.password');
$this->dbh = new PDO($dsn, $user, $password);
}
public static function getInstance()
{
if (!isset(self::$instance))
{
$object = __CLASS__;
self::$instance = new $object;
}
return self::$instance;
}
// others global functions
}
this class take parameters from a static class called "Config" where you can store your configuration:
<?php
class Config
{
static $confArray;
public static function read($name)
{
return self::$confArray[$name];
}
public static function write($name, $value)
{
self::$confArray[$name] = $value;
}
}
// db
Config::write('db.host', '127.0.0.1');
Config::write('db.port', '5432');
Config::write('db.basename', 'mydb');
Config::write('db.user', 'myuser');
Config::write('db.password', 'mypassword');
in all your scripts / objects you just have to get the instance of Core and then query the DB
$sql = "select login, email from users where id = :id";
try {
$core = Core::getInstance();
$stmt = $core->dbh->prepare($sql);
$stmt->bindParam(':id', $this->id, PDO::PARAM_INT);
if ($stmt->execute()) {
$o = $stmt->fetch(PDO::FETCH_OBJ);
// blablabla....
If you need more information about singleton look at the PHP doc http://php.net/manual/en/language.oop5.patterns.php
Here is a mostly complete working cut & paste example of Guillaume Boschini's answer above.
A populated DB table (MySQL):
CREATE TABLE `useraddress` (
`addressid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`addresstitle` char(100) NOT NULL,
`streetaddressa` char(100) NOT NULL,
`streetaddressb` char(100) DEFAULT NULL,
`unit` char(50) DEFAULT NULL,
`city` char(50) NOT NULL,
`state` char(2) NOT NULL,
`zip` int(5) NOT NULL,
`zipplusfour` int(4) DEFAULT NULL,
PRIMARY KEY (`addressid`),
KEY `userid` (`userid`),
CONSTRAINT `useraddress_fk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
In /DBLibrary/pdocore.php:
<?php
Config::write('db.host', 'localhost');
Config::write('db.port', '3306');
Config::write('db.basename', 'DBName');
Config::write('db.user', 'DBUser');
Config::write('db.password', 'DBPassword');
class Config {
static $confArray;
public static function read($name) {
return self::$confArray[$name];
}
public static function write($name, $value) {
self::$confArray[$name] = $value;
}
}
class Core {
public $dbh; // handle of the db connection
private static $instance;
private function __construct() {
// building data source name from config
$dsn = 'mysql:host=' . Config::read('db.host') . ';dbname=' . Config::read('db.basename') . ';port=' . Config::read('db.port') .';connect_timeout=15';
// getting DB user from config
$user = Config::read('db.user');
// getting DB password from config
$password = Config::read('db.password');
$this->dbh = new PDO($dsn, $user, $password);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
public static function getInstance() {
if (!isset(self::$instance)) {
$object = __CLASS__;
self::$instance = new $object;
}
return self::$instance;
}
// others global functions
}
?>
In /objectsLibrary/SYS_UserAddress.php:
<?php
define('k_uaddress_addressid','addressid');
define('k_uaddress_userid','userid');
define('k_uaddress_addresstitle','addresstitle');
define('k_uaddress_addressa','streetaddressa');
define('k_uaddress_addressb','streetaddressb');
define('k_uaddress_unit','unit');
define('k_uaddress_city','city');
define('k_uaddress_state','state');
define('k_uaddress_zip','zip');
define('k_uaddress_zipplusfour','zipplusfour');
require_once '../DBLibrary/pdocore.php';
class SYS_UserAddress {
public $addressid;
public $userid;
public $addresstitle;
public $addressa;
public $addressb;
public $unit;
public $city;
public $state;
public $zip;
public $zipplusfour;
public function SYS_UserAddressByAddressId($_addressid) {
$returnValue=FALSE;
$query='select * from useraddress where ' . k_uaddress_addressid . '=:addressid';
try {
$pdoCore = Core::getInstance();
$pdoObject = $pdoCore->dbh->prepare($query);
$queryArray = array(':addressid'=>$_addressid);
if ($pdoObject->execute($queryArray)) {
$pdoObject->setFetchMode(PDO::FETCH_ASSOC);;
while ($addressrow = $pdoObject->fetch()) {
$this->addressid=$addressrow[k_uaddress_addressid];
$this->userid=$addressrow[k_uaddress_userid];
$this->addresstitle=$addressrow[k_uaddress_addresstitle];
$this->addressa=$addressrow[k_uaddress_addressa];
$this->addressb=$addressrow[k_uaddress_addressb];
$this->unit=$addressrow[k_uaddress_unit];
$this->city=$addressrow[k_uaddress_city];
$this->zip=$addressrow[k_uaddress_zip];
$this->zipplusfour=$addressrow[k_uaddress_zipplusfour];
}
$returnValue=TRUE;
}
}
catch(PDOException $pe) {
trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
}
return $returnValue;
}
}
$test=1;
$testAddressId=2;
if($test>0) {
$testAddress = new SYS_UserAddress();
$testAddress->SYS_UserAddressByAddressId($testAddressId);
echo '<pre>';
echo print_r($testAddress);
echo '</pre>';
}
?>
The post above really helped me. This post I am making now would have gotten me to where I wanted to be faster. That is all. If anything isn't right, I'll be around to fix it.
$dbh
isn't within the scope of Foo
, do this instead:
class Foo /*extends PDO*/
{
public $dbh;
public function __construct()
{
$dbh = new PDO(/*...*/);
}
public function bar()
{
$this->dbh->prepare('SELECT * FROM table');
return $this->dbh->execute();
}
}
Also, Foo
doesn't need to extend PDO
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With