Section: Profiles and configuration
| ^ jDb: accessing to SQL database | Executing queries » |
− Table of content
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 an example of dbprofils.ini.php file :
[default]
driver="mysql"
database="jelix"
host= "localhost"
user= "jelix"
password= "jelix"
persistent= on
force_encoding=true
There is a "default" section. Each section corresponds to a connection profile. A "default" section specify the profile to use by default.
You can use also an alias:
default = foo
[foo]
driver="mysql"
database="jelix"
...
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 thejDbConnectionobject. This method takes one parameter: the table name.
mysql profile ¶
Possible parameters:
- driver : should be "mysql"
- database : the database name
- host : the server name
- user et password : the login and the password to use for the connection
- persistent : says if the connection should be persistent ("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 schema 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. You can use the shortcuts like "app:", "lib:" or "var:" to indicate a path inside the application directory, the lib directory, or the var directory.
- persistent : says if the connection should be persistent ("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 profile ¶
Since Jelix 1.2, you can indicate into a mysql, postgresql or else, to use PDO for the connection, by just adding the parameter usepdo=on.
Or you can still use the deprecated way to use PDO, by creating a profile using these parameters:
- driver : should be "pdo"
- dsn : contains all parameters for the connection as indicated in the PDO documentation on php.net. To indicate the path for a sqlite database, you can use shortcuts like "app:", "lib:" or "var:" to indicate path of the application, the lib directory or the var directory.
- 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=
Profile aliases ¶
At the begining of the file, you can specify some aliases to existing profile. It is useful for modules which use a specific profile name, and you want to assign this profile name to an existing profile. jAcl2 uses an aliases, jacl2_profile. You can create a new profile named "jacl2_profile", or create an alias:
jacl2_profile=default
[default]
driver=mysql
...
Virtual profile ¶
jDb supports connexion to a profile not declared in dbprofils.ini.php 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.
