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.

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).

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.

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:

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.

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:

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).

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:

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:

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:

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:

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:

Joins

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.