1. Abstract
  2. MySQL
  3. SQLite
  4. Summary

Abstract

The term CRUD refers to the four basic functionalities used in most PHP applications.

  • Create
  • Retreive
  • Update
  • Delete

These tasks can often be repetitive and time consuming and each database has its own bunch of functions to handle how these tasks are performed. This tutorial shows how to use the PHPRO CRUD class athttp://www.phpro.org/classes/PDO-CRUD.html. The CRUD class provides a common method of performing each of these tasks with the use of PDO, only the Data Source Name (DSN) changes when connecting.

This example CRUD class allows the user to manipulate simple database tasks in a common way. By using PHP PDO, the interface never changes across any database supported by PDO. Save the CRUD class as crud.class.php for use with the following scripts.

MySQL

Using the CRUD class is quite easy. Simply include the class in a script and it is ready to go. As the class uses PDO, the interface for all actions is identical. Here a table of animals, in a database is used demonstrate. The table definition looks like this.

/*
	CREATE TABLE animals (
	animal_id int(11) NOT NULL AUTO_INCREMENT,
	animal_name varchar(20) NOT NULL,
	animal_type varchar(20) NOT NULL,
	PRIMARY KEY (animal_id)
	);

In the script belew, this table is loaded into the animals database and several animals INSERTed.


<?php 

include 'crud.class.php';

/*** a new crud object ***/
$crud = new crud();

/*** The DSN ***/
$crud->dsn = "mysql:dbname=animals;host=localhost";

/*** MySQL username and password ***/
$crud->username = 'username';
$crud->password = 'password';

/*** array of values to insert ***/
$values = array(
array('animal_name'=>'bruce', 'animal_type'=>'dingo'),
array('animal_name'=>'bruce', 'animal_type'=>'wombat'),
array('animal_name'=>'bruce', 'animal_type'=>'kiwi'),
array('animal_name'=>'bruce', 'animal_type'=>'kangaroo')
);
/*** insert the array of values ***/
$crud->dbInsert('animals', $values);

/*** select all records from table ***/
$records = $crud->rawSelect('SELECT * FROM animals');

/*** fetch only associative array of values ***/
$rows = $records->fetchAll(PDO::FETCH_ASSOC);

/*** display the records ***/
foreach($rows as $row)
{
foreach($row as $fieldname=>$value)
{
echo $fieldname.' = '.$value.'<br />';
}
echo '<hr />';
}

/*** update the kiwi ***/
$crud->dbUpdate('animals', 'animal_name', 'troy', 'animal_id', 3);

/*** delete the second record ***/
$res = $crud->dbSelect('animals', 'animal_id', 3 );

/*** show the results ***/
foreach($res as $row)
{
echo $row['animal_name'].' = '.$row['animal_type'].'<br />';
}
?>

Stepping through the script, the CRUD class is used to create a table, then INSERT several animals. A raw query is sent to retrieve all values from the table (handy for debugging). One of the animals does not belong though. The kiwi is a New Zealand bird, and is instead named troy. The dbUpdate method is used to fix this. Then the dbSelect is used to show the new value of the record with the id of 3.

animal_id = 1
animal_name = bruce
animal_type = dingo









animal_id = 2
animal_name = bruce
animal_type = wombat









animal_id = 3
animal_name = bruce
animal_type = kiwi









animal_id = 4
animal_name = bruce
animal_type = kangaroo









troy = kiwi

SQLite

SQLite has several advantages of conventional databases, not the least of which is its speed. Many programmers use SQLite for small, or simple applications such as configuration or embedding.

The CRUD class, which uses PHP PDO extention makes a simple interface to using SQLite. In this example, the SQLite database is loaded into memory, rather than to file on disc.

The scripts is much the same as with the mysql script above, only the DSN changes which means using the CRUD class on any database is as simple as changing the DSN.


<?php
error_reporting(E_ALL); 

include 'crud.class.php';

$crud = new crud;
$crud->dsn = "sqlite::memory:";

$sql = "CREATE TABLE animals (
animal_id INTEGER PRIMARY KEY AUTOINCREMENT,
animal_name TEXT,
animal_type TEXT
)";

$crud->rawQuery($sql);

/*** insert values into db ***/
$values = array(
array('animal_name'=>'bruce', 'animal_type'=>'dingo'),
array('animal_name'=>'bruce', 'animal_type'=>'wombat'),
array('animal_name'=>'bruce', 'animal_type'=>'kiwi'),
array('animal_name'=>'bruce', 'animal_type'=>'kangaroo')
);

$crud->dbInsert('animals', $values);

$records = $crud->rawSelect('SELECT * FROM animals');
$rows = $records->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row)
{
foreach($row as $fieldname=>$value)
{
echo $fieldname.' = '.$value.'<br />';
}
echo '<hr />';
}

/*** update ***/
$crud->dbUpdate('animals', 'animal_name', 'troy', 'animal_id', 3);

/*** retrieve a single record ***/
$res = $crud->dbSelect('animals', 'animal_id', 3 );
foreach($res as $row)
{
echo $row['animal_name'].' = '.$row['animal_type'].'<br />';
}
?>

Like the MySQL script above, the code is the same, and the output is the same, which allows great flexibility when choosing a database, as it will work with any database supported by PHP PDO, which is all mainstream databases.

Abstract

The scripts in this tutorial show how PDO can be used to create a simple and CRUD class with PDO that will work across multiple databases, only the DSN needs to change when connecting and everything else is the same. This is a great addition to the PHP toolkit and this example can be modified or extended to provide even further functionality.

 

Join the Conversation

1 Comment

Leave a comment