Section: Executing queries
| « Profiles and configuration | ^ jDb: accessing to SQL database | Schemas and tools » |
− Table of content
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();
}
