File: index.php


<?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 />';
    }
}
?>
Copyright (c) Icontem 1999-2008	PHP Classes	- PHP Class Scripts
	PHP Book Reviews	- Reviews of books and other products
			Classes of Johan Barbier	>	aDB PDO like database abstraction	>	index.php
Contents:
