<?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 />';
}
}
?>
|
Download
