Table of Contents
The Db plugin provides database features over PDO. Thus, it is advice to know how to use PDO before using this plugin. Check out http://php.net/pdo for more information.
You can connect to a database either automatically or manually. The first method can be achieved by setting some configuration.
You need to specify a dsn, a username and a password as shown below. You also need to define the “autoconnect” configuration key to true.
Example 5.1. Automatically connecting to a database
Atomik::set('plugins/Db', array(
'dsn' => 'mysql:host=localhost;dbname=mydb',
'username' => 'root',
'password' => '',
'autoconnect' => true
));
To connect manually, use the Atomik_Db::connect() method.
Example 5.2. Connecting to a database manually
Atomik_Db::connect('mysql:host=localhost;dbname=mydb', 'root', '');
The plugin then manages a PDO instance. The instance is stored in Atomik_Db::$pdo.
The Atomik_Db class provides three alias methods to PDO methods:
query(), exec() and prepare().
The two last one behave exactly the same. However Atomik_Db::query() is a little
different.
Under the hood it creates a PDO statement using prepare and executes it. Thus, the method also allows an additional argument which can contains an array to pass to the execute call of the statement.
Example 5.3. Using Atomik_Db::query()
$results = Atomik_Db::query('select * from posts');
$results = Atomik_Db::query('select * from posts where id=?', array(1));
Example 5.4. Using other PDO methods with Atomik_Db
$statement = Atomik_Db::prepare('select * from posts');
$statement->execute();
$results = Atomik_Db::exec("insert into posts (content) values ('my new post')");
The Atomik_Db class also provides two powerful methods to query the database:
Atomik_Db::find() and Atomik_Db::findAll(). They are
exactly the same but the first one will return only one record whereas the second all of them.
Conditions are specified as an associative array. The keys are database's fields. You can specify conditions in two different ways.
The first way only allows you to query one table at once. The first argument of the method should be a table name and the second one the fields array. An sql string can also be used instead of the array.
Example 5.5. Finding records from one table
// all records from the posts table
$results = Atomik_Db::findAll('posts');
// only records from the author 3
$results = Atomik_Db::findAll('posts', array('author' => 3));
// or
$results = Atomik_Db::findAll('posts', 'author = 3');
The second way allows you to query multiple tables (but without using joins). Instead of specifying the table name as a string, you must provide an array as the first argument where keys are table names and values the fields array.
Example 5.6. Finding records from multiple tables
// all records from the posts and users tables
$results = Atomik_Db::findAll(array('posts', 'users'));
// only records from the author 3
$results = Atomik_Db::findAll(array('posts', 'users' => array('id' => 'posts.user_id')));
// will generate select * from posts, users where users.id = posts.user_id
You can also specify an order by and a limit clause. Respectively as the third and fourth arguments.
Example 5.7. Finding records with order by and limit clauses
// all records from the posts table ordered by creation_date
$results = Atomik_Db::findAll('posts', null, 'creation_date ASC');
// the first 10 records from the posts table
$results = Atomik_Db::findAll('posts', null, '', '10');
// the first 10 records from the posts table ordered by creation_date
$results = Atomik_Db::findAll('posts', null, 'creation_date' , '10');
// records 10 to 20 from the posts table
$results = Atomik_Db::findAll('posts', null, '', '10, 10');
Example 5.8. Working with the result of find methods
$posts = Atomik_Db::findAll('posts');
foreach ($posts as $post) {
echo $post['title'];
echo $post['content'];
}
The Atomik_Db::insert() method can be use to insert data into the database. It takes
as first argument the table name and as second an associative array where keys are fields name.
Example 5.9. Inserting data into the database
Atomik_Db::insert('posts', array('title' => 'my first posts', 'content' => 'hello world'));
// will execute: insert into posts (title, content) values('my first post', 'hello world')
All values are automatically escaped.
Atomik_Db::update() works pretty much the same. However it takes as the last argument
an array of conditions (the same way as in find methods) or an sql string.
Example 5.10. Updating data
Atomik_Db::update('posts', array('content' => 'updated hello world'), array('id' => 1));
// will execute: update posts set content = 'updated hello world' where posts.id = 1
Finally, the Atomik_Db::delete() works the same as find methods (without the order by and
limit arguments).
Example 5.11. Deleting data
Atomik_Db::delete('posts', array('id' => 1));
// will execute: delete from posts where posts.id = 1
Atomik_Db::delete(array('posts', array('id' => 1)));
// will execute: delete from posts where posts.id = 1
Atomik_Db::delete('posts', 'id = 1');
// will execute: delete from posts where id = 1
The static class Atomik_Db manages an instance of Atomik_Db_Instance. It's
this last class which actually handles all the work. As most applications will only have one database connection, the first class
allows to quickly access the only instance of the later class.
You can access the instance of Atomik_Db_Instance used by Atomik_Db by calling
Atomik_Db::getInstance(). You can also set the instance using Atomik_Db::setInstance().
You can creates multiple database connections by creating instances of Atomik_Db_Instance. This class works
exactly the same as described in this chapter, only all calls should not be static.
Example 5.12. Using Atomik_Db_Instance
$db = new Atomik_Db_Instance();
$db->connect($dsn, $username, $password);
$posts = $db->findAll('posts');