PHP Classes

File: index.php

Recommend this page to a friend!
  Classes of Johan Barbier   aDB PDO like database abstraction   index.php   Download  
File: index.php
Role: Example script
Content type: text/plain
Description: Exemples
Class: aDB PDO like database abstraction
Database abstraction layer similar to PDO
Author: By
Last change: New methods added
Date: 16 years ago
Size: 9,179 bytes
 

Contents

Class file image Download
<?php
/**
 * EXEMPLES UTILISATION aDB
 *
 */
define ('HOST', '****');
define ('LOGIN', '****');
define ('PWD', '****');
define ('DB', '****');

define ('HOST2', '*****');
define ('LOGIN2', '*****');
define ('PWD2', '*****');
define ('DB2', '*****');

define ('HOST3', '***');
define ('LOGIN3', '***');
define ('PWD3', '***');
define ('DB3', '***');

require_once
'class.aDB.php';

try {

   
/**
     * instanciation with mssql db server via aDBFactory
     */
   
$test = aDBFactory::getInstance ('mssql', array ('HOST' => HOST, 'LOGIN' => LOGIN, 'PWD' => PWD, 'DB' => DB));
   
/**
     * query prepared
     * :iId is an integer (aDB::PARAM_INT) and has the value 6
     * Query is executed
     * results are fetched in an associative array if query returns something
     */
   
$sQuery = 'SELECT dest_id FROM destinataires WHERE dest_id = :iId';
   
$test -> prepare ($sQuery);
   
$test -> bindValue (':iId', 6, aDB::PARAM_INT );
   
$test -> execute ();
    if (
$test -> count () > 0 ) {
        echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
        echo
'<hr />';
    }
   
/**
     * query prepared
     * :iId has value 1, and type is automatically chosen from the value (so : integer)
     * Query is executed
     * results are fetched in an associative array if query returns something
     */
   
$sQuery = 'SELECT dest_id FROM destinataires WHERE dest_id = :iId';
   
$test -> prepare ($sQuery);
   
$test -> bindValue (':iId', 1);
   
$test -> execute ();
    if (
$test -> count () > 0 ) {
        echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
        echo
'<hr />';
    }

   
/**
     * query prepared
     * :sRS is a string (aDB::PARAM_STR) and has the value 'CUSTOM PROMO'
     * Query is executed
     * results are fetched in an associative array
     */
   
$sQuery = 'SELECT dest_id, dest_raison FROM destinataires WHERE dest_raison = :sRS';
   
$test -> prepare ($sQuery);
   
$test -> bindValue (':sRS', 'CUSTOM PROMO', aDB::PARAM_STR);
   
$test -> execute ();
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
   
/**
     * Query is executed
     * Limitation is set to : staring offset = 0, length = 10
     * results are fetched in an associative array
     * Limitation is updated to the next set of results
     * results are fetched
     */
   
$sQuery = 'SELECT dest_id, dest_raison FROM destinataires where dest_id < 25';
   
$test -> query ($sQuery);
   
$test -> set_limit (0, 10);
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<p>NEXT PAGE</p>';
   
$test -> next_limit ();
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
/**
     * Query is executed
     * Limitation is set to : staring offset = 0, length = 10
     * results are fetched in a grouped array : index is the dest_id, and it points to an associative array with dest_raison
     * Limitation is canceled for the next query
     */
   
$test -> query ($sQuery);
   
$test -> set_limit (0, 10);
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_GROUP|aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
$test -> set_limit ();
   
   
/**
     * Query is executed
     * first dest_raison value is retrieved (column 1; column 0 being dest_id)
     */
   
$sQuery2 = 'SELECT top 1 dest_id, dest_raison FROM destinataires';
   
$test -> query ($sQuery2);
    echo
$test -> fetchColumn (1);
    echo
'<hr />';

   
/**
     * Staring a transaction
     * INSERT query is executed
     * Inserted ID is retrieved
     * We roll the transaction back : insertion is canceled
     */
   
$test -> startTransaction ();
   
$sQuery3 = 'INSERT INTO transporteurs (tp_libelle) values (\'tp_test\')';
   
$test -> query ($sQuery3);
    echo
$test -> lastInsertId ();
   
$test -> rollbackTransaction ();

   
/**
     * Limitation is set : starting offset is 3, length is 3
     * Query is execute
     * Results are fetched in a numeric array
     */
   
$test -> set_limit (3, 3);
   
$test -> query ($sQuery);
    echo
'<pre>', print_r ($test -> fetchAll (aDB::FETCH_NUM)), '</pre>';
    echo
'<hr />';

   
/**
     * Query is executed
     * Limitation is set : starting offset is 10, no length limit
     * results are fetched in an array both associative and numeric
     * Limitations is canceled for the next query
     */
   
$test -> query ($sQuery);
   
$test -> set_limit (10);
    echo
'<pre>', print_r ($test -> fetchAll()), '</pre>';
   
$test -> set_limit ();
    echo
'<hr />';

   
/**
     * Query is executed
     * results are fetched through an iterator wich returns both an associative and numeric array
     */
   
$test -> query ($sQuery);
   
$it = $test -> fetch();
    foreach (
$it as $aVal) {
        echo
'<pre>', print_r ($aVal), '</pre>';
    }

   
/**
     * Query is executed
     * results are fetched through an iterator wich is grouped and associative
     */
   
echo '<hr />';
   
$test -> query ($sQuery);
   
$it = $test -> fetch(aDB::FETCH_GROUP|aDB::FETCH_ASSOC);
    foreach (
$it as $aVal) {
        echo
'<pre>', print_r ($aVal), '</pre>';
    }
   
/**
     * Limitation is set : Starting offset is 1, length is 3
     * Query has already been executed
     * results are fetched through an iterator wich returns variables corresponding to columns name (just like an extract)
     */
   
echo '<hr />';
   
$test -> set_limit (1, 3);
   
$it = $test -> fetch(aDB::FETCH_EXTRACT);
    foreach (
$it as $aVal) {
        echo
$dest_id, ' => ', $dest_raison, '<br />';
    }
   
$test -> set_limit ();
   
   
/**
     * Query is executed
     * results are fetched through an iterator via a while loop
     */
   
echo '<hr />';
   
$sQuery = 'SELECT dest_id FROM destinataires WHERE dest_id < 25';
   
$test -> query ($sQuery);
   
$it = $test -> fetch (aDB::FETCH_ASSOC);
   
$it -> next ();
    while (
$it -> valid ()) {
        echo
'<pre>', print_r ($it -> current ()), '</pre>';
       
$it -> next ();
    }
    echo
'<hr />';
   
/**
     * retrieving the same instance of mssql but with a new connection!
     */
   
$test = aDBFactory::getInstance ('mssql', array ('HOST' => HOST2, 'LOGIN' => LOGIN2, 'PWD' => PWD2, 'DB' => DB2));
   
   
$sQuery = 'SELECT dest_id, dest_raison FROM destinataires where comt_id = 38 and ent_id = 46';
   
$test -> query ($sQuery);
   
$test -> set_limit (0, 10);
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
$test -> set_limit ();

   
/**
     * retrieving a new instance of mysql this time
     */
   
$test = aDBFactory::getInstance ('mysql', array ('HOST' => HOST3, 'LOGIN' => LOGIN3, 'PWD' => PWD3, 'DB' => DB3));

   
$sQuery = 'SELECT * FROM csv';
   
$test -> query ($sQuery);
   
$test -> set_limit (0, 10);
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
$test -> set_limit ();

   
/**
     * back to the instance of mysql with last connection used
     */
   
$test = aDBFactory::getInstance ('mssql');
   
   
$sQuery = 'SELECT dest_id, dest_raison FROM destinataires where comt_id = 38 and ent_id = 46';
   
$test -> query ($sQuery);
   
$test -> set_limit (0, 10);
    echo
'<pre>', print_r ($test -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
    echo
'<hr />';
   
$test -> set_limit ();

   
/**
     * Fetch server infos if available (an exception is thrown if not and if Exceptions are active)
     */
    //echo $test -> server_info();
} catch (aDBException $e) {
    echo
$e;
}
/**
 * Basic Exemple of how to use aDB Exceptions
 * First, HOST is wrong, an aDBExceptionDbConnectorErrors will be caught. So, we modify the host (correct one this time)
 */
$aCon = array ('HOST' => 'fake', 'LOGIN' => LOGIN3, 'PWD' => PWD3, 'DB' => DB3);
$iOffset = 2;
goDB ($aCon, $iOffset);

function
goDB (& $aCon, & $iOffset) {
    try {
       
       
$myDb = aDBFactory::getInstance ('mysql', $aCon);
       
$myDb -> set_limit ($iOffset, 10);
       
$sQuery = 'SELECT * FROM csv WHERE id = :fake';
       
$myDb -> bindValue (':fake', 1, aDB::PARAM_INT);
       
$myDb -> execute ();
        echo
'<pre>', print_r ($myDb -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
        echo
'<hr />';
       
$myDb -> set_limit ();
       
    } catch (
aDBExceptionIllegalClass $e) {
        echo
$e, '<br />';
       
    } catch (
aDBExceptionDbConnectorErrors $e) {
       
/**
         * First error: HOST was a wrong host, so aDB could not connect.
         * Let's change it and rty again :-)
         */
       
echo $e, '<br />';
        if (
$e -> getCode () === aDBExceptionDbConnectorErrors::CODE_CONNEXION_FAILED) {
           
$aCon['HOST'] = HOST3;
           
goDB ($aCon, $iOffset);
        }
       
/**
         * Third error : Offset is not rood because this query retrieves only 1 row.
         * Set the offset correctly and try again
         */
       
if ($e -> getCode () ===aDBExceptionDbConnectorErrors::CODE_INVALID_SEEK_POSITION) {
           
$iOffset = 0;
           
goDB ($aCon, $iOffset);
        }
       
    } catch (
aDBExceptionInvalidClassCalls $e) {
       
/**
         * Second error : we tried to bind a value but we forgot to prepare the query first! Let's prepare it and try again.
         */
       
echo $e, '<br />';
        if (
$e -> getCode() === aDBExceptionInvalidClassCalls::CODE_NO_QUERY_TO_PREPARE) {
           
$myDb -> prepare ($sQuery);
           
goDB ($aCon, $iOffset);
        }
        if (
$e -> getCode() === aDBExceptionInvalidClassCalls::CODE_NEEDLE_NOT_FOUND) {
           
/**
             * Fourth error : as we have already bound the :fake, it cannot be bound another time!
             * So, here, we must go to the end : just execute and fetch. It's over :-)
             */
           
$myDb -> execute ();
            echo
'<pre>', print_r ($myDb -> fetchAll(aDB::FETCH_ASSOC)), '</pre>';
            echo
'<hr />';
           
$myDb -> set_limit ();
        }
       
    } catch (
aDBExceptionTypesError $e) {
        echo
$e, '<br />';
       
    } catch (
Exception $e) {
        echo
$e, '<br />';
    }
}
?>