Skip to content

Latest commit

 

History

History
55 lines (33 loc) · 3 KB

Database.markdown

File metadata and controls

55 lines (33 loc) · 3 KB
Raw

Database

The Epiphany PHP Framework

Understanding and using the database module

The database module is a clean interface to PHP's MySql PDO driver. It provides a simple way to use prepared statements to execute queries.

Epi::init('database');
EpiDatabase::employ('mysql', 'database', 'host', 'username', 'password');
$user = getDatabase()->one('SELECT * FROM user WHERE userId=:id', array(':id' => $userId));

First you'll need to include the database module. Then you can specify the type, database name, host, username and password. A connection won't be made to MySql until you issue a query. Here we call the one method to get a single entry from the database.

Ways to perform queries

There are three methods to perform queries. They are one, all and execute. The one method gets a single record and is useful when you want to query for a single user. The all method returns a collection of rows or array of arrays. The execute method should be used for INSERT, UPDATE and DELETE statements.

Since the library uses PDO you can write your SQL as a prepared statement with named placeholders denoted by a leading : (colon). The second parameter to all of these methods are an associative array where the key is the placeholder.

getDatabase()->execute('DELETE FROM user WHERE id=:id', array(':id' => $userId));

You do not have to worry about escaping user input as long as you use named placeholders because the PDO library will do that for you.

SELECT queries

There are two methods for retrieving data. If you want a single row from the database you should call one(). To get multiple rows you should call all().

$singleRow = getDatabase()->one('SELECT * FROM tbl WHERE id=:id', array(':id' => 1));
$manyRows = getDatabase()->all('SELECT * FROM tbl WHERE id>:id', array(':id' => 0));

INSERT, UPDATE and DELETE

To write to the database you'll want to call the execute() method.

$userId = getDatabase()->execute('INSERT INTO user(id, name) VALUES(:id, :name)', array(':id' => 1, ':name' => 'jmathai'));
$affectedRows = getDatabase()->execute('UPDATE user SET name=:name WHERE id=:id', array(':id' => 1, ':name' => 'Jaisen'));
$affectedRows = getDatabase()->execute('DELETE FROM user WHERE id=:id', array(':id' => 1));

The return value when you call execute depends on the type of query. For INSERT queries on tables with an auto_increment column you'll get the auto_increment value. For UPDATE and DELETE queries the return value is the number of affected rows.

Available methods

In addition to one, all and execute you can call insertId to get the id of the last inserted row. The execute method returns the last insert id for INSERT statements and the number of affected rows for UPDATE or DELETE statements.

one($sql, $params);
all($sql, $params);
execute($name, $value);
insertId();