simply put: Database - Learn about PIE's database abstraction layer and its benefits.

Database abstraction layer

PIE provides excellent facilities for using databases. There are several reasons to use them in your app, including:

• Automatically sanitizing database queries to prevent sql injection attacks. Without this, you might expose your site to serious security risks.
• It fires PIE's events, allowing you to attach a hook wherever you may need it later, such as for logging database queries or to implement horizontal partitioning.
• Writes correct SQL code for you, and uses the the PHP compiler to ensure correct syntax, even with arbitrary expressions.

For now, only the MySQL is supported. If you'd like to contribute to the project, you are more than welcome to write an adapter for your favorite DBMS, such as PostgreSQL or SQLite.

PIE also provides object-relational mapping, which we will cover in its own article.

Connections

Your app can have one or more database connections, which you would normally set up in the db/connections config field. Note that this information might be different for each development machine, and therefore connections should be specified APP_DIR/local/app.json config file. Here is an example:

{
  "db": {
    "connections": {
      "youMixer": {
        "prefix": "ym_",
        "dsn": "mysql:host=localhost;dbname=youMixer_db",
        "username": "youMixer",
        "password": "somePassword",
        "driver_options": {
          "3": 2
        }
      }
    }
  }
}

Behind the scenes, PIE's database abstraction layer uses PDO, and all the connection information is used to establish a PDO connection (exception the prefix, which is used to prefix table names).

To use a connection, you could write the following:

$youmixer_db = Db::connect('youMixer'); // returns a Db object for the youMixer connection

but the connection is not made until you actually execute the first query against the database. If you call Db::connect multiple times with the same connection name, it will just return the same object.

Making queries

PIE's DB abstraction layer includes several classes, which are all found in the "Db" package. Among them:

• The Db class represents a database connection. Among other things, it is a wrapper for PDO objects.
• The Db_Query class represents a database query (customized to the particular DBMS that the connection was made to), that you are in the process of building and then executing.
• The Db_Result class represents a result set from executing a query. Among other things, it is a wrapper for PDOStatement objects.
• The Db_Row class represents a row in the database. It is used by the ORM, and is discussed in the article about Classes and Models.

When you get a Db object, you can call methods on it, such as select, insert, update and delete. They return an object of type Db_Query. That class, in turn, has its own methods, most of which also return a Db_Query object. Here are some examples demonstrating a bunch of functionality at once:

$db = Db::connect('youMixer');

$q = $db->select('*', 'mix')
   ->where(array(
      'name' => 'My Mix',               // 'My Mix' will be sanitized, and = will be inserted
      'by_user_id <' => 100             // Here, an extra = is not inserted
      'title LIKE ' => '%somethin%'     // Here it's also not inserted - notice the space after "LIKE".
   ))->orderBy('songs_count', false)    // you can chain these as much as you need
   ->limit(5, 1);

$q2 = $q->orWhere('id < 3')             // add more things to the query at any time

$q3 = $db->update('mix')
   ->set(array(
     'a' => 'b', 
     'c' => 'd'
   ))->where(array(
	 'a' => 5
   ));

$q4 = $db->delete('mix')
   ->where(array('id' => $mix_id)); // $mix_id will be sanitized

$q5 = $db->insert('mix', compact('name', 'by_user_id'));

$q6 = $db->rawQuery("SELECT name, by_user_id FROM mix");

Executing queries

There are a couple ways you can execute a query and fetch the results. One way is to get a Db_Result object, and then fetch:

$r = $q->execute();
$r->fetchAll(PDO::FETCH_ASSOC);

// or all in one line:
Db::connect('youMixer')
   ->select('*', 'mix')
   ->where('id > 5')                  // you can pass strings to "where"
   ->execute()->fetchAll();

A second way involves calling "fetch"-type methods directly on a query:

$q->fetchAll(PDO::FETCH_ASSOC); // just fetch an array

// or all in one line:
Db::connect('youMixer')->select('*', 'mix')
   ->where('id > 5')
   ->fetchAll(PDO::FETCH_BOTH); // all in one line

The second way implicitly executes the query (and obtains a Db_Result) before fetching. Besides being shorter, the second way makes use of caching based on the query's SQL content. That means, if you use it twice in the same PHP script, it will only hit the database once, having cached the results.

An actual (PDO) connection is made to the database only when the first query is executed against that connection. You can also hook the "db/query/execute" event for your own needs. For example, you may want to take this opportunity to implement sharding in the application layer.

Database expressions

By default, PIE's database library sanitizes values that you pass when building queries. For example, if you wrote:

$results = $db->select('*', 'mix')
   ->where('time_created >' => "CURRENT_TIMESTAMP - INTERVAL 1 DAY")
   ->fetchAll(PDO::FETCH_ASSOC);

then PIE would treat the value as a string, and sanitize it as such. If what you really want is to insert an arbitrary database expression instead of a value, you would use the Db_Expression class:

$results = $db->select('*', 'mix')
   ->where('time_created >' => new Db_Expression("CURRENT_TIMESTAMP - INTERVAL 1 DAY"))
   ->fetchAll(PDO::FETCH_ASSOC);

In general, you can nest database expressions as much as you want, and PIE will write the SQL for you. When doing this, valid PHP code will automatically produce evenly matched parentheses, so you don't have to worry about it. Here are some examples:

// Subquery:

$results = $db->select('*', 'mix')
   ->where(array(
     'id IN ' => $db->select('id', 'mix')->where('name' => 'foo')
   ))->fetchAll(PDO::FETCH_ASSOC);

// Multiple clauses and lots of crazy stuff:

$criteria = new Db_Expression(
	"id > 4 AND ", 
	array('name' => "some name"), " AND ",
	new Db_Expression("name IS NULL OR", array('name' => "blah"))
);
$results = $db->select('*', 'mix')
   ->where($criteria)
   ->fetchAll(PDO::FETCH_ASSOC);

You can use Db_Expression anywhere that you would provide a value. In fact, Db_Query objects are Db_Expressions themselves, and can be used in place of one.

Other functionality

The PIE database library also has a few other functions you might be interested in, such as $db->insertManyAndExecute, $db->rank, $db->fromDateTime, $db->toDateTime, and $db->scriptToQueries. It also has and $db_result->fetchDbRows($class_name = null) and $db_query->fetchDbRows($class_name = null), which we will explore in the Classes and Models article.