7. ORACLE 11g XE PERSISTENT DB CONNECTION POOLED (ESTABLISHED WITH PHP PDO OR E.Rangel’s PDOOCI)

HOME  

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.

  1. <?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 />&nbsp;&nbsp;&nbsp;(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 />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp $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 &amp; 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 =&gt; 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-&gt;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 =&gt; TRUE) ;<br>
     try {<br>
     $db = new PDO(&quot;dsn&quot;, $l, $p, $opt);<br>
     } catch (PDOException $e) {<br>
     echo $e-&gt;getMessage();<br>
     }</p>
    <h2>DSN INI Tricks<br>
    </h2>
    <p> ? The DSN string can be an INI setting <br>
     you can &quot;name&quot; as many DSNs are you like.<br>
     <br>
     ini_set(&quot;pdo.dsn.ilia&quot;, &quot;sqlite::memory&quot;);<br>
     try {<br>
     $db = new PDO(&quot;ilia&quot;);<br>
     } catch (PDOException $e) {<br>
     echo $e-&gt;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 &amp; 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-&gt;prepare( &quot;SELECT * FROM users WHERE id=?&quot; );<br>
     $stmt-&gt;execute(array($_GET['id']));<br>
     $stmt-&gt;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-&gt;prepare(&quot;INSERT INTO users VALUES(:name,:pass,:mail)&quot;);<br>
     foreach (array('name','pass','mail') as $v)<br>
     $stmt-&gt;bindParam(':'.$v, $$v);<br>
     $fp = fopen(&quot;./users&quot;, &quot;r&quot;);<br>
     while (list($name,$pass,$mail) = fgetcsv($fp,4096)) {<br>
     $stmt-&gt;execute();<br>
     }</p>
    <p>Bound Result Columns<br>
     ====================<br>
     ? Result columns can be bound to variables as well.</p>
    <p>$qry = &quot;SELECT :type, :data FROM images LIMIT 1&quot;;<br>
     $stmt = $db-&gt;prepare($qry);<br>
     $stmt-&gt;bindColumn(':type',$type);<br>
     $stmt-&gt;bindColumn(':type',STDOUT,PDO::PARAM_LOB);<br>
     $stmt-&gt;execute(PDO::FETCH_BOUND);<br>
     header(&quot;Content-Type: &quot;.$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-&gt;query(&quot;SELECT * FROM users&quot;);<br>
     foreach ($res as $v) {<br>
     if ($res['name'] == 'end') {<br>
     $res-&gt;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-&gt;beginTransaction();<br>
     if ($db-&gt;exec($qry) === FALSE) { $db-&gt;rollback(); }<br>
     $db-&gt;commit();</p>
    <p>Metadata<br>
     =========<br>
     ? Like most native db interfaces PDO can access query metadata.</p>
    <p>$res = $db-&gt;query($qry);<br>
     $ncols = $res-&gt;columnCount();<br>
     for ($i=0; $i &lt; $ncols; $i++) {<br>
     $meta_data = $stmt-&gt;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-&gt;exec(&quot;INSERT INTO _&quot;)) {<br>
     $id = $db-&gt;lastInsertId();<br>
     }</p>
    <p>Connection Information<br>
     ======================<br>
     ? Some connection information can be obtained via <strong>getAttribute() 
     PDO method</strong>. </p>
    <p>$db-&gt;getAttribute(PDO::ATTR_SERVER_VERSION);<br>
     &nbsp;&nbsp;&nbsp; // Database Server Version</p>
    <p> $db-&gt;getAttribute(PDO::ATTR_CLIENT_VERSION);<br>
     &nbsp;&nbsp;&nbsp;&nbsp; // Client Library Server Version</p>
    <p> $db-&gt;getAttribute(PDO::ATTR_SERVER_INFO);<br>
     &nbsp;&nbsp;&nbsp;&nbsp; // Misc Server information</p>
    <p> $db-&gt;getAttribute(PDO::ATTR_CONNECTION_STATUS);<br>
     &nbsp;&nbsp;&nbsp;&nbsp; // Connection Status</p>
    <p>Extending PDO <br>
     =============<br>
     class DB extends PDO {<br>
     function query($qry, $mode=NULL) {<br>
     &nbsp;&nbsp;&nbsp;&nbsp; $res = parent::query($qry, $mode);<br>
     &nbsp;&nbsp;&nbsp;&nbsp; if (!$res) {<br>
     &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; var_dump($qry, $this-&gt;errorInfo());<br>
     &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; return null;<br>
     &nbsp; &nbsp;&nbsp; } else { return $res; }<br>
     }<br>
     }<br>
    </p>
    <p>&nbsp;</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(&quot;DSN&quot;);<br>
     $db-&gt;exec(&quot;INSERT INTO foo (id) VALUES('bar')&quot;);<br>
     $db-&gt;exec(&quot;UPDATE foo SET id='bar'&quot;);</p>
    <p>? In some cases &quot;change&quot; queries may not affect any rows and return 0,<br>
     so TYPE-SENSITIVE COMPARE TO AVOID FALSE POSITIVES! </p>
    <p>$res = $db-&gt;exec(&quot;UPDATE foo SET id='bar'&quot;);</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:&nbsp; 42000 == Syntax Error </p>
    <p>? errorInfo() - Detailed error information <br>
     ? Ex. array( <br>
     [0] =&gt; 42000,<br>
     [1] =&gt; 1064<br>
     [2] =&gt; 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-&gt;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-&gt;query(&quot;SELECT * FROM foo&quot;);<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-&gt;query(&quot;SELECT * FROM foo&quot;);</p>
    <p>while ($row = $res-&gt;fetch(PDO::FETCH_NUM)){<br>
     // $row == array with NUMERIC KEYS<br>
     }<br>
     while ($row = $res-&gt;fetch(PDO::FETCH_ASSOC)){<br>
     // $row == array with associated (STRING) KEYS<br>
     }<br>
     while ($row = $res-&gt;fetch(PDO::FETCH_BOTH)){<br>
     // $row == array with ASSOCIATED &amp; NUMERIC KEYS<br>
     }</p>
    <p>Fetch as String<br>
     ===============<br>
     ? fetch data contained within single column.</p>
    <p>$u = $db-&gt;query(&quot;SELECT users WHERE login='login' AND password='password'&quot;);<br>
     // fetch(PDO::FETCH_COLUMN)<br>
     if ($u-&gt;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-&gt;query(&quot;SELECT * FROM foo&quot;);<br>
     while ($obj = $res-&gt;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-&gt;query(&quot;SELECT * FROM foo&quot;);<br>
     $res-&gt;setFetchMode(<br>
     PDO::FETCH_CLASS,<br>
     &quot;className&quot;,<br>
     array('optional'='Constructor Params')<br>
     );<br>
     while ($obj = $res-&gt;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-&gt;query(&quot;SELECT * FROM foo&quot;);<br>
     $res-&gt;setFetchMode(<br>
     PDO::FETCH_CLASS |<br>
     PDO::FETCH_CLASSTYPE<br>
     );<br>
     while ($obj = $res-&gt;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-&gt;query(&quot;SELECT * FROM users&quot;);<br>
     $res-&gt;setFetchMode(PDO::FETCH_INTO, $u); <br>
     while ($res-&gt;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-&gt;query(<br>
     &quot;SELECT * FROM users&quot;, <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-&gt;query(<br>
     &quot;SELECT * FROM users&quot;, <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 = &quot;SELECT * FROM users&quot;;<br>
     $res = $db-&gt;query($qry)-&gt;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-&gt;query(&quot;SELECT * FROM msg&quot;);<br>
     $res-&gt;fetchAll(PDO::FETCH_FUNC, &quot;draw_message&quot;);</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 = &quot;SELECT * FROM users <br>
     WHERE login=&quot;.$db-&gt;quote($_POST['login']).&quot;<br>
     AND passwd=&quot;.$db-&gt;quote($_POST['pass'])<br>
     ;</p></td>
    </tr>
     <tr>
     <td colspan="1" valign="top">&nbsp;</td>
     </tr>
     </table>
    </body>
    
    </html>
  2. Connected to user: ************ $dbh =
    new PDO(‘oci:host=sspc/XE:pooled;charset=EE8MSWIN1250’, ‘hr’, ‘hr’);
  3. 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 )
  1. PDO driver: $dbh->getAttribute(PDO::ATTR_DRIVER_NAME) = oci
  2. not supported for OCI PDO: $dbh->getAttribute(PDO::ATTR_CONNECTION_STATUS);
  3. DB Server Version $dbh->getAttribute(PDO::ATTR_SERVER_VERSION) =
    11.2.0.2.0
  4. $dbh->getAttribute(PDO::ATTR_SERVER_INFO) =
    Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
  5. 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

  1. DELETE T_WEBUSER table
  2. SELECT count(*) from T_WEBUSER, $row = array with NUMERIC KEYS
    $countrec in table =
  3. $nextidrec (from DB records, if=1 this is first record in table – unknown sequence) = 1
  4. $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

  1. UPDATE T_WEBUSER SET FORENAME = ‘FORENAME’ where UNAME = ?
  2. All rows: SELECT * FROM T_WEBUSER
    142 usr1 pswusr1 FORENAME
    143 usr2 pswusr2

II.3 D E L E T E with bind variable

  1. DELETE T_WEBUSER where UNAME = ?

II.4 R E T R I V E with bind variable

  1. All rows: SELECT * FROM T_WEBUSER WHERE UNAME=? (? means bind variable value)
    142 usr1 pswusr1 FORENAME
  2. 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’])
;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.