Quick links: Content - sections - sub sections
EN FR
The page corresponding to the latest stable release can be seen in the Jelix 1.8 Manual

Jelix has an abstract database access system : jDb. jDb provides an API common to every databases. For the moment, featured drivers are those for :

  • mysql
  • postgresql
  • sqlite
  • oracle (oci)

And also the last one, pdo, allowing to specify to jDb to use pdo instead of the abstract classes internal to jDb.

If in the list above, there isn't a driver for your database, and if PDO doesn't provide the driver too, you can create your own driver.

Notice that, although jDb is an common API to all databases, it can't adapt your queries to specificities of the database.

Profile and configuration

To be able to access a database, you need of course to specify the connection parameters in a configuration file. This file is dbprofils.ini.php by default in var/config/. You can have one with a different name, and you have then to specify this name in the main config file of your application.

You can define several connections named profiles. Thus, you can define connection for the production database, development database, or also the different bases on which lies your application.

Here is the dbprofils.ini.php file :


default = foo

[foo]
driver="mysql"
database="jelix"
host= "localhost"
user= "jelix"
password= "jelix"
persistent= on
force_encoding=true

There is a "foo" section. Each section corresponds to a connection profile. A "default" parameter, specify the profile to use by default.

In a section, you have several parameters. Their number and name can be different according to the driver used, but some of them can be used in all profile

  • driver: it indicates the driver name
  • table_prefix: allow to indicate a prefix for all table when using a DAO with a profile indicating a table prefix, this prefix is added automatically to the table names indicating in the dao files. When you construct your SQL queries, you should use the prefixTable() method of the jDbConnection object. This method takes one parameter: the table name.

mysql profile

Possible parameters:

  • driver : should be "mysql"
  • database : the database name
  • host : the serveur name
  • user et password : the login and the password to use for the connection
  • persistent : says if the connection should be persistant ("on") or not ("off")
  • force_encoding : says if the current charset should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.

postgresql profile

Possible parameters:

  • driver : should be "pgsql"
  • database : the database name
  • host : the server name. If you give an empty value, the connection will be set over an unix socket.
  • port : TCP port to use for the connection. Don't indicate this parameter if you want to use the default port.
  • user and password : the username and the password to use for the connection. Don't indicate this parameters if you want to use the default user/password indicated in environment variable in the operating system.
  • persistent : says if the connection should be persistent ("on") or not ("off")
  • force_encoding : says if the current character set should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
  • timeout : Number of second allowed before a timeout.
  • single_transaction : if set to on, all queries executed in a same page will be sent in a same transaction (between a BEGIN; and a COMMIT;). Default: off
  • search_path: the list of schemas where table are getting from, if the default schema of the connection doesn't correspond to the schema used by the application

sqlite profile

Possible parameters:

  • driver : should be "sqlite"
  • database : the database file
  • persistent : says if the connection should be persistant ("on") or not ("off")

Do not forget that the sqlite database files must be placed in the var/db/sqlite/ directory of your application. Theses files and the directory must have read and write permissions for your webserver user.

PDO profil

Possible parameters:

  • driver : should be "pdo"
  • dsn : contains all parameters for the connectionas indicated in the PDO documentation on php.net.
  • user et password : the login and the password to use for the connection, if needed.
  • force_encoding : says if the current charset should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.

[bar]
driver=pdo
dsn= "mysql:host=localhost;dbname=test"
user=
password=

Virtual profile

jDb supports connexion to a profile not declared in dbprofils file. It supports virtual profiles, useful for database whose information is known only during the execution.

A virtual profile must be created before any jDb method call. Use createVirtualProfile method and pass it a name and an array of parameters. The latters are the same as described above of course.


   $params = array(
       'driver'=>'mysql',
       'host'=>'localhost',
       'database'=>'jelix',
       'user'=>'toto',
       'password'=>'blabla',
       'persistent'=>'false',
       'force_encoding'=>true
   );
   jDb::createVirtualProfile('my_profil', $params);

   $cnx = jDb::getConnection('my_profil');
   // and play with your database. see below.

Making queries

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

First, you should retrieve a jDbConnection object:


  $conn = jDb::getConnection();

The getConnection method allows you to retrieve a jDbConnection type object (or jDbConnection 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: 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.


  $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.

To execute queris, 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.

ResultSet

jDbResultSet is the object you retrieve after a SELECT query.

Its fetch() method allows to retrieve the records one by one. jDbResultSet always returns a record as an object.

Its fetchAll() method allows to retrieve everything in a PHP array in one shot.

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 (StdObject 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($rs->FETCH_CLASS , 'User');
  
  $result='';
  foreach($rs as $record){
     $result.='name = '.$record->name.' firstname='.$record->firstname."\n";
  } 

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|escxml}</td>
       <td>{$user->firstname|esxml}</td>
    </tr>
  {/foreach}
   </table>

Transactions

jDb allow you to execute your queries into transactions. Of cours, 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();

   $conn->exec(...);
   $conn->query(...);
   //....
   
   if($ok)
     $conn->commit();
   else
     $conn->rollback();

jDbWidget

jDbWidget is a class providing useful methods.


  $dbw = jDb::getDbWidget(); // instead of getConnection()

  $record = $dbw->fetchFirst("SELECT name, first_name FROM user");
  
  $liste = $dbw->fetchAll("SELECT name, first_name FROM user");

For other methods of jDbWidget, see the API reference.