4.Mart 2017
Two scripts below (dbconn and tbl) are basic skeleton for any CRUD (PHP PDO) program.
Here is only R=Read (with filter and sort) of PDO CRUD, no JS.
Later I shall upload OOP MVC version with CUD of CRUD and JS msg and yesno dialog, but :
code here is first MUST LEARN about PHP PDO CRUD.
You can use any your suitable table for testing and Oracle 11g as I did or MySQL or SQLite.
Code changes are not difficult and are minimal because PDO syntax is used.
I think Mini3 is best learning code for learning OOP MVC.
Simplest JS msg code (from OOP MVC version with CUD which I am testing based on
https://github.com/panique/mini3 which has no JS),
generated in PHP on server side (eg for delete confirmation or debugging as code below) :
// snippet from J:\awww\apl\dev1\papl1\mini3fw\application\Core\Application.php
// see https://github.com/panique/mini3
if (TEST)
{
?><SCRIPT LANGUAGE="JavaScript"><!-- Begin
//alert(t1+"\n"+t2+"\n"+t3+"\n"+t4+"\n"+t5+"\n"+t6+"\n"+txt_srvgen);
alert( '<?php echo str_replace('<br>','\n',str_replace('<br>','\n',
'ctr='.$this->url_controller
.'<br>akc='.$this->url_action
.'<br>akc.params='.json_encode($this->url_params)
));?>'
);
// End --></SCRIPT>
<?php }
<?php
// J:\awww\apl\dev1\inc\db\dbconn_PDOOCI_mer.php
use PDOOCI\PDO as PDO;
define("DSN", "sspc1/XE:pooled;charset=UTF8"); // UTF8 EE8MSWIN1250
define("USR", "uuu"); define("PSW", "uu");
//require_once $_SERVER['DOCUMENT_ROOT'].'/vendor/autoload.php'; //E.Rangel's pdooci in ROOTDIR
require_once $_SERVER['DOCUMENT_ROOT'].'/inc/db/PDO.php'; //or put it in same dir as tbl script
$options = array(PDO::ATTR_PERSISTENT => true);
try{
$db = new PDO(DSN, USR, PSW, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if(TEST) {echo '<b>'.__FILE__.' SAYS:</b><pre>';
echo 'PDOOCIonOCI8 connection successful, DSN='.DSN.', USR='.USR.', PSW='.PSW;
echo '</pre>'; }
}catch (PDOException $ex){
echo '<b>'.'***ERROR dbconn DSN='.DSN.' '.$ex->getMessage().'</b>';
}
<?php
// J:\awww\apl\dev1\pdev1\01info\02izdatnica_tbl.php
// IZDATNICA = INVOICE
defined('TEST') or define('TEST', '1');
defined('DS') or define('DS', DIRECTORY_SEPARATOR);
defined('ROOTURL') or define('ROOTURL',
//$_SERVER['REQUEST_SCHEME'] . ':'.
'//'
. $_SERVER['SERVER_NAME'] //WEBADRESA sspc1, localhost, dev1
.':'.$_SERVER['SERVER_PORT'] );
defined('CSSURL') or define('CSSURL', ROOTURL.'/'.'inc/'.'css/sitemoj.css');
require_once $_SERVER['DOCUMENT_ROOT'].'/inc/db/dbconn_PDOOCI_mer.php';
//require_once 'z_local_dbconn_PDOOCI_mer.php';
// ******************************************
// P a r a m e t e r s
// ******************************************
// Filters are sticky values :
$p_imekup = ''; if (isset($_GET['imekup'])) $p_imekup = $_GET['imekup'];
$p_tipd = 9999; if (isset($_GET['tipd'])) $p_tipd = $_GET['tipd'];
// S o r t clause is sticky value :
$p_sortdml = 'i.SIFRA_TIP_DOC,length(i.BROJ_IZDATNICE) desc,i.BROJ_IZDATNICE desc';
$p_sort = 'SIFRA_TIP_DOC'; // default
if (isset($_GET['sort'])) $p_sort = $p_sortdml = $_GET['sort'];
switch ($p_sort)
{
case 'BROJ_IZDATNICE':
$p_sortdml = 'length(i.BROJ_IZDATNICE) desc,i.BROJ_IZDATNICE desc';
break;
case 'IME_KUPCA':
$p_sortdml = 'k.IME_KUPCA,length(i.BROJ_IZDATNICE) desc,i.BROJ_IZDATNICE desc';
break;
case 'SIFRA_TIP_DOC':
default:
$p_sortdml = 'i.SIFRA_TIP_DOC,length(i.BROJ_IZDATNICE) desc,i.BROJ_IZDATNICE desc';
break;
} // e n d s w i t c h
if (isset($_GET['search']))
{
// ******************************************
// M o d e l
// ******************************************
try
{
//require_once $_SERVER['DOCUMENT_ROOT'].'/inc/db/dbconn_PDOOCI_mer.php';
//require_once 'z_local_dbconn_PDOOCI_mer.php';
$sql = '
SELECT i.BROJ_IZDATNICE, i.datum
, to_char(i.datum,'.'\'RRRR.MM.DD\''.') DATUM_FMT, i.SIFRA_TIP_DOC
, k.ime_kupca ime_kupca, tipd.opis OPIS_DOK
FROM t_izdatnica i, t_kupac k, t_tip_doc tipd
WHERE i.SIFRA_KUPCA = k.SIFRA_KUPCA
AND i.SIFRA_TIP_DOC = tipd.SIFRA_TIP_DOC
AND upper(k.ime_kupca) LIKE upper(:imekup)
AND i.SIFRA_TIP_DOC = decode(:tipd, '.'9999'.',i.SIFRA_TIP_DOC, :tipd)
ORDER BY '.$p_sortdml ; // , i.napomena
$stmt = $db->prepare($sql);
$stmt->bindValue(':imekup', '%'.$p_imekup.'%');
$stmt->bindParam(':tipd', $p_tipd, PDO::PARAM_INT);
if(TEST) {echo '<b>'.__FILE__.' SAYS:</b><pre>';
echo '$sql='; print_r(
str_replace(':tipd', $p_tipd,
str_replace(':imekup','%'.$p_imekup.'%', $sql))
); echo '</pre>';
}
$stmt->execute();
// Each call to PDOStatement::fetch() or PDOStatement::fetchAll() will
// update all the variables that are bound to columns.
$stmt->bindColumn('BROJ_IZDATNICE', $BROJ_IZDATNICE);
$stmt->bindColumn('DATUM_FMT', $DATUM_FMT);
$stmt->bindColumn('IME_KUPCA', $imekup);
//$stmt->bindColumn(3, $imekup);
$stmt->bindColumn('SIFRA_TIP_DOC', $tipd);
$errorInfo = $stmt->errorInfo();
if (isset($errorInfo[2])) {
$error = $errorInfo[2];
}
} catch (Exception $e) {
$error = $e->getMessage();
}
}
$title_tab = 'tblIZDATNICA';
include($_SERVER['DOCUMENT_ROOT'].'/inc/hdr.php');
//include('z_local_hdr.php');
?>
</head>
<body>
<h1>Tablica izdatnica</h1>
<!--h1>PDO Prepared Statement: Binding Output Parameters</h1-->
<?php if (isset($error)) {
echo "<p>$error</p>";
}
// ******************************************
// View P a r a m e t e r s f o r m
// (s e a r c h f o r m)
// ******************************************
?>
<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<fieldset>
<legend>Filter redaka: utipkajte nekoliko znakova pa tipka ENTER
ili gumb "Upit"</legend>
<p>
<label for="imekup">Ime kupca </label>
<input type="text" name="imekup" id="imekup"
value="<?= $p_imekup ?>" autofocus
placeholder="Filter redaka upita...">
<label for="tipd">Tip izdatnice </label>
<select name="tipd" id="tipd" value="<?= $p_tipd ?>">
<?php
echo "<option value='9999'"; if ($p_tipd == '9999') echo ' selected';
echo '>' . 'SVI TIPOVI IZDATNICA' . '</option>';
echo "<option value='4'"; if ($p_tipd == '4') echo ' selected';
echo '>'.'Izdatnice'.'</option>';
echo "<option value='8'"; if ($p_tipd == '8') echo ' selected';
echo '>'.'MP zaklučci'.'</option>';
/*
for ($p = 1; $p <= 11; $p+=1) {
echo "<option value='$p'";
if ($p == 4) { echo ' selected'; }
echo '>' . number_format($p) . '</option>';
}
*/
?>
</select>
<input type="submit" name="search" value="Upit">
</p>
<?php if (TEST) echo 'Redosljed '.$p_sortdml; ?>
</fieldset>
</form>
<?php
if (isset($_GET['search']))
{
// ******************************************
// View t a b l e
// ******************************************
$stmt->fetch(PDO::FETCH_BOUND);
if ($imekup)
{
// http://dev1:8083/z30GB/02_test/08pdo/01dpowers/02izdatnica_tbl.php
// ?search=Upit&imekup&tipd=9999&sort=BROJ_IZDATNICE
?>
<table>
<tr>
<th>RBR</th>
<th><a href="<?php $p_sort = 'SIFRA_TIP_DOC';
echo $_SERVER['PHP_SELF']
.'?search=Upit'
.'&imekup='.$p_imekup
.'&tipd='.$p_tipd
.'&sort='.$p_sort;
?>">TIP</a>
</th>
<th><a href="<?php $p_sort = 'BROJ_IZDATNICE';
echo $_SERVER['PHP_SELF']
.'?search=Upit'
.'&imekup='.$p_imekup
.'&tipd='.$p_tipd
.'&sort='.$p_sort;
?>">BR.IZD.</a>
</th>
<th>DATUM</th>
<th><a href="<?php $p_sort = 'IME_KUPCA';
echo $_SERVER['PHP_SELF']
.'?search=Upit'
.'&imekup='.$p_imekup
.'&tipd='.$p_tipd
.'&sort='.$p_sort;
?>">IME KUPCA</a>
</th>
</tr>
<?php
$ii = 1;
do
{ ?>
<tr>
<td><?= $ii++; ?></td>
<td><?= number_format($tipd); ?></td>
<td><b><?= $BROJ_IZDATNICE; ?></b></td>
<td><?= $DATUM_FMT; ?></td>
<td><?= $imekup; ?></td>
</tr>
<?php
} while ($stmt->fetch(PDO::FETCH_BOUND));
?>
</table>
<?php
} else {
echo '<p>No results found.</p>';
}
}
?>
</body>
</html>
2.Sept.2015 Download : click HOME link on this page top and see site_ver2.rar from my article 9.
I. Testing is possible with code below :
– uncomment lines // PHP PDO
– comment lines // E.Rangel’s PDOOCI and code beginning with
use chcons as cnf; (before $c1 = DB_DSN;)
E.Rangel’s PDOOCI works ok same as PHP PDO which is experimental.
PDO OCI is PDO sintax with OCI8 DBI interface.
-
<?php
// http://dev1:8083/test/t_oci8/undergr/pdo.php
// H:\dev_web\htdocs\test\t_oci8\undergr\pdo.php
// ******************************************
use chcons as cnf;
$confglob_dir = realpath($_SERVER['DOCUMENT_ROOT'].'/../inc');
if (!defined('DS')) define('DS',DIRECTORY_SEPARATOR);
//require_once($confglob_dir.DS.'confglob.php');
// 1. v a r i a b l e s & f n s :
require_once($confglob_dir.DS.'utl'.DS.'utls.php');
cnf\chcons::ini(__FILE__ // $idxfle
,dirname(dirname(__DIR__)) . DS.'index.php'); // $aplfle=test dir
// 2. d b c o n n p a r a m s :
require_once(CONFGLOB_DIR.DS.'confglobcondb.php');
// ******************************************
$c1 = DB_DSN;
//$c1 = 'oci:dbname=sspc/XE:pooled';
//$c1 = 'oci:dbname=localhost/XE:pooled';
//$c1 = 'oci:dbname=127.0.0.1/XE:pooled';
$c2 = SCHEMA;
//$c2 = 'hr';
$c3 = PASSWORD;
//$c3 = 'hr';
$opt = array(PDOP1.' => '.PDOP1VAL) ; // PDO parameter 1
//$opt = array(PDO::ATTR_PERSISTENT => TRUE) ;
?>
<!DOCTYPE html>
<!-- saved from url=(0075)http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html -->
<html>
<head><meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<style type="text/css">
<!--
/*
J:\awww\apl\inc\cssfmt\style01.css
font-family: Verdana, Tahoma, Arial, Helvetica, sans-serif;
font-style: normal; "Trebuchet MS"
*/
body {
padding: 10px;
color:black; background-color: #F0F0F0;
font-size: 16px;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
font-weight: normal; text-align: justify;
}
/* p, li {
font-size: 16px;
}*/
p.group {color:#3366cc}
p#pla {color:blue} /* <-- ZADNJI P R E G A Z I
p r ethodne istoimene */
p#lju {color:magenta}
p#zel {color:green}
img {
float:right;
margin-right:10px
}
table {
margin: auto;
/* background-color: #FFFFFF; */
border-collapse: collapse;
border-style: solid; border-color: lightblue;
border-width: 1px 3px 3px 1px; /* T,R,D,L smedja= #936709*/
/* border-color: #000000; border: 0; */
margin: 5px;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
td, th {
/* border: solid #000000 1px; */
/* text-align: left; */
padding: 2px;
height: 20px;
/* max-width: 300px; */
}
.maintable {
border: 2px solid #376EAB;
}
.parameter {
font-weight: bold; color: #6586AC;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
h1 {
font-size: 20px;
color: #A5663D; /* 7A7272=siva */
font-weight: normal;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
h2 {
font-size: 18px;
font-weight: bold;
color: #303030;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
h3 {
font-size: 16px;
font-weight: bold;
color: #2B5885;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
div {
margin: 5px;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
div.settings {
margin-right: 0;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
a:link, a:visited, a:active {
color: #294F75;
text-decoration: none;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
a:hover {
color: #182634;
text-decoration: underline;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
.txtBody {
font-size: 16px;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
font-style: normal; font-weight: normal;
}
.txtBody18px {
font-size: large; font-style: normal; font-weight: normal;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
.mainTitle {
font-family: Impact;
font-size: 24px;
}
.mnuMainH {
text-align: center;
cursor: pointer;
cursor: hand;
color: #294F75;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
.imgPlusMinus {
float : left
/* cursor: pointer;
cursor: hand;
height: 14px; */
}
.NotDisp {
display: none;
}
.hdr_open_close_section {
color: #000;
background-color:#E8E8E8; /* CCC=svjetlosiva E8E8E8=jos vise svjetlosiva */
font-weight: bold;
font-family: Corbel, Constantia, Arial, 'Helvetica', sans-serif;
}
.pgBreakAfter {
cursor: text;
filter: Gray;
page-break-after: always;
}
.pgBreakBefore {
cursor: text;
filter: Gray;
page-break-before: always;
}
/* <span class="pgBreakAfter"> </span> */
-->
</style>
</head>
<body>
<table border="0" align="center"
cellpadding="3" cellspacing="1">
<tr>
<td width="700px" colspan="1" valign="top">
<?php
// -------------------------------------------------
echo '<h1>I. ORACLE 11g XE PERSISTENT DB CONNECTION POOLED
<br /> (ESTABLISHED WITH PHP PDO OR E.Rangel\'s PDOOCI) </h1>';
// -------------------------------------------------
//$c1 = 'oci:dbname=localhost/XE:pooled'; $c2 = 'hr'; $c3 = 'hr';
//$c2 = 'mercedes'; $c3 = 'm1';
try {
$dbh = new PDO($c1, $c2, $c3);
} catch (PDOException $e) {
echo "*****Error, naredba:<br /><br /> \$dbh = new PDO('$c1', '$c2', '$c3');"
, '<br /><br />', $e->getMessage()
;
exit;
}
echo '<ol>';
echo '<li><pre>';
echo <<< 'EOTXT'
// ******************************************
use chcons as cnf;
$confglob_dir = realpath($_SERVER['DOCUMENT_ROOT'].'/../inc');
if (!defined('DS')) define('DS',DIRECTORY_SEPARATOR);
//require_once($confglob_dir.DS.'confglob.php');
// 1. v a r i a b l e s & f n s :
require_once($confglob_dir.DS.'utl'.DS.'utls.php');
cnf\chcons::ini(__FILE__ // $idxfle
,dirname(dirname(__DIR__)) . DS.'index.php'); // $aplfle=test dir
// 2. d b c o n n p a r a m s :
require_once(CONFGLOB_DIR.DS.'confglobcondb.php');
// ******************************************
$c1 = DB_DSN; // E.Rangel's PDOOCI
//$c1 = 'oci:dbname=sspc/XE:pooled'; // PHP PDO
//$c1 = 'oci:dbname=localhost/XE:pooled';
//$c1 = 'oci:dbname=127.0.0.1/XE:pooled';
$c2 = SCHEMA; // E.Rangel's PDOOCI
//$c2 = 'hr'; // PHP PDO
$c3 = PASSWORD; // E.Rangel's PDOOCI
//$c3 = 'hr'; // PHP PDO
$opt = array(PDOP1.' => '.PDOP1VAL) ; // PDO parameter 1 // E.Rangel's PDOOCI
//$opt = array(PDO::ATTR_PERSISTENT => TRUE) ; // PHP PDO
EOTXT;
echo '</pre>';
echo '<li>';
echo "Connected to user: ************ \$dbh =
<br />new PDO('$c1', '$c2', '$c3');";
//$c1 = 'oci:dbname=localhost/XE'; //$c2 = 'mercedes'; $c3 = 'm1';
try {
$dbh = new PDO($c1, $c2, $c3, $opt);
} catch (PDOException $e) {
echo "*****Error, naredba:<br /><br /> \$dbh = new PDO('$c1', '$c2', '$c3', '\$opt');"
, '<br /><br />', $e->getMessage()
;
exit;
} // ."\n"
echo "<li> PERSISTENT Connected to user: ************\$dbh =
<br />new PDO('$c1', '$c2', '$c3', array(PDO::ATTR_PERSISTENT => TRUE));";
echo '<br />   $opt = array(PDO::ATTR_PERSISTENT => TRUE) = ';
print_r($opt); //echo '</pre>';
echo '</ol>';
//
echo '<br/>';
echo '<ol>';
echo '<li>';
$pdodriver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME);
if ($pdodriver == 'mysql') {
echo "Running on mysql; doing something mysql specific here\n";
} else echo 'PDO driver: $dbh->getAttribute(PDO::ATTR_DRIVER_NAME) = '
. $pdodriver;
echo '<li>';
echo ''.'not supported for OCI PDO: '
. '$dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS);';
//echo 'Connection Status ' . $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS);
echo '<li>';
echo 'DB Server Version $dbh->getAttribute(PDO::ATTR_SERVER_VERSION) = <br />'
. $dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
echo '<li>';
echo '$dbh->getAttribute(PDO::ATTR_SERVER_INFO) = <br />'
. $dbh->getAttribute(PDO::ATTR_SERVER_INFO);
echo '<li>';
echo 'Client Library Server Ver. $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION) = '
. $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION);
echo '</ol>';
//
$dbh->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
function nextidrec($dbh, $query) {
$sth = $dbh->prepare($query);
$sth->execute();
$row = $sth->fetch(PDO::FETCH_NUM);
return $row[0] + 1;
}
function countrec($dbh, $query) {
$sth = $dbh->prepare($query);
$sth->execute(array('usr2'));
$row = $sth->fetch(PDO::FETCH_NUM);
return $row[0];
}
echo '<br/>';
// -------------------------------------------------
echo '<h1>II. PDO CRUD DML (QUERY)</h1>';
// -------------------------------------------------
//$db = new PDO("DSN");
// -------------------------------------------------
echo '<h3>II.1 max ID & C R E A T E with bind variables - prepare, execute, fetch</h3>';
// -------------------------------------------------
echo '<ol>';
// 11111111111
echo '<li>';
$sth = $dbh->prepare("DELETE T_WEBUSER");
$sth->execute();
print 'DELETE T_WEBUSER table';
// 22222222222
echo '<li>';
$countrec = countrec($dbh, "SELECT count(*) from T_WEBUSER");
print 'SELECT count(*) from T_WEBUSER, $row = array with NUMERIC KEYS ';
echo '<br />$countrec in table = '. $countrec;
if($countrec == 0) $nextidrec = 1;
else {
$nextidrec = nextidrec($dbh, "SELECT max(MEMBER_ID) from T_WEBUSER");
}
// 3333333333333
echo '<li>';
echo '$nextidrec (from DB records, if=1 this is first record in table - unknown sequence) = '. $nextidrec;
// 444444444444
echo '<li>';
echo '$sth = $dbh->prepare(\'INSERT INTO T_WEBUSER
(UNAME, PASSWORD)
VALUES(?,?)\'
) <br />// and same for usr2, BUT NOT MEMBER_ID - TRIGGER !!'; ;
$sth = $dbh->prepare('INSERT INTO T_WEBUSER
(UNAME, PASSWORD)
VALUES(?,?)'
);
$sth->execute(array('usr1','pswusr1'));
$sth = $dbh->prepare("INSERT INTO T_WEBUSER
(UNAME, PASSWORD)
VALUES(?,?)");
$sth->execute(array('usr2','pswusr2'));
echo '</ol>';
// -------------------------------------------------
echo '<h3>II.2 U P D A T E with bind variable</h3>';
// -------------------------------------------------
echo '<ol>';
// 11111111111
echo '<li>';
echo 'UPDATE T_WEBUSER SET FORENAME = \'FORENAME\' where UNAME = ?';
$sth = $dbh->prepare("UPDATE T_WEBUSER SET FORENAME = 'FORENAME' where UNAME = ?");
$sth->execute(array('usr1'));
// 2222222222222
echo '<li>';
echo 'All rows: SELECT * FROM T_WEBUSER';
$sth = $dbh->prepare( "SELECT * FROM T_WEBUSER" );
$sth->execute();
echo "<table border='1'>\n";
while (($row = $sth->fetch(PDO::FETCH_ASSOC)) != false) {
echo " <tr>";
echo " <td>".htmlentities($row['MEMBER_ID'])."</td>";
echo " <td>".htmlentities($row['UNAME'])."</td>";
echo " <td>".htmlentities($row['PASSWORD'])."</td>";
echo " <td>".htmlentities($row['FORENAME'])."</td>";
echo " </tr>";
}
echo "</table>\n";
echo '</ol>';
// -------------------------------------------------
echo '<h3>II.3 D E L E T E with bind variable</h3>';
// -------------------------------------------------
echo '<ol>';
// 11111111111
echo '<li>';
echo 'DELETE T_WEBUSER where UNAME = ?';
$sth = $dbh->prepare('DELETE T_WEBUSER where UNAME = ?');
$sth->execute(array('usr2'));
echo '</ol>';
// -------------------------------------------------
echo '<h3>II.4 R E T R I V E with bind variable</h3>';
// -------------------------------------------------
echo '<ol>';
// 11111111111
echo '<li>';
echo 'All rows: SELECT * FROM T_WEBUSER WHERE UNAME=? (? means bind variable value)';
$sth = $dbh->prepare( "SELECT * FROM T_WEBUSER WHERE UNAME=? or UNAME=?" );
$sth->execute(array('usr1', 'usr2'));
//$row = $sth->fetch(PDO::FETCH_ASSOC);
//echo '<pre>'; print_r($row); echo '</pre>';
echo "<table border='1'>\n";
while (($row = $sth->fetch(PDO::FETCH_ASSOC)) != false) {
echo " <tr>";
echo " <td>".htmlentities($row['MEMBER_ID'])."</td>";
echo " <td>".htmlentities($row['UNAME'])."</td>";
echo " <td>".htmlentities($row['PASSWORD'])."</td>";
echo " <td>".htmlentities($row['FORENAME'])."</td>";
echo " </tr>";
}
echo "</table>\n";
// 2222222222
echo '<li>';
echo 'One row: SELECT * FROM T_WEBUSER WHERE UNAME=? (? means bind variable value)';
$sth = $dbh->prepare( "SELECT * FROM T_WEBUSER WHERE UNAME=?" );
$sth->execute(array('usr1'));
$row = $sth->fetch(PDO::FETCH_ASSOC);
echo '<pre>'; print_r($row); echo '</pre>';
echo '</ol>';
/*
$dbh->exec("INSERT INTO T_WEBUSER (MEMBER_ID, UNAME, PASSWORD)
VALUES(null,'usr1','usr1')");
echo '<pre>2. $dbh->exec("UPDATE T_WEBUSER SET FORENAME = \'FORENAME\' where MEMBER_ID = 1");</pre>';
$dbh->exec("UPDATE T_WEBUSER SET FORENAME = 'FORENAME' where MEMBER_ID = 1");
echo "<br/>3. SELECT * FROM T_WEBUSER where MEMBER_ID in (1)<br/>";
echo '<pre>3.1 Array Fetching
==============';
$sth = $dbh->prepare("SELECT * FROM T_WEBUSER order by MEMBER_ID desc");
$sth->execute();
//moze i ovako: $sth = $dbh->query("SELECT * FROM T_WEBUSER order by MEMBER_ID desc");
//while
($row = $sth->fetch(PDO::FETCH_NUM));
print '<br/>1. FETCH_NUM $row == array with NUMERIC KEYS ';
print_r($row);
//while
($row = $sth->fetch(PDO::FETCH_ASSOC));
print '3.2 FETCH_ASSOC $row == array with associated (STRING) KEYS ';
print_r($row);
//while
($row = $sth->fetch(PDO::FETCH_BOTH));
print '3.3 FETCH_BOTH row == array with ASSOCIATED & NUMERIC KEYS ';
print_r($row);
echo '<pre>2. Fetch as String - column
===============';
//$sth = $dbh->prepare("SELECT opis FROM T_WEBUSER order by MEMBER_ID desc");
//$sth->execute();
if ($opis = $sth->fetchColumn(0) . ' i ' . $sth->fetchColumn(1)) {
print '$sth->fetchColumn(0) i $sth->fetchColumn(1) vrati string:<br/>';
print_r($opis); }
// fetch data contained within single column.
//$u = $db->query("SELECT users WHERE login='login' AND //password='password'");
// // fetch(PDO::FETCH_COLUMN)
//if ($u->fetchColumn()) { // returns a string
// // login OK
//} else { //authentication failure
//}
echo '<pre>3. Fetch as Standard Object
========================';
//while
($row = $sth->fetch(PDO::FETCH_OBJ));
print 'FETCH_OBJ fetch row as instance of stdClass
where column name == property name $row->MEMBER_ID=' . $row->MEMBER_ID .'<br/>';
print_r($row);
*/
//echo '<pre>4. Fetch Into a Class
//==================';
// ? PDO allows the sthult to be fetched into a class type of your choice.
//
//$sth = $db->query("SELECT * FROM foo");
//$sth->setFetchMode(
// PDO::FETCH_CLASS,
// "className",
// array('optional'='Constructor Params')
//);
//while ($obj = $sth->fetch()) {
// // $obj == instance of className
//}
//
//? PDO allows query result to be used to determine destination class.
//
//$sth = $db->query("SELECT * FROM foo");
//$sth->setFetchMode(
// PDO::FETCH_CLASS |
// PDO::FETCH_CLASSTYPE
//);
//while ($obj = $sth->fetch()) {
// // $obj == instance of class who's name is
// // found in the value of the 1 st column
//}
//echo '<pre>5. Fetch Into an Object
//====================';
//
//? PDO even allows retrieval of data into existing object.
//
//$u = new userObject;
//$sth = $db->query("SELECT * FROM users");
//$sth->setFetchMode(PDO::FETCH_INTO, $u);
//while ($sth->fetch()) {
// // will re-populate $u with row values
//}
//
//echo '<pre>6. Result Iteration
//=================';
//
//? PDOStatement implements Iterator interface, which allows for
// a method-less result iteration.
//
//$sth = $db->query(
// "SELECT * FROM users",
// PDO::FETCH_ASSOC
//);
//foreach ($sth as $row) {
// // $row == associated array representing row's values.
//}
//
//
//
//
//echo '<pre>7. Lazy Fetching
//=============';
//
//? Lazy fetches returns a result in a form object,
// but HOLDS OF POPULATING PROPERTIES UNTIL THEY ARE ACTUALLY USED.
//$sth = $db->query(
// "SELECT * FROM users",
// PDO::FETCH_LAZY
//);
//foreach ($sth as $row) {
// echo $row['name']; // only fetch name column
//}
//
//echo '<pre>8. fetchAll()
//===========';
//? retrieval of all results from query right away (handy for templates)
//? Can be memory intensive for large results sets!
//
//$qry = "SELECT * FROM users";
//$sth = $db->query($qry)->fetchAll(PDO::FETCH_ASSOC);
// $sth == array of all result rows, where each row is an associated array.
//echo '<pre>9. Callback Function
//==================';
//
//? fetch mode where each result is processed via a callback function.
//
//function draw_message($subject,$email) { _ }
//
//$sth = $db->query("SELECT * FROM msg");
//$sth->fetchAll(PDO::FETCH_FUNC, "draw_message");
/*
echo '<pre>$dbh->exec("DELETE T_WEBUSER where MEMBER_ID in(1,2)");</pre>';
$dbh->exec("DELETE T_WEBUSER where MEMBER_ID in(1,2)");
*/
echo '<br/>'.'<br/>'.'<br/>'.'<br/>';
echo <<<'EOTXT'
<pre>
</pre>
EOTXT;
?>
<h1>III. PHP Data Objects Layer (PDO) - Ilia Alshanetsky</h1>
<p> Written in C - FAST! Designed to make use of all PHP 5.1 features </p>
<p>What Databases are Supported?<br>
? At time ~ PHP 5.1 PDO offered following drivers:<br>
? MySQL 3,4,5 (depends on client libs) <br>
? PostgreSQL<br>
? SQLite 2 & 3 <br>
? ODBC<br>
? DB2<br>
<strong>? Oracle - 2015 year still experimental</strong><br>
? Firebird<br>
? FreeTDS/Sybase/MSSQL</p>
<h2>Installing PDO</h2>
<p> ? PDO is divided into two components <br>
? CORE (provides the interface) <br>
? DRIVERS (access to particular database) - Example: pdo_mysql<br>
? The CORE is enabled by default, <br>
drivers with the exception of pdo_sqlite are not. <br>
Actual Install Steps :<br>
? PECL Way<br>
? pecl install pdo_[driver_name] <br>
? Update php.ini and add<br>
extension=pdo_[driver_name].so (or .dll on win32) <br>
? Built into PHP Built<br>
? ./configure -with-pdo-[driver_name]<br>
? For Win32 dlls for each driver are available.</p>
<h2>Using PDO - connection<br>
</h2>
<p><strong>// Oracle <br>
$dbh =
new PDO('oci:dbname=sspc/XE:pooled', 'hr', 'hr', array(PDO::ATTR_PERSISTENT => TRUE));</strong></p>
<p> // MySQL <br>
new PDO('mysql:host=localhost;dbname=testdb', $login, $passwd);</p>
<p>// PostgreSQL<br>
new PDO('pgsql:host=localhost port=5432 dbname=testdb user=john password=mypass');</p>
<p>// SQLite<br>
new PDO('sqlite:/path/to/database_file');</p>
<h2>What if the Connection Fails?</h2>
<p> ? As is the case with most native PHP objects, <br>
instantiation failure lead to an exception being thrown.<br>
try {<br>
$db = new PDO(_);<br>
} catch (PDOException $e) {<br>
echo $e->getMessage();<br>
}<br>
</p>
<h2>Persistent Connections</h2>
<p> ? Connecting to complex DB like Oracle is a slow process, <br>
it would be nice to re-use a previously opened connection.</p>
<p>$opt = array(PDO::ATTR_PERSISTENT => TRUE) ;<br>
try {<br>
$db = new PDO("dsn", $l, $p, $opt);<br>
} catch (PDOException $e) {<br>
echo $e->getMessage();<br>
}</p>
<h2>DSN INI Tricks<br>
</h2>
<p> ? The DSN string can be an INI setting <br>
you can "name" as many DSNs are you like.<br>
<br>
ini_set("pdo.dsn.ilia", "sqlite::memory");<br>
try {<br>
$db = new PDO("ilia");<br>
} catch (PDOException $e) {<br>
echo $e->getMessage();<br>
}<br>
</p>
<h1>Queries</h1>
<p>? Query execution in PDO can be done in two ways :</p>
<p><strong>? Prepared Statements (recommended for speed & security) - bind variables</strong><br>
? Direct Query Execution</p>
<h1>Prepared Statements - prepare, execute, fetch</h1>
<p>? Compile once, execute as many times as you want.<br>
? Clear SEPARATION BETWEEN STRUCTURE AND INPUT, which prevents SQL injection. <br>
? Often faster then query()/exec() even for single runs.</p>
<p>$stmt = $db->prepare( "SELECT * FROM users WHERE id=?" );<br>
$stmt->execute(array($_GET['id']));<br>
$stmt->fetch(PDO::FETCH_ASSOC);</p>
<p>Bound Parameters<br>
================<br>
? Prepared statements parameters can be given names and bound to variables. </p>
<p>$stmt = $db->prepare("INSERT INTO users VALUES(:name,:pass,:mail)");<br>
foreach (array('name','pass','mail') as $v)<br>
$stmt->bindParam(':'.$v, $$v);<br>
$fp = fopen("./users", "r");<br>
while (list($name,$pass,$mail) = fgetcsv($fp,4096)) {<br>
$stmt->execute();<br>
}</p>
<p>Bound Result Columns<br>
====================<br>
? Result columns can be bound to variables as well.</p>
<p>$qry = "SELECT :type, :data FROM images LIMIT 1";<br>
$stmt = $db->prepare($qry);<br>
$stmt->bindColumn(':type',$type);<br>
$stmt->bindColumn(':type',STDOUT,PDO::PARAM_LOB);<br>
$stmt->execute(PDO::FETCH_BOUND);<br>
header("Content-Type: ".$type);</p>
<p>Partial Data Retrieval<br>
======================<br>
? you only want part of the data on the cursor. <br>
To properly end the cursor use closeCursor() method. <br>
<br>
$res = $db->query("SELECT * FROM users");<br>
foreach ($res as $v) {<br>
if ($res['name'] == 'end') {<br>
$res->closeCursor();<br>
break;<br>
}<br>
}</p>
<p>Transactions<br>
============<br>
? Nearly all PDO drivers talk with transactional DBs, so PDO provides <br>
handy methods for this purpose.</p>
<p>$db->beginTransaction();<br>
if ($db->exec($qry) === FALSE) { $db->rollback(); }<br>
$db->commit();</p>
<p>Metadata<br>
=========<br>
? Like most native db interfaces PDO can access query metadata.</p>
<p>$res = $db->query($qry);<br>
$ncols = $res->columnCount();<br>
for ($i=0; $i < $ncols; $i++) {<br>
$meta_data = $stmt->getColumnMeta($i);<br>
}</p>
<p>getColumnMeta() Result<br>
======================<br>
? native_type - PHP data type<br>
? driver:decl_type - data type of the column according to DB.<br>
? flags - will return any flags particular to this column in array.<br>
? name - of the column as returned by DB without any normalization.<br>
? len - maximum length of a string column, may not always be available,<br>
will be set to -1 if it isn't.<br>
? precision - numeric precision of this column. <br>
? pdo_type - column type according to PDO as one of <br>
pdo_type PDO_PARAM constants.</p>
<p>lastInsertId()<br>
==============<br>
? Many db have unique identifier assigned to each newly inserted row. <br>
PDO provides to each newly inserted row. access to this value <br>
via lastInsertId() method.<br>
? Can take optional sequence name as parameter.<br>
? Useful for PostgreSQL</p>
<p>if ($db->exec("INSERT INTO _")) {<br>
$id = $db->lastInsertId();<br>
}</p>
<p>Connection Information<br>
======================<br>
? Some connection information can be obtained via <strong>getAttribute()
PDO method</strong>. </p>
<p>$db->getAttribute(PDO::ATTR_SERVER_VERSION);<br>
// Database Server Version</p>
<p> $db->getAttribute(PDO::ATTR_CLIENT_VERSION);<br>
// Client Library Server Version</p>
<p> $db->getAttribute(PDO::ATTR_SERVER_INFO);<br>
// Misc Server information</p>
<p> $db->getAttribute(PDO::ATTR_CONNECTION_STATUS);<br>
// Connection Status</p>
<p>Extending PDO <br>
=============<br>
class DB extends PDO {<br>
function query($qry, $mode=NULL) {<br>
$res = parent::query($qry, $mode);<br>
if (!$res) {<br>
var_dump($qry, $this->errorInfo());<br>
return null;<br>
} else { return $res; }<br>
}<br>
}<br>
</p>
<p> </p>
<h1>Direct Query Execution - exec - standard: do not use</h1>
<p> ? Queries that modify information need to be run via exec() method.</p>
<p>? RETURN VALUE IS NUMBER OF ROWS AFFECTED BY OPERATION OR FALSE ON ERROR. </p>
<p>$db = new PDO("DSN");<br>
$db->exec("INSERT INTO foo (id) VALUES('bar')");<br>
$db->exec("UPDATE foo SET id='bar'");</p>
<p>? In some cases "change" queries may not affect any rows and return 0,<br>
so TYPE-SENSITIVE COMPARE TO AVOID FALSE POSITIVES! </p>
<p>$res = $db->exec("UPDATE foo SET id='bar'");</p>
<p>if (!$res) // Wrong<br>
<strong>if ($res !== FALSE) // Correct</strong></p>
<p>Retrieving Error Information<br>
=======================<br>
? PDO Provides 2 methods of getting error info:</p>
<p>? errorCode() - SQLSTATE <br>
? Example: 42000 == Syntax Error </p>
<p>? errorInfo() - Detailed error information <br>
? Ex. array( <br>
[0] => 42000,<br>
[1] => 1064<br>
[2] => You have an error in your SQL syntax; <br>
))</p>
<p>Better Error Handling<br>
=====================<br>
? Being an OO extension PDO would allow error handling via Exceptions.<br>
? Any query failure will throw an Exception. </p>
<p>$db->setAttribute(<br>
PDO::ATTR_ERRMODE, <br>
PDO::ERRMODE_EXCEPTION <br>
);</p>
<p>? When executing queries that retrieve query() method <br>
needs to be used. <br>
? On error FALSE is returned </p>
<p>$res = $db->query("SELECT * FROM foo");<br>
// $res == PDOStatement Object</p>
<p>Fetch Query Results<br>
====================<br>
? One of the biggest features of PDO is its flexibility <br>
in how data is to be fetched. <br>
? Array (Numeric or Associated Indexes)<br>
? Strings (for single column result sets) <br>
? Objects: stdClass, <br>
of given class <br>
or into an existing object<br>
? Callback function<br>
? Lazy fetching<br>
? Iterators<br>
? And more!</p>
<p>Array Fetching<br>
==============</p>
<p>$res = $db->query("SELECT * FROM foo");</p>
<p>while ($row = $res->fetch(PDO::FETCH_NUM)){<br>
// $row == array with NUMERIC KEYS<br>
}<br>
while ($row = $res->fetch(PDO::FETCH_ASSOC)){<br>
// $row == array with associated (STRING) KEYS<br>
}<br>
while ($row = $res->fetch(PDO::FETCH_BOTH)){<br>
// $row == array with ASSOCIATED & NUMERIC KEYS<br>
}</p>
<p>Fetch as String<br>
===============<br>
? fetch data contained within single column.</p>
<p>$u = $db->query("SELECT users WHERE login='login' AND password='password'");<br>
// fetch(PDO::FETCH_COLUMN)<br>
if ($u->fetchColumn()) { // returns a string<br>
// login OK<br>
} else { /* authentication failure */ }</p>
<p>Fetch as Standard Object<br>
========================<br>
? You can fetch a row as an instance of stdClass <br>
where column name == property name. <br>
<br>
$res = $db->query("SELECT * FROM foo");<br>
while ($obj = $res->fetch(PDO::FETCH_OBJ)) {<br>
// $obj == instance of stdClass<br>
}</p>
<p>Fetch Into a Class<br>
==================<br>
? PDO allows the result to be fetched into a class type of your choice.</p>
<p>$res = $db->query("SELECT * FROM foo");<br>
$res->setFetchMode(<br>
PDO::FETCH_CLASS,<br>
"className",<br>
array('optional'='Constructor Params')<br>
);<br>
while ($obj = $res->fetch()) {<br>
// $obj == instance of className<br>
}</p>
<p>Fetch Into a Class Cont.<br>
=========================<br>
? PDO allows query result to be used to determine destination class.</p>
<p>$res = $db->query("SELECT * FROM foo");<br>
$res->setFetchMode(<br>
PDO::FETCH_CLASS |<br>
PDO::FETCH_CLASSTYPE<br>
);<br>
while ($obj = $res->fetch()) {<br>
// $obj == instance of class who's name is<br>
// found in the value of the 1 st column<br>
}</p>
<p>Fetch Into an Object<br>
====================<br>
? PDO even allows retrieval of data into existing object.</p>
<p>$u = new userObject;<br>
$res = $db->query("SELECT * FROM users");<br>
$res->setFetchMode(PDO::FETCH_INTO, $u); <br>
while ($res->fetch()) {<br>
// will re-populate $u with row values<br>
}<br>
</p>
<p>Result Iteration<br>
=================<br>
? PDOStatement implements Iterator interface, which allows for <br>
a method-less result iteration. <br>
<br>
$res = $db->query(<br>
"SELECT * FROM users", <br>
PDO::FETCH_ASSOC<br>
);<br>
foreach ($res as $row) {<br>
// $row == associated array representing row's values.<br>
}</p>
<p>Lazy Fetching<br>
=============<br>
? Lazy fetches returns a result in a form object, <br>
but HOLDS OF POPULATING PROPERTIES UNTIL THEY ARE ACTUALLY USED. <br>
$res = $db->query(<br>
"SELECT * FROM users", <br>
PDO::FETCH_LAZY<br>
);<br>
foreach ($res as $row) {<br>
echo $row['name']; // only fetch name column<br>
}</p>
<p>fetchAll()<br>
===========<br>
? retrieval of all results from query right away (handy for templates) <br>
? Can be memory intensive for large results sets!</p>
<p>$qry = "SELECT * FROM users";<br>
$res = $db->query($qry)->fetchAll(PDO::FETCH_ASSOC);<br>
// $res == array of all result rows, where each row is an associated array.</p>
<p>Callback Function<br>
==================<br>
? fetch mode where each result is processed via a callback function. </p>
<p>function draw_message($subject,$email) { _ }</p>
<p>$res = $db->query("SELECT * FROM msg");<br>
$res->fetchAll(PDO::FETCH_FUNC, "draw_message");</p>
<p>Direct Query Problems<br>
=====================<br>
? Query interpreted on each execution - slow FREQUENTLY REPEATED queries.<br>
? Security issues, un-escaped user input can contain special elements<br>
leading to SQL injection.</p>
<p>Escaping in PDO<br>
===============<br>
? Escaping of special characters is handled via the quote() method. </p>
<p>$qry = "SELECT * FROM users <br>
WHERE login=".$db->quote($_POST['login'])."<br>
AND passwd=".$db->quote($_POST['pass'])<br>
;</p></td>
</tr>
<tr>
<td colspan="1" valign="top"> </td>
</tr>
</table>
</body>
</html>
- Connected to user: ************ $dbh =
new PDO(‘oci:host=sspc/XE:pooled;charset=EE8MSWIN1250’, ‘hr’, ‘hr’);
- PERSISTENT Connected to user: ************$dbh =
new PDO(‘oci:host=sspc/XE:pooled;charset=EE8MSWIN1250’, ‘hr’, ‘hr’, array(PDO::ATTR_PERSISTENT => TRUE));
$opt = array(PDO::ATTR_PERSISTENT => TRUE) = Array ( [0] => 0 )
- PDO driver: $dbh->getAttribute(PDO::ATTR_DRIVER_NAME) = oci
- not supported for OCI PDO: $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS);
- DB Server Version $dbh->getAttribute(PDO::ATTR_SERVER_VERSION) =
11.2.0.2.0
- $dbh->getAttribute(PDO::ATTR_SERVER_INFO) =
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
- Client Library Server Ver. $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION) = 11.2.0.2.0
II. PDO CRUD DML (QUERY)
II.1 max ID & C R E A T E with bind variables – prepare, execute, fetch
- DELETE T_WEBUSER table
- SELECT count(*) from T_WEBUSER, $row = array with NUMERIC KEYS
$countrec in table =
- $nextidrec (from DB records, if=1 this is first record in table – unknown sequence) = 1
- $sth = $dbh->prepare(‘INSERT INTO T_WEBUSER (UNAME, PASSWORD) VALUES(?,?)’ )
// and same for usr2, BUT NOT MEMBER_ID – TRIGGER !!
II.2 U P D A T E with bind variable
- UPDATE T_WEBUSER SET FORENAME = ‘FORENAME’ where UNAME = ?
- All rows: SELECT * FROM T_WEBUSER
| 142 |
usr1 |
pswusr1 |
FORENAME |
| 143 |
usr2 |
pswusr2 |
|
II.3 D E L E T E with bind variable
- DELETE T_WEBUSER where UNAME = ?
II.4 R E T R I V E with bind variable
- All rows: SELECT * FROM T_WEBUSER WHERE UNAME=? (? means bind variable value)
| 142 |
usr1 |
pswusr1 |
FORENAME |
- One row: SELECT * FROM T_WEBUSER WHERE UNAME=? (? means bind variable value)
Array
(
[MEMBER_ID] => 142
[IS_ADMIN] => 0
[ACTIVATION_TOKEN] => 0
[FORENAME] => FORENAME
[LASTNAME] =>
[UNAME] => usr1
[EMAIL] =>
[PASSWORD] => pswusr1
[ACTIVE] =>
[DATE_REGISTER] =>
[DATE_MOD] =>
[DATE_EXPIRES] =>
[MEMBER_LEVEL] =>
[TIP] =>
[DESCRIPTION1] =>
[DESCRIPTION2] =>
[DESCRIPTION3] =>
)
III. PHP Data Objects Layer (PDO) – Ilia Alshanetsky
Written in C – FAST! Designed to make use of all PHP 5.1 features
What Databases are Supported?
? At time ~ PHP 5.1 PDO offered following drivers:
? MySQL 3,4,5 (depends on client libs)
? PostgreSQL
? SQLite 2 & 3
? ODBC
? DB2
? Oracle – 2015 year still experimental
? Firebird
? FreeTDS/Sybase/MSSQL
Installing PDO
? PDO is divided into two components
? CORE (provides the interface)
? DRIVERS (access to particular database) – Example: pdo_mysql
? The CORE is enabled by default,
drivers with the exception of pdo_sqlite are not.
Actual Install Steps :
? PECL Way
? pecl install pdo_[driver_name]
? Update php.ini and add
extension=pdo_[driver_name].so (or .dll on win32)
? Built into PHP Built
? ./configure -with-pdo-[driver_name]
? For Win32 dlls for each driver are available.
Using PDO – connection
// Oracle
$dbh = new PDO(‘oci:dbname=sspc/XE:pooled’, ‘hr’, ‘hr’, array(PDO::ATTR_PERSISTENT => TRUE));
// MySQL
new PDO(‘mysql:host=localhost;dbname=testdb’, $login, $passwd);
// PostgreSQL
new PDO(‘pgsql:host=localhost port=5432 dbname=testdb user=john password=mypass’);
// SQLite
new PDO(‘sqlite:/path/to/database_file’);
What if the Connection Fails?
? As is the case with most native PHP objects,
instantiation failure lead to an exception being thrown.
try {
$db = new PDO(_);
} catch (PDOException $e) {
echo $e->getMessage();
}
Persistent Connections
? Connecting to complex DB like Oracle is a slow process,
it would be nice to re-use a previously opened connection.
$opt = array(PDO::ATTR_PERSISTENT => TRUE) ;
try {
$db = new PDO(“dsn”, $l, $p, $opt);
} catch (PDOException $e) {
echo $e->getMessage();
}
DSN INI Tricks
? The DSN string can be an INI setting
you can “name” as many DSNs are you like.
ini_set(“pdo.dsn.ilia”, “sqlite::memory”);
try {
$db = new PDO(“ilia”);
} catch (PDOException $e) {
echo $e->getMessage();
}
Queries
? Query execution in PDO can be done in two ways :
? Prepared Statements (recommended for speed & security) – bind variables
? Direct Query Execution
Prepared Statements – prepare, execute, fetch
? Compile once, execute as many times as you want.
? Clear SEPARATION BETWEEN STRUCTURE AND INPUT, which prevents SQL injection.
? Often faster then query()/exec() even for single runs.
$stmt = $db->prepare( “SELECT * FROM users WHERE id=?” );
$stmt->execute(array($_GET[‘id’]));
$stmt->fetch(PDO::FETCH_ASSOC);
Bound Parameters
================
? Prepared statements parameters can be given names and bound to variables.
$stmt = $db->prepare(“INSERT INTO users VALUES(:name,:pass,:mail)”);
foreach (array(‘name’,’pass’,’mail’) as $v)
$stmt->bindParam(‘:’.$v, $$v);
$fp = fopen(“./users”, “r”);
while (list($name,$pass,$mail) = fgetcsv($fp,4096)) {
$stmt->execute();
}
Bound Result Columns
====================
? Result columns can be bound to variables as well.
$qry = “SELECT :type, :data FROM images LIMIT 1”;
$stmt = $db->prepare($qry);
$stmt->bindColumn(‘:type’,$type);
$stmt->bindColumn(‘:type’,STDOUT,PDO::PARAM_LOB);
$stmt->execute(PDO::FETCH_BOUND);
header(“Content-Type: “.$type);
Partial Data Retrieval
======================
? you only want part of the data on the cursor.
To properly end the cursor use closeCursor() method.
$res = $db->query(“SELECT * FROM users”);
foreach ($res as $v) {
if ($res[‘name’] == ‘end’) {
$res->closeCursor();
break;
}
}
Transactions
============
? Nearly all PDO drivers talk with transactional DBs, so PDO provides
handy methods for this purpose.
$db->beginTransaction();
if ($db->exec($qry) === FALSE) { $db->rollback(); }
$db->commit();
Metadata
=========
? Like most native db interfaces PDO can access query metadata.
$res = $db->query($qry);
$ncols = $res->columnCount();
for ($i=0; $i < $ncols; $i++) {
$meta_data = $stmt->getColumnMeta($i);
}
getColumnMeta() Result
======================
? native_type – PHP data type
? driver:decl_type – data type of the column according to DB.
? flags – will return any flags particular to this column in array.
? name – of the column as returned by DB without any normalization.
? len – maximum length of a string column, may not always be available,
will be set to -1 if it isn’t.
? precision – numeric precision of this column.
? pdo_type – column type according to PDO as one of
pdo_type PDO_PARAM constants.
lastInsertId()
==============
? Many db have unique identifier assigned to each newly inserted row.
PDO provides to each newly inserted row. access to this value
via lastInsertId() method.
? Can take optional sequence name as parameter.
? Useful for PostgreSQL
if ($db->exec(“INSERT INTO _”)) {
$id = $db->lastInsertId();
}
Connection Information
======================
? Some connection information can be obtained via getAttribute() PDO method.
$db->getAttribute(PDO::ATTR_SERVER_VERSION);
// Database Server Version
$db->getAttribute(PDO::ATTR_CLIENT_VERSION);
// Client Library Server Version
$db->getAttribute(PDO::ATTR_SERVER_INFO);
// Misc Server information
$db->getAttribute(PDO::ATTR_CONNECTION_STATUS);
// Connection Status
Extending PDO
=============
class DB extends PDO {
function query($qry, $mode=NULL) {
$res = parent::query($qry, $mode);
if (!$res) {
var_dump($qry, $this->errorInfo());
return null;
} else { return $res; }
}
}
Direct Query Execution – exec – standard: do not use
? Queries that modify information need to be run via exec() method.
? RETURN VALUE IS NUMBER OF ROWS AFFECTED BY OPERATION OR FALSE ON ERROR.
$db = new PDO(“DSN”);
$db->exec(“INSERT INTO foo (id) VALUES(‘bar’)”);
$db->exec(“UPDATE foo SET id=’bar'”);
? In some cases “change” queries may not affect any rows and return 0,
so TYPE-SENSITIVE COMPARE TO AVOID FALSE POSITIVES!
$res = $db->exec(“UPDATE foo SET id=’bar'”);
if (!$res) // Wrong
if ($res !== FALSE) // Correct
Retrieving Error Information
=======================
? PDO Provides 2 methods of getting error info:
? errorCode() – SQLSTATE
? Example: 42000 == Syntax Error
? errorInfo() – Detailed error information
? Ex. array(
[0] => 42000,
[1] => 1064
[2] => You have an error in your SQL syntax;
))
Better Error Handling
=====================
? Being an OO extension PDO would allow error handling via Exceptions.
? Any query failure will throw an Exception.
$db->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
? When executing queries that retrieve query() method
needs to be used.
? On error FALSE is returned
$res = $db->query(“SELECT * FROM foo”);
// $res == PDOStatement Object
Fetch Query Results
====================
? One of the biggest features of PDO is its flexibility
in how data is to be fetched.
? Array (Numeric or Associated Indexes)
? Strings (for single column result sets)
? Objects: stdClass,
of given class
or into an existing object
? Callback function
? Lazy fetching
? Iterators
? And more!
Array Fetching
==============
$res = $db->query(“SELECT * FROM foo”);
while ($row = $res->fetch(PDO::FETCH_NUM)){
// $row == array with NUMERIC KEYS
}
while ($row = $res->fetch(PDO::FETCH_ASSOC)){
// $row == array with associated (STRING) KEYS
}
while ($row = $res->fetch(PDO::FETCH_BOTH)){
// $row == array with ASSOCIATED & NUMERIC KEYS
}
Fetch as String
===============
? fetch data contained within single column.
$u = $db->query(“SELECT users WHERE login=’login’ AND password=’password'”);
// fetch(PDO::FETCH_COLUMN)
if ($u->fetchColumn()) { // returns a string
// login OK
} else { /* authentication failure */ }
Fetch as Standard Object
========================
? You can fetch a row as an instance of stdClass
where column name == property name.
$res = $db->query(“SELECT * FROM foo”);
while ($obj = $res->fetch(PDO::FETCH_OBJ)) {
// $obj == instance of stdClass
}
Fetch Into a Class
==================
? PDO allows the result to be fetched into a class type of your choice.
$res = $db->query(“SELECT * FROM foo”);
$res->setFetchMode(
PDO::FETCH_CLASS,
“className”,
array(‘optional’=’Constructor Params’)
);
while ($obj = $res->fetch()) {
// $obj == instance of className
}
Fetch Into a Class Cont.
=========================
? PDO allows query result to be used to determine destination class.
$res = $db->query(“SELECT * FROM foo”);
$res->setFetchMode(
PDO::FETCH_CLASS |
PDO::FETCH_CLASSTYPE
);
while ($obj = $res->fetch()) {
// $obj == instance of class who’s name is
// found in the value of the 1 st column
}
Fetch Into an Object
====================
? PDO even allows retrieval of data into existing object.
$u = new userObject;
$res = $db->query(“SELECT * FROM users”);
$res->setFetchMode(PDO::FETCH_INTO, $u);
while ($res->fetch()) {
// will re-populate $u with row values
}
Result Iteration
=================
? PDOStatement implements Iterator interface, which allows for
a method-less result iteration.
$res = $db->query(
“SELECT * FROM users”,
PDO::FETCH_ASSOC
);
foreach ($res as $row) {
// $row == associated array representing row’s values.
}
Lazy Fetching
=============
? Lazy fetches returns a result in a form object,
but HOLDS OF POPULATING PROPERTIES UNTIL THEY ARE ACTUALLY USED.
$res = $db->query(
“SELECT * FROM users”,
PDO::FETCH_LAZY
);
foreach ($res as $row) {
echo $row[‘name’]; // only fetch name column
}
fetchAll()
===========
? retrieval of all results from query right away (handy for templates)
? Can be memory intensive for large results sets!
$qry = “SELECT * FROM users”;
$res = $db->query($qry)->fetchAll(PDO::FETCH_ASSOC);
// $res == array of all result rows, where each row is an associated array.
Callback Function
==================
? fetch mode where each result is processed via a callback function.
function draw_message($subject,$email) { _ }
$res = $db->query(“SELECT * FROM msg”);
$res->fetchAll(PDO::FETCH_FUNC, “draw_message”);
Direct Query Problems
=====================
? Query interpreted on each execution – slow FREQUENTLY REPEATED queries.
? Security issues, un-escaped user input can contain special elements
leading to SQL injection.
Escaping in PDO
===============
? Escaping of special characters is handled via the quote() method.
$qry = “SELECT * FROM users
WHERE login=”.$db->quote($_POST[‘login’]).”
AND passwd=”.$db->quote($_POST[‘pass’])
; |
