Quick links: Content - sections - sub sections
EN FR

Once the the config file is written, you can access the databases.

The jDbConnection object

First, you should retrieve a jDbConnection object:


  $conn = jDb::getConnection();

The getConnection() method allows you to retrieve a jDbConnection object (or jDbPDOConnection extending PDO, if you use PDO, they however have the same API). This method accepts an optional parameter : the name of the profile to use. If it is not specified, the default one is used.

To construct your SQL queries, you have an important method to use if you are not using prepared statement: quote(). It escapes all reserved characters of the database, and you should use it for all data you want to insert in your SQL queries. It avoid security issue like SQL injection. Another friend of quote() is quote2() which can be used on binary column.


  $sql = "INSERT INTO users (name,firstname) VALUES";
  $sql .=" (". $conn->quote("Doe") .",".$cnx->quote('john').")";

Notice that the quote() method add quotes at the begin and the end of the given string.

If you need to build your queries by adding table names or field names that need to be enclosed between characters specific to the database type, you can use the encloseName method:


  $table = 'users';
  $sql = "INSERT INTO ".$cnx->encloseName($table)." (nom, prenom) VALUES";
  $sql .=" (". $cnx->quote("de l'ombre") .",".$cnx->quote('robert').")";

To execute queries, you have two methods: exec() and query().

exec

exec should be use for queries which don't return records, like UPDATE, INSERT, DELETE... This method only returns the number of updated/inserted/deleted records. Example:


  $conn = jDb::getConnection();

  $conn->exec("INSERT INTO users (name,firstname) VALUES('dupont','toto')");

query

query should be used for queries which return records : SELECT, stored procedure. The method returns a jDbResultSet object.

Quick example:


  $conn = jDb::getConnection();

  $rs = $conn->query('SELECT name, firstname FROM users');
  $result = '';
  while ($record = $rs->fetch()) {
     $result .= 'name = '.$record->name.' firstname = '.$record->firstname."\n";
  }

limitQuery

You can retrieve only some few records, by using the limitQuery method:


  $conn = jDb::getConnection();

  $rs = $conn->limitQuery('SELECT name, firstname FROM users', 5, 10);
  $result = '';
  while ($record = $rs->fetch()) {
     $result .= 'name = '.$record->name.' firstname = '.$record->firstname."\n";
  }

The first parameter is the query. The second is the number of the first record to retrieve. And the third parameter is the count of records to retrieve.

Prepared statement

You can use prepared statement. It is more secured than creating a SQL request string from variables.

The API is similar to PDO: a method prepare() allow to indicate the query. The query can have named parameters starting with a @@:@. And the method returns an object jDbResultSet.


    $stmt = $cnx->prepare('INSERT INTO `labels_test` (`key`,`lang` ,`label`) VALUES (:k, :lg, :lb)');

On the returned object, you can indicate the value of each parameters with the method bindParam() or bindValue(). bindParam() is to give the value of variable, and bindValue() is to give directly a PHP value. You have to indicate the type of the variable or value to both methods, with one of the PDO::PARAM_* constants. By default it is PDO::PARAM_STR, so the value should be a string.


    $bind = $stmt->bindParam('lg', $lang);
    $bind = $stmt->bindParam('k', $key, PDO::PARAM_INT);
    $bind = $stmt->bindValue('lb', 'hello', PDO::PARAM_STR);

Then you execute the query:


    $stmt->execute();

If the request returns some results (like a SELECT), you can use $stmt like when calling query() on a connection object to retrieve results. See below.

Because you are using a prepared statement, it is possible to reuse the $stmt object to bind other values and to retrieve corresponding results, without recalling prepare().


    $stmt = $cnx->prepare('INSERT INTO `labels_test` (`key`,`lang` ,`label`) VALUES (:k, :lg, :lb)');

    // we insert a first record
    $bind = $stmt->bindParam('lg', $lang);
    $bind = $stmt->bindParam('k', $key, PDO::PARAM_INT);
    $bind = $stmt->bindValue('lb', 'hello', PDO::PARAM_STR);
    $stmt->execute();

    // we insert a second record, by setting only parameters that have different
    // values
    $bind = $stmt->bindValue('k', 'good.bye', PDO::PARAM_INT);
    $bind = $stmt->bindValue('lb', 'Good Bye', PDO::PARAM_STR);
    $stmt->execute();

An alternative to bindParam() and bindValue(), is to give parameter values directly to the execute() method.


    $stmt = $cnx->prepare('INSERT INTO `labels_test` (`key`,`lang` ,`label`) VALUES (:k, :lg, :lb)');

    $stmt->execute(array(
        'lg' => $lang,
        'k' => $key,
        'lb' => 'hello'
    ));

Note that the oci driver does not support parameters given to execute().

Result Set

jDbResultSet is the object you retrieve after a SELECT query (via query(), limitQuery() or prepare()).

Its fetch() method allows to retrieve the records one by one. Its fetchAll() method allows to retrieve all records in a PHP array in one shot.

With these methods, and when using it as an iterator, jDbResultSet returns records as objects. If you prefer to have associative arrays, you should use fetchAssociative() and fetchAllAssociative().

jDbResultSet implements also the Iterator interface, so you can use it in some case, like in a foreach statement.


  $conn = jDb::getConnection();

  $rs = $conn->query('SELECT name, firstname FROM users');
  $result = '';
  foreach ($rs as $record) {
     $result .= 'name = '.$record->name.' firstname = '.$record->firstname."\n";
  } 

The objects returned by the jDbResultSet object, are anonymous object (StdClass in PHP). If you want to have objects which are based on a specific class, you should indicate it with the setFetchMode():


  class User {
    ...
  }

  $conn = jDb::getConnection();
  $rs = $conn->query('SELECT name, firstname FROM users');

  $rs->setFetchMode(jDbConnection::FETCH_CLASS, 'User');
  
  $result = '';
  foreach ($rs as $record) {
     $result .= 'name = '.$record->name.' firstname = '.$record->firstname."\n";
  } 

When an object jDbResultSet is destroyed, the associated database resource is deleted automatically. But you can delete it explicitly with the method free().

For other methods of jDbResultSet, see the API reference.

Using results in a template

Of course, you can assign a jDbResultSet object in a template variable.

For example, in a controller:


  $conn = jDb::getConnection();

  $rs = $conn->query('SELECT name, firstname FROM users');

  $template->assign('results', $rs);

And in the template:


  <table>
    <tr> <th>name</th> <th>firstname</th></tr>
  {foreach $results as $user}
    <tr> 
       <td>{$user->name|eschtml}</td>
       <td>{$user->firstname|eschtml}</td>
    </tr>
  {/foreach}
   </table>

Transactions

jDb allows you to execute your queries into transactions. Of course, the driver and the database should support this feature.

To start a transaction, you should call the beginTransaction() method. Then you execute your queries. Then you can validate the transaction by calling the commit() method, or you can cancel it by calling the rollback() method.


   $conn = jDb::getConnection();

   $conn->beginTransaction();

   try {
        $conn->exec(...);
        $conn->query(...);
        //....
        $conn->commit();
    }
    catch (Exception $e) {
        $conn->rollback();
    }