Framework:sb PDO

From Surebert wiki

Contents

Overview

All surebert framework application should be using PHP 5's PDO db interface. The framework has an extended version of PDO called sb_PDO which in turn is extended into sb_PDO_Debugger and sb_PDO_Logger

You should familiarize yourself with the PDO to begin. Two great resources are the PHP PDO manual at http://us2.php.net/pdo and the phpro.org PDO tutorial http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

Framework Conventions

You should make your database connection a property of the global App static object. If you only have one database connection it should be called App::$db. If you have more than one database connection per application then the names should be more specific. e.g. App::$lawson_db. By doing this you can access your database connection globally from anywhere.

In general all SQL queries should be placed in the methods of your models. The model instance methods than can be called in the ViewClass (hybrid controller) or in the .view templates themselves.

Usage

Creating a connection

To instantiate a db connection with sb_PDO you use the same exact calls as you would with PDO itself.

php code

App::$db = new sb_PDO("mysql:dbname=evals;host=".$host, $user, $pass);

Using s2o

sb_PDO also extends PDO to provide a simple to use method called s2o. s2o stands for SQL to Object. It takes SELECT statements and converts them to an array of objects. If no rows are returned it always returns a blank array. That way you can safely go right into using foreach on the return from a SELECT statement.

php code

$employees = App::$db->s2o("SELECT first_name, last_name FROM employees");
foreach($employees as $employee){
	echo "hello ".$employee->first_name;
}

Using Parameters. This would prepare a statement and return all employees with an id greater than 5. The values of parameters set in the first argument are specified in the second Array argument to s2o.

php code

$employees = App::$db->s2o("SELECT first_name, last_name FROM employees WHERE id < :id", Array(":id" => 5));
foreach($employees as $employee){
	echo "hello ".$employee->first_name;
}

Using ClassName. If you want the results to come back as instances of a certain object type, you can use the following. Notice the third argument is a className. Each row comes back as an instance of Employee with the properties set by the row and any additional properties and methods set in the class definitions. In this example we assume the Employee class definition includes a method for say_hello()

php code

$employees = App::$db->s2o("SELECT first_name, last_name FROM employees WHERE id < :id", Array(":id" => 5), 'Employee');
foreach($employees as $employee){
	echo "hello ".$employee->say_hello();
}

Error Reporting

By default sb_PDO uses PDO::ERRMODE_SILENT so that no sql information is accidentally displayed in production. During development, when you want to see errors, you should use sb_PDO_Debugger instead.

sb_PDO_Debugger

Works just like sb_PDO but throws exceptions for errors. Works great with any PDO statements and especially well with debugging s2o call. TO switch to using sb_PDO_Debugger you simply change the definition of your database connection. sb_PDO_Debugger intentionally extends sb_PDO instead of debugging state being determined by additional properties or methods of sb_PDO as make it use additional debugging resources only when in development. When you are ready to switch to production make sure to switch back to sb_PDO.

php code

App::$db = new sb_PDO_Debugger("mysql:dbname=evals;host=".$host, $user, $pass);

sb_PDO_Logger

Sometimes you want to be able to log SQL queries and errors to a log for debugging. This is convenient because it allows you to see the overall SQL call usage on a per page basis. It also makes catching excess SQL queries a snap.

php code

App::$db = new sb_PDO_Logger("mysql:dbname=evals;host=".$host, $user, $pass);

By default it logs to a sb_Logger_FileSystem log which writes into /private/logs/dbConnName/date.log A new log file is generated for each day. If you would like to use another kind of logger you can set the logger using sb_PDO_Logger's set_logger($logger); method. The argument must be an instance of a class that extends sb_Logger_Base.

php code

App::$db = new sb_PDO_Logger("mysql:dbname=evals;host=".$host, $user, $pass);
App::$db->set_logger($my_logger);

If you would like to log for once specific SBF_ID, the session ID used in a framework application, then you should use the log_only_for_SBF_ID() method to set that. Logging will only be done for one specific user.

php code

App::$db = new sb_PDO_Logger("mysql:dbname=evals;host=".$host, $user, $pass);
App::$db->log_only_for_SBF_ID('84c3594b1814c0ba2f089e89acfc8822');
Retrieved from "http://wiki.surebert.com/index.php/Framework:sb_PDO"