[RESOLVED] Transitioning from mysql to PDO.

[gwerner]

I'm trying to get a handle on PDO. I've been using mysql_real_escape_string to handle database inserts, updates and so forth currently. I feel like I'm about six years behind when it comes to this stuff.

The current way that I deal with database connections is I have a config file that contains the host name, db name, uersname and password. Then, I have a dbConnect file that does the actual connection. See below.

$MYSQL_ERRNO = '';
$MYSQL_ERROR = '';

function db_connect() {
	global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
	global $MYSQL_ERRNO, $MYSQL_ERROR;

$link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);
if(!$link_id) {
	$MYSQL_ERRNO = 0;
	$MYSQL_ERROR = "Conection failed to the host $dbhost.";
	return 0;
}
else if(empty($dbname) && !mysql_select_db($default_dbname)) {
	$MYSQL_ERRNO = mysql_errno();
	$MYSQL_ERROR = mysql_error();
	return 0;
}
else return $link_id;
}

function sql_error() {
	global $MYSQL_ERRNO, $MYSQL_ERROR;

if(empty($MYSQL_ERROR)) {
	$MYSQL_ERRNO = mysql_errno();
	$MYSQL_ERROR = mysql_error();
}
return "$MYSQL_ERRNO: $MYSQL_ERROR";
}

Below is what I'm coming up with for PDO. How bad is it?

function db() {
	global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
	static $conn;
	if (!isset($conn)) {
		$conn = new PDO("mysql:host=$dbhost;dbname=$default_dbname", $dbusername, $dbuserpassword);
	}
	return $conn;
}

A select query and how I would retrieve results.

$rows = db()->query("SELECT users_id FROM users")->fetchAll();
foreach ($rows as $row) {
	echo $row['users_id'] . '<br />';
}

One of the things I'm not certain about is the try/catch. I don't have it above, but every example I found uses it. I have a feeling my method above is incorrect. Also, most of the examples I've found always have the INSERT, UPDATE or SELECT inside the try/catch. Does this have to be, or can queries exist outside?

[dalecosp]

I'm not at all sure, but it's good practice, just as wrapping mysql_query in a conditional to allow for meaningful feedback in event of an error is also good practice; that's why you're always seeing it in try blocks.

[NogDog]

You could go for a more OOP approach to what you are doing with your function, using the Singleton pattern:

<?php
class PDOSingleton
{
   private static $instance;
   private function __construct() { }
   public static function getInstance($dsn, $user=null, $pass=null, $db=null)
   {
      if(empty(self::$instance))
      {
         self::$instance = new PDO($dsn, $user, $pass, $db);
      }
      return self::$instance;
   }
}

// USAGE:
$dbhost = 'localhost';
$dbname = '******';
$dbusername = '******';
$dbuserpassword = '******';
$dsn = "mysql:host=$dbhost;dbname=$dbname";
try {
   $db = PDOSingleton::getInstance($dsn, $dbusername, $dbuserpassword);
   $id = 1;
   $stmt = $db->prepare("SELECT * FROM table_name WHERE id = :id");
   $stmt->bindParam(':id', $id, PDO::PARAM_INT);
   $result = $stmt->exec();
   // etc....
}
catch(PDOException $e)
{
   error_log((string) $e);
   die("Database error, see error log");
}

[gwerner]

I had a feeling someone was going to lead me towards OOP. I'm not that well versed with OOP yet. I understand the premise, but I don't have the syntax down. But I'm learning.

I found this on another thread and am making sense and use out of it. Are there limitations to this class? On simple single queries it works as expected. As I get deeper into my web app I'll have more complex queries. I guess it'll be trial and error. : ) Which seems to be how I learn this stuff anyway.

Also, I've been scouring the web looking into Singleton patterns, and have read that I should avoid them. But I'm not always clear on the context as to when, where, environment or type of application they should be avoided.

Oh, and one more question. Is it safe to include the db credientials in the class. I believe they are a protected variable, but not certain that is 100% safe?

class PDODB {
	static protected $instance = null;
	protected $conn;
	protected $host = 'localhost';
	protected $user = 'root';
	protected $pass = 'root';
	protected $database = 'mydb';
	protected $stmt;

private function __construct() {
	// fill the connection vars from wherever you choose
	$this->connectDB();
}

private function connectDB() {
	// Connect to the database
	try {
		$this->conn = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database, $this->user, $this->pass);
		$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	}
	catch(PDOException $e) {
		throw new Exception('DATABASE ERROR: ' . $e->getMessage());
	}
}

public static function getInstance() {
	// method for returning db
	if(self::$instance == null) {
		self::$instance = new PDODB();
	}
		return self::$instance;
}

public function getData($sql,$params) {
	// run a select query
	try {
	$this->stmt = $this->conn->prepare($sql);
	$this->stmt->execute($params);
	return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
	}
	catch(PDOException $e) {
		throw new Exception('DATABASE ERROR: ' . $e->getMessage());
	}
}

public function editData($sql,$params) {
	// run an insert/update/delete query
	try {
		$this->conn->beginTransaction();
		$this->stmt = $this->conn->prepare($sql);
		$this->stmt->execute($params);
		$this->conn->commit();
		return $this->stmt->rowCount();
	}
	catch(PDOException $e) {
		$this->conn->rollBack();
		throw new Exception('DATABASE ERROR: ' . $e->getMessage());
	}
}

public function __destruct() {
	$this->conn = null;
}
}

[NogDog]

The singleton pattern has some shortcomings (e.g.: search on "why singletons are evil"), so you need to balance those negatives against any perceived positives.

Personally, I prefer to instantiate a non-singleton class once (perhaps in a config include file?), then pass that object to each class that needs it via its constructor. But I'm not as fanatical about it as some of the writers in those links. 🙂

[gwerner]

More questions. When doing a SELECT with the class I posted above is there a way to use rowcount. It doesn't appear so? Is it proper form to just check if the array that is returned contains a value?

Another question. bindParam. NogNog's solution above includes this. As I've been reading the manual it seems this is something I want to include. It kinda seems like a way to cast the variable before making it's way to the database and helps the database by instructing it on what kind of data it's receiving. What if you have 20 variables from a form post? Do you bind each one individually? They could all be different types of data. Is there a resource that shows real world examples vs. the simple stuff I seem to be finding. It is more helpful for me to see proper code and then break it apart and put it back together to see how it works.

[NogDog]

The binding also handles the escaping of special characters where needed, similar to mysql_real_escape_string().

As far as binding several parameters, the $params argument in your query functions should be an associative array where the key is the query "place-holder", e.g.:

$params = array(
   ':id' => $id,
   ':name' => $name
);

Then that array ($params in this example) would be passed as the second function parameter, the query string being the first. More info at http://www.php.net/manual/en/pdostatement.execute.php .

[gwerner]

Yet another question. The class I posted doesn't seem to handle bindParam very easily. NogDog's example seems more expandable. If queries get complex, multiple queries, multiple values and that sort of thing. True or False?

Edit - I just read: You can use bindParam() or you can supply a $values associative array to the execute() function. Doing both is redundant. True or False?

[NogDog]

Both would seem to be redundant -- at least if they are for the same parameters. I would guess whichever one happens second would overwrite the prior one, but it's not something I've tried or researched. 🙂

[gwerner]

Thanks for the help. I believe I'm getting a better feel for how it works.