Autore: Fabio Di Matteo
Ultima revisione: 03/07/2014 - 10:55

Repository Git

sPDO e' una semplice classe che consente di sviluppare con PDO in maniera semplice e veloce.
La classe in questione non tende a nascondere i metodi e le funzioni del driver PDO (richiamabili mediante strutture pubbliche alla stessa) piuttosto ne aggiunge pochi altri per rendere il lavoro piu' veloce.

La classe si presenta con i seguenti metodi pubblici:

include_once("lib/sPDO.php");
 
$mydb = new sPDO();
 
//mostriamo tutti i record di una tabella
$mydb->connect();
if ( !$mydb->execute("select * from users ")) echo "problemi nella query<br>";
 
for ($i=1; $i<= $mydb->nrows(); $i++)
{
	echo $mydb->read($i, "user") ." - ". $mydb->read($i, "pass") . "<br>" ;
}
$mydb->disconnect();

Da notare l'utilizzo di read(): $mydb→read([numero riga], [nome campo]) . Le righe vengono numerate a partire da 1.

include_once("lib/sPDO.php");
 
$mydb = new sPDO();
 
//inseriamo qualche record
$mydb->connect();
$sql="INSERT INTO  `users` ( `user` ,`pass` , `type` ) VALUES ( 'utente' , '365816905f5e9c148e20273719fe163d' , 1 );" ;
if ( !$mydb->execute($sql)) echo "problemi nella insert <br>";
$mydb->disconnect();

include_once("lib/sPDO.php");
 
$mydb = new sPDO();
 
//cancelliamo qualche record
$mydb->connect();
$sql="DELETE  from `users` WHERE  `id` >3;" ;
if ( !$mydb->execute($sql)) echo "problemi nella delete <br>";
$mydb->disconnect();

include_once("lib/sPDO.php");
 
$mydb = new sPDO();
//modifichiamo un record
$mydb->connect();
$sql="UPDATE  `users` SET  `user` =  'test0',
						   `pass` =  '098f6bcd4621d373cade4e832627b4f6' 
				WHERE  `id` =3;";
if ( !$mydb->execute($sql)) echo "problemi nella update <br>";
$mydb->disconnect();

function query2select($sql,$id,$field)
{
	$ubb = new sPDO();
	$ubb->connect();
	$ubb->execute($sql);
	for ($i=1; $i<= $ubb->nrows(); $i++)
	{
		$res=$res.'<option value="'.$ubb->read($i, $id).'">'.$ubb->read($i, $field).'</option>';
	}
	$ubb->disconnect();
	return $res;
}

La si utilizza così:

echo query2select("select nome from miatabella","id","nome")

la funzione riempira' la select di <option>.
$id è la chiave primaria e $field il campo da mostrare come etichetta della <option> .

Come si potra' notare dal codice è possibile interagire direttamente con le strutture di PDO tramite il secondo gruppo di variabili pubbliche. (utilizzo_diretto_di_pdo)

var $dbh;  //puntatore al alla connessione
var $sql;  //stringa sql
var $stmt; //lo statement 
var $row;  // la riga pdo in output

spdo.php

<?php
/*
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU LESSER GENERAL PUBLIC LICENSE as published 
 * by the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU LESSER GENERAL PUBLIC LICENSE for more details.
 * 
 * You should have received a copy of the GNU LESSER GENERAL PUBLIC LICENSE
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 * MA 02110-1301, USA.
 * 
 */
 
 
 
 
 
 
class sPDO
{
//--Edit to fit your needs
var $DSN =  'mysql:host=localhost;dbname=miodb' ;    
var $DBUSER = 'root';
var $DBPASS = 'mypassword';
//---------------------------------
 
 
 
//You can access, if you will, to the normal structures of PDO using these members
var $dbh;  //connection handler
var $sql;  //sql string
var $stmt; //statement 
var $row;  //output row
 
var $nrows; //number of rows affected by select;
var $error; // a string for errors
 
 
 
function connect()
{
	try
	{
		$this->dbh = new PDO($this->DSN, $this->DBUSER, $this->DBPASS);
 
        //Verbosity errors level
        $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	}
 
	catch (PDOException $myerror)
	{
		print "Problemi nella connessione al database: <br>" . $myerror->getMessage() . "<br/>";
		$this->error=$myerror->getMessage();
	}
}
 
function execute($sql)
{
	$this->sql=$sql;
 
	$this->stmt = $this->dbh->prepare($this->sql);
 
	//Run query
    $state = $this->stmt->execute();
 
    //Update the variable containing the number of records affected by the last select
	if ($this->isSelect($this->sql))
	{
		$this->nrows=0;
		while ($this->row = $this->stmt->fetch(PDO::FETCH_BOUND)) $this->nrows++ ;
    }
 
    return $state;
}
 
function quote($sql)
{
	return $this->dbh->quote($sql);
}
 
 
function isSelect($sql)
{
	$a=trim($sql);
	$a=split(" ",$a);
	$res = FALSE;
	if (strcasecmp($a[0], "select")==0 ) $res = TRUE;
	return $res;
}
 
 
function nrows()
{
	return $this->nrows;
}
 
 
function read($i, $coloumn)
{
	 $c=0;
	 foreach ($this->dbh->query($this->sql) as $this->row) 
	 {
            $c++;
	    if ($c==$i)	return $this->row[$coloumn];
         }
}
 
 
function lastInsertId()
{
	return $this->dbh->lastInsertId();
}
 
function GetDriverName()
{
	 $res=split(":", $this->DSN, 2);
	 return $res[0];
}
 
function addslashes($str)
{
	if ($this->GetDriverName()=="sqlite") $res=sqlite_escape_string($str);
	if ($this->GetDriverName()=="sqlite2") $res=sqlite_escape_string($str);
	if ($this->GetDriverName()=="sqlite3") $res=sqlite_escape_string($str);
	if ($this->GetDriverName()=="mysql") $res=addslashes($str);
	if ($this->GetDriverName()=="mysqli") $res=addslashes($str);
        //if ($this->GetDriverName()=="mysql") $res= mysql_real_escape_string($str);
	//if ($this->GetDriverName()=="mysqli") $res= mysql_real_escape_string($str);
        if ($this->GetDriverName()=="pgsql") $res=pg_escape_string($str);
	return $res;
}
 
 
function countRecordFromTable($table)
{
	$count = current($this->dbh->query("select count(*) from $table")->fetch());
	return $count;
}
 
 
 
function disconnect()
{
	$this->dbh=null;
	$this->nrows=0;
	$this->row=null;
	$this->stmt=null;
	$this->error="";
}
 
 
}//class
 
?>

Come gia' detto i principali componenti della classe PDO sono “esposti” come oggetti pubblici ed è quindi anche possibile utilizzare le classiche tecniche PDO per la modifica dei record(consigliate). Ecco un esempio:

$db = new sPDO();
$db->connect();
 
$sql =  " UPDATE itcms_pages set
		title = :title, 
		body =  :body,
		menu =  :menu,				
		menuorder = :menuorder				
		where id = :id " ; 											
 
		$db->stmt = $db->dbh->prepare($sql);
		$db->stmt->bindParam(":title", $_POST["title"]);
		$db->stmt->bindParam(":body", $_POST["body"]);
		$db->stmt->bindParam(":menu", $menu);
		$db->stmt->bindParam(":menuorder", $_POST["menuorder"]);
		$db->stmt->bindParam(":id", $_POST["id"]);
 
if (!$db->stmt->execute()) echo 'Errore nella query: '. $sql;
 
$db->disconnect();

<?php 
	$mydb= new sPDO();
	$mydb->DSN =  'sqlite:db.sqlite' ;
	$mydb->connect();
	$sql='CREATE TABLE if not exists "persone" (
						  "id" INTEGER NOT NULL ,
						  "cognome" varchar(1) ,
						  "nome" varchar(256) ,
						  "tel" varchar(256) ,
						  "email" varchar(256) ,
						  PRIMARY KEY ("id") 
						);' ;
	if (!$mydb->execute($sql)) echo 'Problemi nella query';
	$mydb->disconnect();					
?>

<?php 
	$mydb= new sPDO();
	$mydb->DSN =  'sqlite:db.sqlite' ;
	$mydb->connect();
	$sql='Select * from persone;' ;
	if (!$mydb->execute($sql)) echo 'Problemi nella query';
 
	for ($i=1; $i<= $mydb->nrows(); $i++)
	{
		echo $mydb->read($i, "cognome") ." - ".
				$mydb->read($i, "nome") ." - ".
				$mydb->read($i, "tel")." - ".
				$mydb->read($i, "email"). "<br>" ;
	}
	echo '<div style="margin-top:15px">Record totali nella tabella: '.$mydb->countRecordFromTable('persone').'</div>';	
	$mydb->disconnect();
 
?>

<?php 
	$mydb= new sPDO();
	$mydb->DSN =  'sqlite:db.sqlite' ;
	$mydb->connect();
	$sql='INSERT INTO persone (id,cognome,nome,tel,email)VALUES (null,"Cane","Chester","834502","chester@email.it")' ;
	if (!$mydb->execute($sql)) echo 'Problemi nella query';
	$mydb->disconnect();					
?>