PDO example

try {
$pdo = new PDO("mysql:host=127.0.0.1;dbname=dev;","root","" );
#$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$pdo->exec("DELETE FROM `User` WHERE id<>293968 ");

#insert
$insertedRows = $pdo->exec("INSERT INTO `User` (username, password) VALUES ('elvis','ciotti') ");
$lastInsertId = $pdo->lastInsertId();
echo "Inserted $insertedRows rows [id = $lastInsertId]\n";
$pdo->exec("DELETE FROM `User` WHERE id=$lastInsertId ");

#fetch
foreach ($pdo->query("SELECT * FROM `User` ", PDO::FETCH_ASSOC) as $row){
echo "{$row['id']}:{$row['username']}\n";
}

#insert con statement ":"
$stmt = $pdo->prepare("INSERT INTO `User` (username, password) VALUES (:username, :password) ");
$stmt->bindParam(':username',$user);
$stmt->bindParam(':password',$pass);
#
$user='aaaa'; $pass='aa'; $stmt->execute();
$user='bbbb'; $pass='bb'; $stmt->execute();

#insert con statement "?"
$stmt = $pdo->prepare("INSERT INTO `User` (username, password) VALUES (?, ?) ");
$stmt->bindParam(1,$user);
$stmt->bindParam(2,$pass);
#
$user='cccc'; $pass='cc'; $stmt->execute();
$user='dddd'; $pass='dd'; $stmt->execute();

#insert con execute ":"
$stmt = $pdo->prepare("INSERT INTO `User` (username, password) VALUES (:username, :password) ");
$stmt->execute(array(":username"=>"eeee",":password"=>"ee"));

#insert con execute "?"
$stmt = $pdo->prepare("INSERT INTO `User` (username, password) VALUES (?, ?) ");
$stmt->execute(array("ffff","ff"));

#fixed val, no vars
$stmt->bindValue(1,"gggg");
$stmt->bindValue(2,"gg");
$stmt->execute();


$stmpt_all = $pdo->prepare("SELECT * FROM `User` LIMIT 10");
$stmpt_all->execute();
#echo "all: ".print_r($stmpt_all->fetchAll(),1);

$stmpt_all = $pdo->prepare("SELECT id, username, password FROM `User` LIMIT 10");
$stmpt_all->bindColumn(1,$id);
$stmpt_all->bindColumn(2,$user);
$stmpt_all->bindColumn(3,$pass);
$stmpt_all->execute();
echo "fetch: {$stmpt_all->rowCount()} rows: \n";
while($row = $stmpt_all->fetch(PDO::FETCH_BOUND)) { #foreach
echo " [$id][$user][$pass]\n";
}


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

?>

php5 interfaces

PHP5 supports "interfaces".

What is an interface ? Basically, it's a definition of a class type with methods. If you implement an interface, you must write all the methods in that interface.

Example:

Let's suppose there is a "dbConnection" interface, that contains all the methods to db access.

Interface dbConnection
{

public function __construct($host, $user, $pass, $db);
public function query($q);
public function getError();
public function getAffectedRows();
public function nextRow();
/

/[...]

}

Now you can already write an application that use the methods of the interface, regardless of the implementation of the methods.

//application
$db = new MySQL("localhost","user" [...]);
$db->query("select * from table");
while ($row = $db->nextRow())
print "{$row->field1} {$row->field2}"

Let's write the class for MySQL access

class MySQL implements dbConnection
{

private $conn;
public function __construct($host, $user, $pass, $db)
{
$this->conn = mysql_connect($host, $user, $pass);
mysql_select_db($db, $this->conn);
}
//[..

other methods...]
}

And a class for SQLite

class SQLite implements dbConnection
{

private $conn;
public function __construct($host, $user, $pass, $db)
{
$this->conn = sqlite_open($db);
}
//[.. other methods]

}

Now if want to change the application and use SQLite instead of MySQL, you have to modify ONLY the 1st line:

$db = new SQLite("","","","/path/to/file.db");

You don't need to check the rest of the code. MySQL and SQLite classes implements the same interface (and the same methods) !

Interfaces improve your code !