MENU


PDO

PHP Data Objects defines a lightweight, consistent interface for accessing databases in PHP. 

<!DOCTYPE html><html><head></head><body><pre>
<?php

$P = new PDO ("mysql:dbname=test;host=localhost","root","password");
$P->exec("DROP TABLE IF EXISTS tbl;");
$P->exec(" CREATE TABLE tbl (a INT,b VARCHAR(5))");
echo "*****************************************\n";
   
// *** Style 1
$P->exec("INSERT INTO tbl VALUES (1,'abc')");
$P->exec("INSERT INTO tbl VALUES (2,'def')");
$P->exec("INSERT INTO tbl VALUES (3,'ghi')");
$PS = $P->query("SELECT a,b FROM tbl");
foreach ($PS as $row){
   echo $row['a'],",",$row['b'],"\n";
}
echo "*****************************************\n";

// *** Style 2
$PS = $P->prepare("INSERT INTO tbl VALUES (:a,:b)");
$PS->execute([":a"=>4, ":b"=>"jkl"]);
$PS->execute([":a"=>5, ":b"=>"mno"]);
$PS = $P->query("SELECT a,b FROM tbl");
echo "rows:".$PS->rowCount()."   columns:".$PS->columnCount()."\n";
while ($row = $PS->fetch(PDO::FETCH_BOTH)){
   echo $row['a'],",",$row['b'],"\n";
}
echo "*****************************************\n";

// *** Style 3
$PS = $P->prepare("INSERT INTO tbl VALUES (:a,:b)");
echo "PDO errorCode():".$P->errorCode()."\n";
echo "PDO errorInfo():"; print_r($P->errorInfo()); echo "\n";
$PS->bindValue(":a",6,PDO::PARAM_INT); // by value
$PS->bindParam(":b",$b,PDO::PARAM_STR,3);// by reference
echo "PS errorCode():".$PS->errorCode()."\n";
echo "PS errorInfo():"; print_r($PS->errorInfo()); echo "\n";
$b = "pqr";
$PS->execute();
$PS = $P->query("SELECT a,b FROM tbl");
while ($row = $PS->fetchObject()){
   echo $row->a,",",$row->b,"\n";
}
echo "*****************************************\n";

// *** Style 4
$P->beginTransaction(); // disables auto-commit
   $P->exec("INSERT INTO tbl VALUES(7,'stu')"); //rolled back
   $P->exec("INSERT INTO tbl VALUES(8,'vwx')");//rolled back
$P->rollBack();              // re-enables auto-commit
$P->beginTransaction(); // disables auto-commit
   $P->exec("INSERT INTO tbl VALUES(9,'yz')");
$P->commit();               // re-enables auto-commit
$PS = $P->prepare(
                   "SELECT a,b FROM tbl WHERE a > ? AND a<?");
$PS->execute([0,10]);
$PS->bindColumn(1,$a);   // by column number
$PS->bindColumn('b',$b); // by column name
while ($PS->fetch(PDO::FETCH_BOUND)){
   echo "$a,$b\n";
}
?>
</pre></body></html>

*****************************************
1,abc
2,def
3,ghi
*****************************************
rows:5   columns:2
1,abc
2,def
3,ghi
4,jkl
5,mno
*****************************************
PDO errorCode():00000
PDO errorInfo():Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

PS errorCode():
PS errorInfo():Array
(
    [0] => 
    [1] => 
    [2] => 
)

1,abc
2,def
3,ghi
4,jkl
5,mno
6,pqr
*****************************************
1,abc
2,def
3,ghi
4,jkl
5,mno
6,pqr
9,yz
Possible values for the first parameter for $PS->fetch(……)
PDO::FETCH_ASSOC gets an array indexed by the column name.
PDO::FETCH_BOTH gets an array indexed by the column name and the column number.
PDO::FETCH_BOUND assigns the values of the columns in the result set to the PHP variables bound with bindColumn().
PDO::FETCH_CLASS gets an object of the requested class,
PDO::FETCH_INTO updates an existing object of the requested class.
PDO::FETCH_NAMED is similar to PDO:FETCH_ASSOC. If there are columns with the same name, the value referred by that key will be an array of all the values.
PDO::FETCH_NUM gets an array indexed by the column number, starting at column 0.
PDO::FETCH_OBJ gets an object with property names corresponding to the column names returned.


Possible values for the third parameter for
$PS->bindParam(…) and $PS->bindValue(…)
PDO::PARAM_BOOL
PDO::PARAM_NULL
PDO::PARAM_INT
PDO::PARAM_STR
PDO::PARAM_LOB