PDO means PHP Data Objects and is an interface unified for accessing databases in PHP.
Requires a driver PDO for each particular database.
An implementation for SQLite3 exists, and the driver PDO SQLite v.3, is the only one that functions with PHP5.

The code to declare a SQLite object is:
$db = new PDO('sqlite:cds.db3');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

cds.db3 referes to a database installed in the same directory as the program.
If it is in another directory you must use a relative path.

1.- The database name may be any one.
2.-The declaration is different from the one used in versions 1 and 2 of SQLite, that was
$db = new SQLiteDatabase('cds.db3');.
Moreover it is incompatible, if you intend to open a SQLite v.3 in this manner it gives error.
3.- The user must have acces to read and write to the directory in which is cds.db3.
4.-If the file dose not exist, it is created, if the pointed directory exists.

Many of the declarations are similar to the one used in anterior versions of SQLite. For example:Supposing that a table disks exists, we can make;
$resultado = $db->query('SELECT * FROM disks');
But the FETCH are a little different ones, they always have a parameter such as PDO::FETCH_ASSOC that determines the fetch characteristics del fetch, we will see later another options:
$fila = $resultado->fetch(PDO::FETCH_ASSOC);
$array_de_filas = $resultado->fetchall(PDO::FETCH_ASSOC);
Attention to the ::

The sentence fetch returns an array of database rows indexed by the field name, and is useful because it permits to call by the field names.
The second fetchall returns an array of arrays that includes all the rows.
To start a SQLite v.3 database, is very useful SQLite Administrator
It is my opinion that the use of this program or a similar one is the best option to create a database SQLite, define its tables, fields, indexes, views, etc..
To work with the code I use HTML-Kit, that permits a previsualization of the PHP code and so to detect more easily and correct errors.Click here to see the configuration instructions

SQLITE_MASTER
All the SQLite databases have a system table, sqlite_master in which is stored the structural information.
In order to consult this table we have the following columns:
1.- type, can be table, view o index.
2.-name, the object name.
3.-tbl_name the table name.
4.-root_page the first page of the file with data of this element.
5.-sql , sentences SQL generating the element

<?php
$db = new PDO("sqlite:fieras.s3db");
// function to determine if the animals table exists
function exists_table($dataBase, $tablaDB)
{
$result = $dataBase->query("SELECT name FROM sqlite_master WHERE type='table' AND tbl_name=$tablaDB";
return $result;
}

$tabla="animalessssss";
// The call to the function
$result= existe_la_tabla($db, $tabla);

// the result returned
($result) ? print("The table ".$tabla."already exists ") : print("the table ".$tabla".does not exists ");
?>

In this example the result is: the table animalesssss does not exists.

MEMORY
We can place the database in memory. For example:
$db = new PDO(sqlite::memory:);
When working in memory, once the program finished the databas desapears.
Whle exists, $db is a reference to an object.
Of course it is possible to open different databases instantiated in diferent objects,
each one independent of the others and to pass data form one to another,
and make everithing that is possible to make with objects.
The databases in memory are useful because they permit to use SQL.
They are used also to put code examples.

<?php
try {

/* a new in memory database is created */
$db = new PDO('sqlite::memory:');
echo 'database connected
';

/* we create a table, with three fields */
$db->exec("CREATE TABLE tableName(tableNameID INTEGER PRIMARY KEY,columna1 TEXT UNIQUE,columna2 TEXT)");
$count = $db->exec("INSERT INTO tableName(columna1,columna2) VALUES ('pepe', 'juan')");

/*** number of rows inserted ***/
print($count.' ');
$sql = "SELECT * FROM tableName";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('tipo ='.$row['columna1'] .' animal='. $row['columna2'] . '&ltbr/>');
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}

//we change de select to $db , but continue to use the same database
$sql1 = "SELECT columna1 FROM tableName";
$set1= $db->query($sql1) ;
foreach ($set1 as $rowa)
{
print('tipo ='.$rowa['columna1'].'&ltbr/>');
}
?>


CREATING A TABLE NOT IN MEMORY
The method is the same as the one used to create the table in memory, although in this case it would be better to use SqliteAdministrator, to create the database, tables,fields, etc.
(It is not possible to use SqliteAdministrator for memory databases).

We will use a database fieras.s3db and add to ir a table with three fields.
For this we use $db->exec(SQL command);

We take precautions, the database is opened but is not created if it does not exist, the same is made with the table.
So the example only works if the database exists and the table to create does not exist.

<?php
try
{

$baseDeDatos = "fierasPepe.s3db";
// check if the file exists
if(file_exists($baseDeDatos))
{

/*** connect to SQLite database ***/
$db = new PDO("sqlite:$baseDeDatos");
echo 'database open';

//check if it is needed to create the table using sqlite_master
$st = $db->query("SELECT name FROM sqlite_master WHERE type = 'table' AND tbl_name='animals' ");
// the QUERY returns false if the SELECT does not return anyhting
if( !$st )
{
$sqlCreateTable = 'CREATE TABLE animals(id_animal INTEGER PRIMARY KEY AUTOINCREMENT,
id_tipo VARCHAR(40) NOT NULL,id_name VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);
}
else
echo 'The table already exists';
}
else
echo 'The database'. $baseDeDatos.' does not exist and has not been created';
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


Notas
1.- id_animal Integer Primary Key creates a column Auto-increment.
It is not necessary to take care of it, on inserting will add values to the field.

INSERT
To insert values in a table we use INSERT and exec.
<?php
try {

/*** connect to the database***/
$db = new PDO("sqlite::memory:");
echo 'database open';
$sqlCreateTable = 'CREATE TABLE animals(id_animal INTEGER PRIMARY KEY AUTOINCREMENT,
id_tipo VARCHAR(40) NOT NULL,id_name VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);
$count = $db->exec("INSERT INTO animals(id_tipo,id_name) VALUES ('kiwi', 'troy')");

/*** number of rows inserted ***/
echo $count;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

The output should be:
database open
1

This shows a good connection and the insert of a row, in other case $count will return 0.
It is possible to use teh same system to introduce more values but it si more effcicient to use a TRANSACTION.

CREATE A PREPARED STATEMENT
A prepared statemen is a SQL precompilated sentence that can have zero or various parameters.
There are two diferent types, with names and with place holders.
We use $preSt = $dbh->prepare($insercion)

1.-Con nombres

<?php
try {

/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
/*** a message showing that the dabase was open, or attention that was created ***/
echo ' database open ';

$insercion="INSERT INTO animals(id_tipo,id_name) VALUES (:id_tipo,:id_name)";
// making the prepared statement
$preSt = $dbh->prepare($insercion);

//bind parameters
//----------------------------------------------------

$preSt->bindParam(':id_tipo',$id_tipo_val);
$preSt->bindParam(':id_name',$id_name_val);

//----------------------------------------------------


$id_tipo_val = "pájaro";
$id_name_val = "cigueña";
$preSt->execute();

$id_tipo_val = "pájaro";
$id_name_val = "ruiseñor";
$preSt->execute();

$id_tipo_val = "pájaro";
$id_name_val = "jilguero";
$preSt->execute();

}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

NOTAS
1.-Attention to the sentence $preparedStatement ->execute().
2.-The $preparedStatement->bindParam(':column1', $column1_val); bind the table columns and its values.
We are using an already created database fieras.s3db with two columns id_tipo, id_name.
3.-Observe that three $preSt->execute() are executed, each one with different values.



2.- With posicionals PlaceHolders ? .

Attention: They can not be mixed with the ones with names.
In the example we do a query INSERT substituying one name and one value for the positionals placeholders ? .

<?php
/*** connect to SQLite database ***/
$db = new PDO("sqlite::memory:");
echo 'database open &ltbr />';

// create a table
$sqlCreateTable = 'CREATE TABLE numeros(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(40) NOT NULL,value INTEGER NOT NULL)';
$db->exec($sqlCreateTable);
$stmt = $db->prepare("INSERT INTO numeros(name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert a row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

//query returns one set, or false if the SELECT is not correct.
$sql = "SELECT * FROM numeros";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('name: '. $row['name'] .'---- '. 'value = '.$row['value'] . '&ltbr />');
}
?>


QUERY
QUERY returns an object, or false if the SELECT is not correct, in that late case the FOREACH will give error.
<?php
try {

$dbh = new PDO("sqlite:fieras.s3db");
/*** confirm connection ***/
echo 'database open';
$sql = "SELECT * FROM animals";
$obj= $dbh->query($sql) ;

/*
The two print show that query($sql) is similar to $set= $dbh->query($sql,PDO::FETCH_BOTH),
(see later).*/

foreach ($obj as $row)
{
print('type ='.$row['id_tipo'].' animal='.$row['id_name']. '&ltbr/>');
print('type ='.$row[1] .' animal='. $row[2] . '&ltbr/>');
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Notas
1.- It is possible to iterate directly the $obj with foreach. That is due to the fact tht internally the query implements the SPL iterator, with all the benefices of using SPL.
The greatest beneft is that the SPL iterators only treat one element at a time and so can work with large sets without memory problems.
2.-In the try-catch, the try is normal but catch(PDOException $e) is not. We will treat that later.

UPDATE
To edit the value of a database field we use another time the method $dbh->exec but with a SQL sentence, UPDATE
<?php
try
{

/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
/*** a little message to say we did it ***/
echo 'database open';
$count = $dbh->exec( "UPDATE animals SET id_name='cernícalo' WHERE id_name='cigueña'");
/*** number of rows affected ***/
echo Se alteraron ".$count." filas";
/*** close connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


We see: Database open and the number of rows modified.
If the number of rows is 0, it means that no rows were found with the specified where condition.

QUERY WITH TWO PARAMETERS
The query simple comports itself as a query($sel,PDO::FETCH_BOTH)
but there are other possibilities as query($sel,PDO::FETCH_NUM) and query($sel,PDO:FETCH_ASSOC.).

Sometimes is useful to dispose of only one index associative or numeric .

This is what we make in the following code:
($dh->query(' SELECT * FROM animals', PDO::FETCH_ASSOC)
Of course for the numeric index we would use: PDO::FETCH_NUM

PDO::FETCH_ASSOC
To return an associative array the constant PDO::FETCH_ASSOC is used returning the names of the fields as indexes or keys of the resulting array.
<?php
try
{

$dbh = new PDO("sqlite:fieras.s3db");
echo 'database open';
$sel ='SELECT * FROM animals';
$que=$dbh->query($sel,PDO::FETCH_ASSOC);
foreach($que as $rowa)
print($rowa['id_animal']."----------". $rowa['id_name']."&ltbr />");

/*** cerrar la conexión ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


The $que=$dbh->query($sel,PDO::FETCH_ASSOC);
has returned an objet PDOStatement that can be iterated directly.

/** It is possible to compress the code so
foreach ($dbh->query('SELECT * FROM animals',PDO::FETCH_ASSOC) as $row)
print($row['id_animal']."........". $row['id_name']."&ltbr />");**/


fetchall(PDO::FETCH_NUM)
The object returned by the query pertains to a class that includes different
functions like fetch() and fetchall() that we can use to get different results.
These functions can receive parameters of type PDO::
Her we are going to use $obj->fetchall(PDO::FETCH_NUM)
<?php
try
{

/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
echo 'database open ';
$sql = "SELECT id_animal,id_tipo,id_name FROM animals";
$obj = $dbh->query($sql);
$fet=$obj->fetchall(PDO::FETCH_NUM);

/*Attention: We apply the PDO::FETCH_NUM upon the fetchall(), no upon the query().
That explodes the set of rows.
*/

foreach($fet as $row)
{
foreach($row as $key=>$val)
{
echo $key.': '.$val.' ';
}
echo '&ltbr />';
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

The result is:
database open
0: 17 1: pájaro 2: cernícalo
0: 18 1: pájaro 2: ruiseñor
0: 19 1: pájaro 2: jilguero
The indexes for each row are now numerical ones.


fetch(PDO::FETCH_BOTH)
This is an example of $result = $stmt->fetch(PDO::FETCH_BOTH), on actuating over the fetch, we explode the set and can use it directly in the FOREACH,
We can use field or numeric indexes.
In fact in the FOREACH of the example we use both.
That makes rather confuse the devolution of results, in this particular case.


<?php
try
{

/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
echo 'database open ';

$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
$result = $stmt->fetch(PDO::FETCH_BOTH);
foreach($result as $key=>$val)
{
echo $key.' - '.$val.'&ltbr />';
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


fetchAll(PDO::FETCH_OBJ)
PDO::FETCH_OBJ returns an object of an anonyme class, that maps the names of the fields of the table as properties ( variables)of a class.
It is not necessary to define a class, as it is in the following FETCH_CLASS.


<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
echo 'Connected to database&ltbr />';
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
$arrBestia = $stmt->fetchAll(PDO::FETCH_OBJ);

/*** properties ***/
echo $arrBestia[1]->id_tipo.' ';
echo $arrBestia[1]->id_name.' ';

/*** close connection ***/
$dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

NOTE
Using the names of the fields as class properties make it easier to integrate the results in an OOP ambient.

fetchall(PDO::FETCH_CLASS)
PDO::FETCH_CLASS instanciates an array objects of the specified class.
The names of the fields of the specified table in the database map to the variables of equal name in the class, if they do not exist, they will be created.
That permits less code and increases speed because the mappings are treated internally.


<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
/*** lets define a class animales, different from the table animals in the database.
***/

class animales
{
public $id_animal;
public $id_type;
public $id_name;
public $nombreAnimal;


//function to capitalize first letter
public function capitalizeType($pepe)
{
return ucwords($pepe);
}
}

//--------------------------------------------------------
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);

//here we bind $stmt with the animales class
//el $arrObj is already an array of objetos of the animales class
$arrObj = $stmt->fetchALL(PDO::FETCH_CLASS, 'animales');
/*** iterate the array ***/
foreach($arrObj as $bestia)
{

// The following sentence implies that we are inside an object of the animales class.
$bestia->nombreAnimal = $bestia->id_name."-"."animal";
/*** call to the function capitalizeType ***/
echo $bestia->capitalizeType($bestia->id_tipo)." = ".$bestia->capitalizeType($bestia->id_name).'&ltbr />';
}
echo $arrObj[1]->nombreAnimal;
/*** cerrar la conexión ***/
$dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


The code returns a list of animals with the first letter capitalyzed.
Using PDO::FETCH_CLASS the names were supplied directly form the database to the $arrObj(that is an array of objets of the animales class).
The results were modified, capitalizing the first letter, and the value of one variable (nombreAnimal ) was asigned.

fetchObject()
PDO supplies an alternative to PDO::fetch and PDO::FETCH_CLASS :
PDOStatement::fetchObject().Note that now we are using fetch, and not fetchall as in the example above.
The result is similar but in only one sentence.
We use a while to parcour the rows of the database table.


<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);


class animales
{
public $id_animal;
public $id_type;
public $id_name;
public $nombreAnimal;


public function capitalizeType($pepe)
{
return ucwords($pepe);
}
}

while($obj = $stmt->fetchObject('animales'))
{

$obj->nombreAnimal = $obj->id_name."-"."animal";
echo $obj->capitalizeType($obj->id_tipo)." = ".$obj->capitalizeType($obj->id_name).'&ltbr />';
echo $obj->nombreAnimal.'&ltbr />'.'&ltbr />';
}

$dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>



FETCH_INTO
The constant PDO::FETCH_INTO permits to incrust values in an array of objets of a defined class.
The initial values of the object are sustituted by the incrustated ones.
Like in PDO::FETCH_CLASS the names of the fields map to the properties(variables)of the class.
In this example de fecth mode is defined using setFetchMode(PDO::FETCH_INTO, $bestias)

<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);


class animales
{
public $id_animal;
public $id_tipo;
public $id_name;
public $nombreAnimal;


public function capitalizeType($pepe)
{
return ucwords($pepe);
}
}


$bestias = new animales;
$bestias->id_animal = 14;
$bestias->id_tipo = 'pez';
$bestias->id_name = 'merluza';

//----------------------------------------------------------------------
//value returned by the object $bestias

echo $bestias->id_name.' ';
/*** set the fetch mode ***/
$stmt->setFetchMode(PDO::FETCH_INTO, $bestias);
/*** loop upon the object array, now that the values are already substituted ***/
foreach($stmt as $bestias)
{
echo $bestias->id_name.' ';
echo $bestias->capitalizeType($bestias->id_name).' ' }

/*** close connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


Transactions
It was seen before how to use sentences INSERT to input values.
This works, but with SQLite there is a problem, for each access the row is blocked.

Using a TRANSACTION the process can be condensed in only one access.
Moreover they have the benefit of rolling back if there is an error, for example the fall of the system.

The transaction begins with beginTransaction(), and ends with commit()
beginTransaction() suppress the auto-commit, so that the sentences are not executed until is send commit.
When commit, is called all the sentences are executed and the database returns to the state auto-commit.
The example shows how to do it with a database in memory.


<?php
try
{
$dbh = new PDO("sqlite::memory:");

echo 'Conectado a la base de datos';
/*** put the mode PDO error to excepción ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** Create a table, with three fields ***/
$table = "CREATE TABLE animals(animal_id INTEGER NOT NULL PRIMARY KEY,
animal_type VARCHAR(25) NOT NULL,
animal_name VARCHAR(25) NOT NULL
)";

$dbh->exec($table);
/*** begin transactión ***/
$dbh->beginTransaction();
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('emu', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('funnel web', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('lizard', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('dingo', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('kangaroo', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('wallaby', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('wombat', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('koala', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('kiwi', 'bruce')");

/*** commit transaction ***/
$dbh->commit();

echo 'Data entered successfully';
$sql = "SELECT * FROM animals";
$set= $dbh->query($sql,PDO::FETCH_ASSOC) ;
foreach ($set as $row)
{
print('tipo ='.$row['animal_type'] .' animal='. $row['animal_name'] . ' ');
}
}
catch(PDOException $e)

{
/*** roll back the transaction if it fails ***/
$dbh->rollback();
echo $sql . ' ' . $e->getMessage();
}
?>


ATTACH DATABASE
The sentence ATTACH DATABASE permits to add other databases to the connection with the main database.
The name of the databases to add must be between double quotes .
The added databases can be detached using DETACH DATABASE.

It is possible to read and to write to an added database, also it is possible to modify the schema.
But it is not permitted to create in the added database a table with the same name that one existing already ,although you can add databases with tables that have the save name that the main database, and you can include a database more than one time.
The possibility to add different databases permits for example to have a database for each office, without block problems, and treat them afetrwards grouped.
There is a limit: No more than ten (10) databases can be added.

If two tables have the same name in two different databases they are referenced with the sintaxis nameDatabase.nameTable.

The transactions are atomics, if the main database is not memory.
In other case the transactios continue to be atomics inside each individual database, but if the system falls inside a commit it can take place that some databases will be modified and another ones no.

In the following example the object db connects to films3.s3d, and adds fieras.s3db, permiting a mixed SELECT of both databases.
Really the SELECT permits all that the sentence SELECT permits like using WHERE.

<?php
try
{

$db = new PDO("sqlite:films3.s3db");
echo 'database opened';
$db->exec("ATTACH DATABASE 'fieras.s3db' AS fieras");
$sql = "SELECT id_tipo, id_name, nombrePel FROM fieras.animals,peliculas";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print $row['id_tipo'] .' - '. $row['id_name'] . $row['nombrePel'].' ';
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


A global instance
Here we get it with a SINGLETON.
The objetive of a Singleton is to get that the class have only one object(instance) and supply an access point to it.
A new instance is created only the first time that it is accessed, in the following accesses it returns the existing instance.


<?php

class db
{

/*** Declare the instance ***/
private static $instance = NULL;
// the constructor is declared private so that nobody can create a new instance using new.
private function __construct()
{

/*** here it is not necessary to put anything ***/
}
/***
We return the instance or create the initial conection, what we return is a PDO object, the access to the function is publid.
***/
public static function getInstance()
{
if (!self::$instance)
{
self::$instance = new PDO("sqlite:zoomadrid.s3db");
self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$instance;
}

// We make also private the function _clone so that the instance cannot be cloned.
private function __clone()
{ }
}

//---------------- end of the class------------------
try
{

/*** query the database ***/
$sql="SELECT * FROM animals";
$result = db::getInstance($sql->query);

/*** loop ***/
foreach($result as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . ' ';
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

The above code will produce a result like this:
emu - bruce
funnel web - bruce
lizard - bruce
dingo - bruce
kangaroo - bruce
wallaby - bruce
wombat - bruce
koala - bruce

This access method saves the code to create when a new instance is called and it is not necessay to create it from the initial state.
Note that the constructor and clone are declared private so that an instance could not be instantiated or cloned.


Error treatement
The error treatement in PDO comes in various flavors.
Previosuly we have used the simplest of the try{} catch{}blocks to return an error in the database connection.
But, what takes place if there are other errors?, for example if the name of a fiel does not exist.
Lets see first how we treat a simple error in the prior code.


<?php
try
{
$dbh = new PDO("PDO("sqlite:zoomadrid.s3db");
echo 'Connected to database';

/*** SELECT with an incorrect field name ***/
$sql = "SELECT username FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '&ltbr />';
}
?>

This code produces the following message:
Connected to database
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 18

This is due to the fact that there is no error treatement.
The SELECT specifies a non existing field but we cannot see which is the error.
To remediate this we need to add an atribute to the handling of error that we want to use.

The types of error handling are:
Exception
Warning
Silent

Lets begin with exception in the prior code.

<?php
try
{
$dbh = new PDO("PDO("sqlite:zoomadrid.s3db");

echo 'Connected to database';
/*** set the error reporting attribute ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The SQL SELECT statement ***/
$sql = "SELECT username FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '
';
}

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Now we would get:
Connected to database
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'username' in 'field list'

To put the error handling to Warning we would substitute the line
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
by:
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
Now we would get a different message

Connected to database
Warning: PDO::query() [function.PDO-query]: SQLSTATE[42S22]: Column not found: 1054
Unknown column 'username' in 'field list' in /www/pdo.php on line 21
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 21

Finally there is the Silent mode.
This mode silences the error so that no advertence is emitted.
But it does not stop the code and the following error cotinue appearing.
Para usarlo pondríamos:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
Ahora se desplegaría la siguiente advertencia:

Connected to database
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 21
The error was silenced but the following ones appear

USER DEFINED FUNCTIONS (UDF)
SQLite can be extended to include functions defined by the user written in PHP, in which the functions predefined in PHP can also be used.
Using sqliteCreateFunction the PHP appears as a SQLite function and all the facilities of a database as SELECT can be used.

The UDF are appropiated to break and group strings and make no standard listings.

For example let us have a list of URLs. and create a list of unique names alfabetically ordered, and no capitalyzed.

http://Example.com/directory/index.html y http://example.com/page.html
could be converted into one string: http://example.com/.

In PHP would be necessary to collect all the URL process them in a script, quit the duplicates, and order them.
In SQL this woul be made using DISTINCT y ORDER BY.

The UDF pass the function written in PHP to one in SQLite. Nothing is written in the database.
If you open another program with the same database you must write another time the PHP function and sqliteCreateFunction.

With an UDF as the following one all the work goes to SQLite, that is its proper place.
Note that we use the QUERY with two parameters and, the SELECT is more complicated as the previous ones.

<?php
try
{

// Abrir base de datos
$db = new PDO("sqlite:udf.s3db");
echo ' database open'." ".";
$urlses = array('http://Example.com/directory/index.html' , 'http://example.com/page.html');
echo "Insertions:"." ";
foreach ($urlses as $urla)
{
$sql = "INSERT INTO urls(nom_url) VALUES ('$urla')";
$count=$db->exec($sql);
echo $count." ";
echo $urla." ";
}

// parse_url($url) is a function predefined in PHP

function url2host($url)
{
$parts = parse_url($url);
return "{$parts['scheme']}: //{$parts['host']}/";
}


// Mapping of url2host() to SQLite host(), and indication that host()will have 1 argument.
$db->sqliteCreateFunction('host', 'url2host', 1);

$sel='SELECT DISTINCT host(lower(nom_url)) AS clean_host FROM urls ORDER BY clean_host';
$r = $db->query($sel , PDO::FETCH_ASSOC);

echo " "."Result = ";
foreach ($r as $row) print($row['clean_host']);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


Returns:
Result=http://example.com/

In order to use a UDF
1.- You write a normal function in PHP.
The arguments of the function are the ones that you desire to pass in the SELECT and the function must return only a value.

The function url2host ()takes an URL, calls the predefined PHP function parse_url() to chop the URL in its component parts.
$parts contains a set with these parts.
So, http://example.com/directory/index.html will be fractiones in some chops, http will be stored in $parts['scheme'], and example.com in $parts['host'].
That returns a value http://example.com/.

See, for definition of parse_url()

2.- url2host()is registered with SQLite using: $db->sqlite_create_function().The function takes four parameters:
2.1.-The handler of the database,here $db.
2.2.-The name elected for the SQLite function, here host.
2.3.-The name of the PHP function, here url2host.
2.4.-The number of expected arguments. This last one is optional but if you know, it helps SQLite to optimize the code.

The names of the functions in SQLite and PHP can be equal, but here we use different ones to clarify.
It has been named host() in SQLite and url2host() in PHP.
Now you can use host() in any SQL query, that use the same connection to the database.

The prior SQL sentence 'SELECT DISTINCT host(lower(nom_url)) AS clean_host FROM urls ORDER BY clean_host', takes the URL stored in the column nom_url, convert it to lower case and calls to the UDF host(),that transforms it.
The returned string is called AS clean_host. This permits later to refer to the results as clean_host in the query SQL, and access to the value in PHP using this name.

As it is in SQLite you can order the list using ORDER BY clean_host, that orders the results alphabetically.

sqliteCreateAggregate
Register two functions for use in SQL sentences.
Description

bool PDO::sqliteCreateAggregate ( string function_name, callback step_func, callback finalize_func [, int num_args] )
Attention:The function is EXPERIMENTAL.

PDO::sqliteCreateAggregate() is similar to PDO::sqliteCreateFunction() except that two functions are registered that can be used to calculate an agregate result through all the files of the query.

The key difference is that in PDO::sqliteCreateFunction are necessary two functions to manage the agregate

1.- step_func is called for each row of the result set.
Its PHP function must store the result in the agregation context.
2.- After all rows are processed, we will call to finalize_func and it will take all the data in the agregation context and return the result.
The Callback functions must return a type understood by SQLite, (an scalar).

Parameters
function_name. The name of the function to use in the SQL sentences.
step_func. Callback function called for each row of the result set.
finalize_func. Callback function to aggregate the data supplied by the anterior function.
num_args. One orientation to the SQLite parse of the number of arguments accepted by the callback function.

Example
In this example, we are creating an aggregating function that will calculate the length of the longest string in one of the columns of the table.
For each row, the max_len_step function is called and passed a context parameter.
The context parameter is just like any other PHP variable and be set to hold an array or even an object value.
In this example, we are simply using it to hold the maximum length we have seen so far; if the string has a length longer than the current maximum, we update the context to hold this new maximum length.
After all of the rows have been processed, SQLite calls the max_len_finalize function to determine the aggregate result.
Here, we could perform some kind of calculation based on the data found in the context.
In our simple example though, we have been calculating the result as the query progressed, so we simply need to return the context value multiplied by 7.

The call to $fe=$que->fetch(PDO::FETCH_NUM) is a basic one, in other case by default the fetch calls PDO::FETCH_ASSOC .

<?php

$data = array('one','two','three','four','five','six','seven','eight','nine','ten_diez');
$db = new PDO('sqlite::memory:');
$db->exec("CREATE TABLE strings(a)");
$insert = $db->prepare('INSERT INTO strings VALUES (?)');
foreach ($data as $str)
{
$insert->execute(array($str));
}
$insert = null;

function max_len_step($context, $rownumber, $string)
{
if (strlen($string) > $context)
{
$context = strlen($string);
}
echo " ".string: ".$string." max value till now : ".$context;
return $context;
}

function max_len_finalize($context, $rownumber)
{
echo ' '."last max value : ".$context.' ';
return $context*7;
}

$db->sqliteCreateAggregate('max_len', 'max_len_step', 'max_len_finalize');

$que=$db->query('SELECT max_len(a) from strings');
$fe=$que->fetch(PDO::FETCH_NUM);
foreach ($fe as $row)
{
print('number of letters m. ='.$row);
}

//an alternative form to get the result $fe[0]
echo ' '.'num_echo = '.$fe[0];
?>


FORMULARIES AND OTHER THINGS
Now to send formularies is simple, because PDO protects against SQL inyection.
Here we use Prepared Statements, it is also simple when we use a query.
We will use two documents.

1.- Pdo19_Form.html 2._Pdo19_Form.php
The first one calls to the second.

Pdo19_Form.html

<html>
<head>
<title>Pdo-form
<meta http-equiv="" content="text/html"; charset="iso-8859-1" />
</head>
<body>
<form action="Pdo19_Form.php" method="post">
Cliente:
<input type="submit" name="enviar" value="Envíar cliente">
</form
</body>
</html>


The second one is:
Pdo19_Form.php

<?php
try
{
$db = new PDO("sqlite::memory:");

$sqlCreateTable = 'CREATE TABLE clientes(id_cliente INTEGER PRIMARY KEY AUTOINCREMENT, nom_cliente VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);

$inserto="INSERT INTO clientes(nom_cliente) VALUES (?)";
$preSt = $db->prepare($inserto);
$preSt->bindParam(1,$nomCliente);

$nomCliente= $_POST["cliente"];
$preSt->execute();

$sql = "SELECT * FROM clientes";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('nombre: '. $row['nom_cliente'] . ' ');
}
echo $_POST["cliente"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>


We can use directly the value of $__POST, without fear,because PDO will analyze it and send the chain as a string.
We will use a prepare with "placeholders", these are the ? in the where , followed by the bindParam
These
placeholders will be replaced by the assigned values on executing execute().
For modifying you need only to change the $inserto:
UPDATE Clientes SET Nombre=? WHERE idCliente=?

Extend PDO class
It is possible to extend PDO and create our own class.
Here we add $this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);.
And a que() function.

Also we extend PDOStatement.


<?php
$db = new PDOEx('sqlite:fieras.s3db');
echo "abierta"." ";

class PDOEx extends PDO {
function __construct($dsn) {
parent::__construct($dsn);
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('DBStatement', array($this)));
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
function que()
{
$sel ='SELECT * FROM animals';
$que1=$this->query($sel,PDO::FETCH_ASSOC);
foreach($que1 as $rowa)
print($rowa['id_animal']."----------". $rowa['id_name']."
");
}

}
class DBStatement extends PDOStatement {
public $dbh;
protected function __construct($dbh) {
$this->dbh = $dbh;
}

}
try
{
$db->que();
/*** cerrar la conexión ***/
$db = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

REFERENCIAL INTEGRITY USING TRIGGERS
SQLite does not supports referencial integrity between tables in a database, but it supports triggers.
Using triggers we can implement
referencial integrity.

Let us use the database films3.s3db, that has three tables:

Genero: Like tragedy, commedy,western etc.
Soporte: Like film, MP3, etc.
Peliculas: Tittles of films.

The table PELICULAS has deppendencies form GENERO and SOPORTE.
The triggers before insert and before update in Peliculas account for these two dependencies.

The trigger film_pel_in before insert on peliculas does not permit to insert a pelicula adjudicating it a Genero or Soporte not existent.

The trigger film_pel_up before update on peliculas
does not permit to modify a pelicula adjudicating it a Genero or Soporte not existent.

We need also two more triggers
The trigger film_sop_de before delete on soporte
does not permit to delete a soporte that has peliculas.
El trigger film_gen_de before delete on genero
does not permit to delete a genero that has peliculas.

If the dependencies were of more tables, it would only be necessaire to add more OR in INSERT AND UPDATE and the corresponding triggers on delete.
If the deppendence were of only one table you would quit the OR and the delete not necessary.

The following code would create the database, tables, fields and triggers.

<?php
$db = new PDO("sqlite:films.s3db");

CREATE TABLE [genero] (
[id_gen] INTEGER PRIMARY KEY NOT NULL,
[nombreGen] VARCHAR(40) UNIQUE NOT NULL
);

CREATE TABLE [soporte] (
[id_sop] INTEGER NOT NULL PRIMARY KEY,
[nombreSop] VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE [peliculas] (
[id_pel] INTEGER NOT NULL PRIMARY KEY,
[id_genero] INTEGER NOT NULL,
[id_soporte] INTEGER NOT NULL,
[nombrePel] VARCHAR(40) NOT NULL
);

CREATE TRIGGER film_pel_in before insert on peliculas
for each row begin
select case
when
(
(new.id_soporte is not null)
and
(new.id_genero is not null)
and
(
((select id_sop from soporte where id_sop = new.id_soporte) is null)
or
((select id_gen from genero where id_gen = new.id_genero) is null)
)
)
then raise(abort,'Pelidula not inserted')
end;
end;

CREATE TRIGGER film_pel_up before update on peliculas
for each row begin
select case
when
(
((select id_sop from soporte where id_sop = new.id_soporte) is null)
or
((select id_gen from genero where id_gen = new.id_genero) is null)
)
then raise(abort,'Pelicula not updated')
end;
end;

CREATE TRIGGER film_sop_de before delete on soporte
for each row begin
select case
when
((select id_soporte from peliculas where id_soporte = old.id_sop) is not null)
then raise(abort,'Soporte not deleted')
end;
end;

CREATE TRIGGER film_gen_de before delete on genero
for each row begin
select case
when
((select id_genero from peliculas where id_genero = old.id_gen) is not null)
then raise(abort,'Genero not deleted')
end;
end;
?>