How to use EasyPDO

Download EasyPDO

Using EasyPDO

Using PDO is dead simple. Most queries can be performed in a single line of code. The examples that follow show how to perform basic database tasks using EasyPDO.

This examples are live and interactive - output from queries is displayed in dark red.

Database driver: <?php print_r($db->GetConnectionObject()->getAttribute(PDO::ATTR_DRIVER_NAME) . "\n"); ?>
Server version: <?php print_r($db->GetConnectionObject()->getAttribute(PDO::ATTR_SERVER_VERSION) . "\n"); ?>
Connection status: <?php print_r($db->GetConnectionObject()->getAttribute(PDO::ATTR_CONNECTION_STATUS)); ?>

Output generated at 6th Feb 2012 at 2:03:55am
Database driver: mysql
Server version: 5.0.51a-3ubuntu5.8
Connection status: Localhost via UNIX socket

Index

Connecting to a database

<?php
  require_once 'easypdo.mysql.php';
  $db = EasyPDO_MySQL::Instance(servername, database name, username, password);
?>

Fetching Data

EasyPDO provides a number of ways of fetching data from a database. During our examples, we will be working with the following table structure:

CREATE TABLE widgets (ID INT NOT NULL AUTO_INCREMENT,
                      UserID VARCHAR(255) COLLATE UTF8_UNICODE_CI DEFAULT NULL,
                      Name VARCHAR(255) COLLATE UTF8_UNICODE_CI NOT NULL,
                      Quantity INT NOT NULL,
                      Date DATETIME NOT NULL,
                      PRIMARY KEY (ID),
                      KEY (Date),
                      KEY (UserID));

I've also populated this table with some dummy data:

INSERT INTO widgets
  (Name, Quantity, Date)
VALUES
  ('First Widget', 2, '2010-03-13 16:16:00'),
  ('Medium Widget', 1, '2010-03-13 16:28:00');

Fetching a row as an object

We can easily fetch an object representing a row from our table using:

$widget = $db->FetchObject(SELECT ID, Name, Quantity, UNIX_TIMESTAMP(Date) AS Date FROM widgets WHERE ID = 1);
var_dump($widget);

Output generated at 6th Feb 2012 at 2:03:55am
object(stdClass)#4 (4) {
  ["ID"]=>
  string(1) "1"
  ["Name"]=>
  string(12) "First Widget"
  ["Quantity"]=>
  string(1) "2"
  ["Date"]=>
  string(10) "1268457360"
}

This provides us with a neat way of accessing the data - the object has field names that correspond to each column from the query! For example, to get the name of the widget, just use:

echo 'Widget name = ' . $widget->Name;
echo 'Widget date = ' . date('jS M Y g:i:sa', $widget->Date);

Output generated at 6th Feb 2012 at 2:03:55am
Widget name = First WidgetWidget date = 13th Mar 2010 4:16:00pm

Part of the reason I wrote EasyPDO was so that parameterised queries would be quick and painless to code. Here's the same query written using parameters:

$widget = $db->FetchObject('SELECT ID, Name, Quantity, UNIX_TIMESTAMP(Date) AS Date FROM widgets WHERE ID = ?', 'i', 1);

Since each field in our object must have a unique and valid name, we must be careful in ensuring our SQL statement provides aliases for the results of any function calls, or any duplicated fields. For example:

$widget = $db->FetchObject('SELECT ID, Name, Quantity, UNIX_TIMESTAMP(Date) FROM widgets WHERE ID = 1');

creates a field of $widget called UNIX_TIMESTAMP(Date). This is obviously not a valid PHP identifier, and we would not be able to access the value of this field in the normal manner.

Similarly, you cannot use:

$widget = $db->FetchObject('SELECT * FROM widgets WHERE ID = 1');

Using "SELECT * FROM" is bad coding practise anyway, and you should always explicitly name the fields you wish to retrieve from the database.

Iterating over a result set

To retrieve all of the rows in a result set, use EasyPDO's "FetchAll" method.

foreach ($db->Fetch('SELECT Name, Quantity FROM widgets LIMIT 2') as $widget)
  echo <<<eot
$widget->Quantity × $widget->Name

eot;

Output generated at 6th Feb 2012 at 2:03:55am
2 × First Widget
1 × Medium Widget

Of course, we can still use parameters here, for example

foreach ($db->Fetch('SELECT Name, Quantity FROM widgets WHERE Quantity > ? LIMIT 5', 'i', 1) as $widget)
  echo <<<eot
$widget->Quantity × $widget->Name

eot;

Output generated at 6th Feb 2012 at 2:03:55am
2 × First Widget

Fetching a row as an array

To fetch a single row from a result set as an associative array, use the FetchArray method

$array = $db->FetchArray('SELECT ID, Name, Quantity, UNIX_TIMESTAMP(Date) AS Date FROM widgets WHERE ID = ?', 'i', 1);
var_dump($array);

Output generated at 6th Feb 2012 at 2:03:55am
array(4) {
  ["ID"]=>
  string(1) "1"
  ["Name"]=>
  string(12) "First Widget"
  ["Quantity"]=>
  string(1) "2"
  ["Date"]=>
  string(10) "1268457360"
}

You can alternatively retrieve an array with numerical indices by calling the static "SetFetchMode" method:

EasyPDO::SetFetchMode(EasyPDO::FETCH_MODE_NUMERIC_ARRAY);
$array = $db->FetchArray('SELECT ID, Name, Quantity, UNIX_TIMESTAMP(Date) AS Date FROM widgets WHERE ID = ?', 'i', 1);
var_dump($array);

Output generated at 6th Feb 2012 at 2:03:55am
array(4) {
  [0]=>
  string(1) "1"
  [1]=>
  string(12) "First Widget"
  [2]=>
  string(1) "2"
  [3]=>
  string(10) "1268457360"
}

To return to associative mode:

EasyPDO::SetFetchMode(EasyPDO::FETCH_MODE_ASSOCIATIVE_ARRAY);

Fetching a single value

Sometimes, you might only want to return a single value from a query. This can be achieved by calling the "FetchValue" method:

$count = $db->FetchValue('SELECT COUNT(ID) AS Total FROM widgets WHERE Quantity < ?', 'i', 2);
echo 'There are ' . $count . ' widgets with a quantity less than 2';

Output generated at 6th Feb 2012 at 2:03:55am
There is 1 widget with a quantity less than 2

Executing SQL

EasyPDO does not limit you to SELECT statements. In fact, you can execute any SQL statement, provided the account you've connected with has sufficient permissions. All SQL statements that do not return a result set are called by the "ExecuteSQL" method.

Inserting Data

To insert data:

$widgetID = $db->ExecuteSQL('INSERT INTO widgets (Name, Quantity, Date) VALUES (?, ?, NOW())', 'si', $widgetName, $quantity);

In this case, because the widgets table has an auto-increment ID field, the ExecuteSQL statement returns the identity of the row inserted. For tables that do not have an identity field, the method returns null.

Updating data

To update data, use the "ExecuteSQL" method with an SQL update statement. The following code makes sure you don't have too many widgets.

$db->ExecuteSQL('UPDATE widgets SET Quantity = ? WHERE Quantity > ?', 'ii', $maxQuantity, $maxQuantity);

Deleting data

Finally, to delete data:

$minQuantity = 20;
$db->ExecuteSQL('DELETE FROM widgets WHERE Quantity < ?', 'i', $minQuantity);