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 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. 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.
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.
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 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();
$conn->exec(...);
$conn->query(...);
//....
if($ok)
$conn->commit();
else
$conn->rollback();
