LEAP provides you will the ability to execute/query SQL statements directly on a database using either the DB_Connection or DB_Connection_Pool classes. Through a set of database connection wrappers, LEAP has standardize the way all SQL statements are executed or queried on a database. In other words, you will use the same methods for querying on a MySQL database and an Oracle database. This makes working with database way easier and you will have less of a learning curve when switching between database dialects.
The query method is used only for handling read statements, i.e. select statements. This means that the query method will return a result set, which can then be looped through.
1 <?php
2 $connection = DB_Connection_Pool::instance()->get_connection('default');
3 $results = $connection->query('SELECT * FROM `user`;');
4 if ($results->is_loaded()) {
5 foreach ($results as $record) {
6 echo Debug::vars($record);
7 }
8 }
9 $results->free(); // optional
LEAP’s query method has an added feature: it allows you to specified the data type of the result set. You can choose to have the results stored as an array, an object, or a model. To do so, you just specify a second argument defining the data type to use:
1 <?php
2 $results = $connection->query('SELECT * FROM `user`;', 'object');
By default, results will be stored as an array.
Another way to query your database is to use the reader method. The reader method will return an instance of the data reader class.
1 <?php
2 $connection = DB_Connection_Pool::instance()->get_connection('default');
3 $reader = $connection->reader('SELECT * FROM `user`;');
4 while ($reader->read()) {
5 echo Debug::vars($reader->row('object'));
6 }
7 $reader->free();
For all other SQL statements, the execute method should be used. In other words, you will use the execute method for creates, inserts, updates, deletes, etc. Here is an example of a simple update statement on the ‘user’ table using the execute method:
1 <?php
2 $connection = DB_Connection_Pool::instance()->get_connection('default');
3 $connection->execute('UPDATE `user` SET `FirstName` = 'Spadefoot' WHERE ID = 1;');
You can also execute statements within a transaction.
1 <?php
2 $connection = DB_Connection_Pool::instance()->get_connection('default');
3 $connection->begin_transaction();
4 $connection->execute('UPDATE `user` SET `FirstName` = 'Spadefoot' WHERE ID = 1;');
5 $connection->execute('UPDATE `user` SET `FirstName` = 'John' WHERE ID = 2;');
6 $connection->commit();
If necessary, you can rollback a transaction by doing the following:
1 <?php
2 $connection->rollback();