Database Controllers
To effectively use a framework built of taking care of the data layer, there needs to be a way to easily communicate with the database. While most backend web developers (and many front end!) are quite capable of writing their own SQL queries, let’s face it; that can be a pain, and very repetitious. That’s why there are a couple classes to help streamline the whole process.
The two classes I introduced do two things, one is a query builder, and one interacts with the database. Simple. The query builder is the interesting one here, so I’ll spend the majority of this post on that.
The query builder has all static functions. The easiest way to show you the functions and their requirements are through a sudo-interface:
| class DB_Query_Builder { | |
| // $table: name of the table | |
| // $conditions: array of table where keys are column names, and values are variables they should equal | |
| // $selection: a string of the values to be returned | |
| public static function select_query( $table, $conditions, $options=array() ) { ... } | |
| // $table: base table to build the query from | |
| // $selects: the different columns to be returned (need to include tables!) | |
| // $joins: an array of arrays containing the table and columns to join on: | |
| // array( | |
| // join-table, join-table column, base-table column | |
| // ) | |
| public static function join_query( $table, $selects, $joins, $conditions=false, $options=array() ) { ... } | |
| // $table: name of the table | |
| // $insertions: array of values to insert, where keys are column names, and values are variables to be added | |
| public static function insert_query( $table, $insertions ) { ... } | |
| // $table: name of the table | |
| // $insertions: array of values to insert, where keys are column names, and values are variables to be added | |
| // $conditions: array of table where keys are column names, and values are variables they should equal | |
| public static function update_query( $table, $insertions, $conditions=false ) { ... } | |
| // $table: name of the table | |
| // $conditions: array of table where keys are column names, and values are variables they should equal/ | |
| public static function delete_query( $table, $conditions ) { ... } | |
| } |
I realize that’s a bit much to go over, but it’s a good reference for what each function takes. It’s pretty straightforward: a select query returns a select query statement. A join also returns a select statement, but with (at least one) inner join. Here are some examples to help you understand exactly what it wants:
| // Here is our example SELECT query. | |
| // We just want the last 5 posts from the posts table of type 'page'. We want to order them by title, descending. | |
| $query = DB_Query_Builder::select_query( | |
| 'posts', | |
| array( | |
| 'type' => 'page' | |
| ), | |
| array( | |
| 'order' => 'title', | |
| 'direction' => 'DESC', | |
| 'limit' => 5 | |
| ) | |
| ); | |
| // Here's an example JOIN query. We just want all posts, and their author info | |
| // Note, for that third parameter, we can join as many tables as we want! | |
| $query = DB_Query_Builder::join_query( | |
| 'posts', | |
| array( 'posts.*', 'users.name', 'users.email' ), | |
| array( | |
| array( 'users', 'posts.author', 'users.email' ) | |
| ), | |
| array( | |
| 'posts.type' => 'post' | |
| ), | |
| array() | |
| ); |
You get the idea. All the functions are similar, so I’ll skip giving examples for them all, and we’ll move on to executing the queries. It’s pretty easy:
| // run a select query | |
| $results = (new Database_Interface)->query( $query ); | |
| // let's run an insert query | |
| $results = (new Database_Interface)->insert( $query ); | |
| // and here's an update | |
| $results = (new Database_Interface)->update( $query ); |
Yep, it’s that easy. Nothing else to it. It’ll return whatever values are pulled from the database. That’s the basics of using the database classes! Yes, the query builder does clean the queries - so while you should be carefull, you can rest easier about SQL injections.