Software For Thought

Rebar-MySQL

Go Back To The Rebar Project Page

Go Directly To The Documentation

Table of Contents

  1. Releases
  2. Using the Library
  3. Writing Facets
  4. Insert Queries
  5. Executing Queries in your Facet Class
  6. Select Queries
  7. Query Parameters
  8. Executing Select Queries
  9. Update Queries
  10. Delete Queries
  11. Putting it all Together: Using your Facet in your Application
  12. Additional Features: Limits, Orders, and Joins
  13. Control Features: Table Locking and Atomic Transaction Control

The Rebar-MySQL library is a collection of PHP classes designed to make MySQL and MariaDB access in PHP both safer and easier.

This library performs two functions:

-- Elimination of hand-writing SQL statements. Instances of Query classes are created instead. This prevents SQL syntax errors that are often difficult to detect and debug.

-- A better way of using prepared statements. Prepared statements prevent injection attacks, but their practical implementation can be difficult. A better software interface is overlaid on top of the official mysqli library that provides a far easier way of preparing, executing and retrieving results from a mysqli prepared statement.

Releases

Using The Library

This library thinks about your application in terms of a system that is made up of one or more Facets. What is a Facet? It is meant to be a logical partition of the business logic of your application.

For example, if you had a web site that sold t-shirts then potential facets would be the code that manages inventory, the code that manages payments, code that manages shipping information, et cetera...

Writing Facets

You write a facet by extending the MysqlFacet class. This class is meant to hold application-specific atomic database actions. For the remainder of this document we will be writing a hypothetical one-facet application that is a simple list of members of a website. Our facet class has four atomic actions:

class MyWebsiteMembers extends MysqlFacet {

    function CreateMember ($member_name) { ... }

    function GetMemberByID ($member_id) { ... }

    function UpdateMemberName ($member_id, $member_name) { ... }

    function DeleteMember ($member_id) { ... }

}

This example application will be implemented fully in the following sections. This document will overview the four types of basic database transactions that one would find themselves performing: Insert (Create Member), Select (Get Member By ID), Update (Update Member Name), and Delete (Delete Member).

Insert Queries

The InsertQuery class represents a SQL Insert statement.

InsertQuery::__construct($tablename, $insertfields)

The syntax here is very simple: $tablename expects a string which is the name of the table you wish to insert rows into. $insertfields specifies the fields in the table you wish to insert data into...

Remember, these objects are going to be compiled by the library into prepared statements. One of the hallmarks of a prepared statement library is the ability to specify an expected data type for each input field in the query. We specify two things for each field in $insertfields: we need to specify the name of the field, and we need to specify the expected data type.

The Rebar library defines the following three primitive data types:

Query::$DTYPE_D -- Floating point (double) number
Query::$DTYPE_I -- Integer
Query::$DTYPE_S -- String

Let's look at the problem from another angle. Our sample system, let's say, is a single table called MemberTable. It has two fields: an auto-incrementing integer field called MemberID, and a text field called MemberName. If we were to type out the raw SQL code for CreateMember, it would probably look like this:

Insert into `MemberTable` (`MemberName`) values (?)

All we expect is a string value for the MemberName field. A properly defined InsertQuery class, therefore, would look like the following:

$query = new InsertQuery('MemberTable', array(array('MemberName', Query::$DTYPE_S)));

That's it! I promise, it starts to make a lot of sense once you start to see some practical examples.

Executing Queries in your Facet Class

We have the InsertQuery created, but now what do we do with it?

The MysqlFacet class defines several protected functions that you use in your code to execute the query, analyze the output, and capture the results:

MysqlFacet::queryPrepare($query)
-- Prepares the query class for execution by the library

MysqlFacet::queryExec($inputs = null)
-- Executes the prepared query, with input if the query requires it. Please note that you can call this method multiple times if your query does not change.

MysqlFacet::lastSelectResult(&$select_result)
-- Retrieves the result rows from a Select query... more on this later.

MysqlFacet::lastInsertID(&$insert_id)
-- Retrieves an auto-incremented ID value, if your table is so equipped...more on this immediately below.

MysqlFacet::queryFinish()
-- Closes the query. You must call this before any subsequent call to queryPrepare, otherwise an exception will occur.

Ok then, well, let's write our CreateMember function and see how this all works. Please note that we will also be using the protected function MysqlFacet::lastInsertID, which retrieves the last generated primary key value on tables set up to have an auto-incrementing integer primary key.

function CreateMember ($member_name) {

    $query = new InsertQuery('MemberTable', array(array('MemberName', Query::$DTYPE_S)));

    $this->queryPrepare($query);

    $this->queryExec(array($member_name));

    $this->lastInsertID($new_member_id);

    $this->queryFinish();

    return $new_member_id;

}

Take important notice of queryExec's $inputs argument: it accepts an array of input values into the query. How many values should be in the array? Well, it depends on how many inputs that you specified are in your query...

Select Queries

The SelectQuery class is used for executing what would be a SQL Select statement on the database:

SelectQuery::__construct( $table_fields, $table_names, $left_joins = null, $right_joins = null, $parameters = null, $orders = null, $limits = false )

The SelectQuery class supports a lot of features. Let's focus on the most simple use case: selecting a member from our database by their unique and automatically assigned ID number.

We need to do several things: we need to tell the class what fields we want, what table we want to select from, and what are the parameters that will be used to match rows.

In our theoretical system, the SQL code to implement GetMemberByID would be the following:

Select `MemberID`, `MemberName` from `MemberTable` where `MemberID` = ?

The equivalent SelectQuery instance would be created in the following way:

$query = new SelectQuery(
    array('MemberID', 'MemberName'),
    'MemberTable',
    null,
    null,
    array(array('MemberID', Query::$OPER_EQ, Query::$DTYPE_I))
);

Query Parameters

One of the more complicated topics to understand in this library is how to structure the parameters argument. This argument is used not only for SelectQueries, but also for UpdateQueries and DeleteQueries as well, so it is important to understand how it works.

The simplest use case as demonstrated above is to match a primary key value, which involves looking at a field in the table ('MemberID') that equals (Query::$OPER_EQ) an integer value (Query::$DTYPE_I).

However, what if we want to match rows that are not just equal? What if we want to match all rows greater than or less than? We would just use a different operator. The full list of available operators is as follows:

Query::$OPER_EQ -- Equal to
Query::$OPER_NE -- Not equal to
Query::$OPER_GT -- Greater than but not equal to
Query::$OPER_GE -- Greater than or equal to
Query::$OPER_LT -- Less than but not equal to
Query::$OPER_LE -- Less than or equal to

That's all well and good if we wanted to match a single field. But what if we wanted to query for rows that match a criteria of multiple fields? Say for instance -- in our example system -- you wanted to find rows of uses that fit within a range of ID numbers:

Select [...] where `MemberID` >= ? and `MemberID` <= ?

Take notice above how our array that defined a parameter statement took three values: a field name, an operator, and a data type. This only applies to the first parameter statement. All subsequent parameter statements require four values.

These subsequent parameter statement arrays are right-shifted. The first value of the array defines the boolean relationship between this statement and the statement immediately preceding.

So back to the member ID range example: the parameter argument becomes the following:

array(
    array('MemberID', Query::$OPER_GE, Query::$DTYPE_I),
    array(Query::$BOOL_AND, 'MemberID', Query::$OPER_LE, Query::$DTYPE_I)
)

Boolean relationships can be the following:

Query::$BOOL_AND -- And relationship to the statement preceding.
Query::$BOOL_OR -- Or relationship to the statement preceding.

So, in summary, every parameter array after the first parameter array must specify it's boolean relationship to the parameter immediately preceding it.

It is possible to have nested parameters, as well. How to achieve this is explained in further detail in the documentation.

Executing Select Queries

Writing our GetMemberByID function entails using the protected method MysqlFacet::lastSelectResult(&$select_result)

function GetMemberByID ($member_id) {

    $query = new SelectQuery(array('MemberID', 'MemberName'), 'MemberTable', null, null, array(array('MemberID', Query::$OPER_EQ, Query::$DTYPE_I)));

    $this->queryPrepare($query)

    $this->queryExec(array($member_id))

    $this->lastSelectResult($result_rows)

    $this->queryFinish();

    if (count($result_rows) > 0) {

        return $result_rows[0];

    } else {

        return null;

    }
}

Results are returned as an array of row values, represented as associative arrays. Since in this example we can safely assume that there will only ever be zero or one matched rows (dictated by the design of our hypothetical schema), then we simply added some code that returns only the associative array if the user exists, or null to indicate that the user does not exist.

Update Queries

The UpdateQuery class is used for executing what would be a SQL Update statement on the database:

UpdateQuery::__construct ( $tablename, $updatefields, $parameters = null, $limits = false )

Like the SelectQuery, this class as well has a $parameters argument. It functions in exactly the same way.

The $updatefields argument works in the same way as the $insertfields argument in the InsertQuery class.

In our example system, we would use UpdateQuery in order to construct UpdateMemberName. Equivalent SQL code would be the following:

Update `MemberTable` set `MemberName` = ? where `MemberID` = ?

Using the UpdateQuery, our function would be structured as follows:

function UpdateMemberName ($member_id, $member_name) {

    $query = new UpdateQuery('MemberTable', array(array('MemberName', Query::$DTYPE_S)), array(array('MemberID', Query::$OPER_EQ, Query::$DTYPE_I)));

    $this->queryPrepare($query)

    $this->queryExec(array($member_name, $member_id));

    $this->queryFinish();

}

Notice the order of elements in the array that is passed into queryExec: it is of the order that we defined data types in the query: first was the value for 'MemberName', then the value for 'MemberID'.

Delete Queries

The DeleteQuery class is used for executing what would be a SQL Delete statement on the database. In essence, it is a simplified UpdateQuery:

DeleteQuery::__construct ( $tablename, $parameters = null, $limits = false )

Parameters work the same as in SelectQuery and UpdateQuery. In our example system, we would use DeleteQuery in order to construct DeleteMember. Equivalent SQL code would be the following:

Delete from `MemberTable` where `MemberID` = ?

The function is structured using DeleteQuery as follows:

function DeleteMember ($member_id) {

    $query = new DeleteQuery('MemberTable', array(array('MemberID', Query::$OPER_EQ, Query::$DTYPE_I)));

    $this->queryPrepare($query);

    $this->queryExec(array($member_id));

    $this->queryFinish();

}

Putting It All Together:
Using Your Facet In Your Application

If you have been following along this tutorial, then you have a fully written facet file at includes/MyWebsiteMembers.php. The directory structure of your application should look like the following:

./
|-- includes/
    |-- rebar-mysql.php
    |-- rebar-mysql/
    |    |-- (...library files...)
    |-- MyWebsiteMembers.php

However you wish to create and handle forms, well that's on you. I am only going to focus on accessing and using our facet file.

The first step is to connect to the MySQL database. You will need some kind of mechanism for storing MySQL access credentials, that design decision is also solely up to you. The general purpose code for connecting your facet to the database looks like the following:

include 'includes/rebar-mysql.php';
include 'includes/MyWebsiteMembers.php';
$mysql = new MysqlConnection();
try {
    $mysql->connect($my_mysql_hostname, $my_mysql_targetdb, $my_mysql_username, $my_mysql_password);
    $database = new MyWebsiteMembers($mysql);
} catch (Exception $e) {
    // There was an error while trying to connect to the database
}

Take note that you may pair the same MySQL connection object to multiple facet objects, or you may create additional MySQL connection objects that may be bound to different facet objects. This all depends on the design of your application.

Once a connection to your database has been established, and the connection object bound to your facet object, then you are free to make calls to your facet functions:

try {
    $new_member_id = $database->CreateMember($new_member_name);
} catch (Exception $e) {
    // There was an error
}

Once finished querying your database, the only thing left to do is to close the connection:

$mysql->close();

Additional Features: Limits, Orders and Joins

Limits

The SelectQuery, UpdateQuery, and InsertQuery have a boolean argument called $limits. This argument controls whether limits should be applied to your queries. Limits are always of the format "Limit ?,?" where the first integer value is the start of the set and the second integer value is the length of the set.

To use limits in your queries, set this argument to True when creating your query. Then, when executing the query, add the limit values as the last two values of your array that you pass to queryExec.

Orders

Ordering is an additional feature of the SelectQuery. Ordering represents the Order By clause in plain SQL. The ordering argument expects an array of arrays of which those arrays have two values: the name of the field you wish to order by, and the ordering direction:

Query::$ORDER_A -- Ascending order
Query::$ORDER_D -- Descending order

Joins

The SelectQuery supports left joins and right joins of other tables. This feature is explained in the documentation.

Control Features:
Table Locking and Atomic Transaction Control

While not covered in this tutorial, the library also contains classes for performing locking and transactional control of queries. These classes may be used to provide explicit locking information to the database, or to signal to the database that you wish to begin an atomic transaction.

Locks

Table locks can be specified by using a LockQuery class. This query takes as an argument an array of arrays of which those arrays have two values: the name of the table you wish to lock and the type of lock that should be placed on the table:

Query::$LOCK_R -- Read lock
Query::$LOCK_W -- Read and write lock

Once a LockQuery has been successfully executed, the lock is now active on the database. The lock can be released by creating and executing an instance of the UnlockQuery class. This class takes no arguments.

Transaction Control

Transaction control is achieved through the use of three classes: the TransactQuery class, the CommitQuery class, and the RollbackQuery class. None of these three classes take any arguments. A transaction begins by creating an executing an instance of TransactQuery. You may then either commit the changes to the database by creating and executing an instance of CommitQuery, or you may roll back changes by creating and executing an instance of RollbackQuery. Pretty straightforward.

Further information can be obtained by reading the documentation.

Go Back To The Top

Go Back To The Rebar Project Page