MySQL/Php Introduction

Then activate this roadrunner URL in a separate tab through this link:
http://roadrunner.cs.wcupa.edu/~LOGIN/

MySQL/Php APIs and ORMs

A MySQL database is accessed using Php via an API (Application Programming Interface) which is a set of operations written in the target language (Php) meant to communicate and presumably do everything you would want to do to a MySQL database. Php provides several MySQL-based APIs, for example:
  1. MySQL (original) API: The very first API written. It performs all of its operations through function calls of the form
    mysql_OPERATION
    
    It is now deprecated, meaning that you should avoid it.
  2. The MySQLi (MySQL improved) API. It can operate either as function calls of the form:
    mysqli_OPERATION
    
    or in an object-oriented style. See the MySQLi section below for details.
  3. The PDO (Php Data Objects) API. The key point about this is that it is database-independent. See the PDO section below for details.
The MySQLi approach is often favored by textbooks, However, there are some drawbacks to its usage:

Using ORMs

One of the disadvantages in all the above APIs is the reliance on SQL operations to achieve the desired effects. SQL operations can be subtly different for different database systems. Furthermore, when using SQL directly, we are still working on the level of tables and records, whereas it is usually better programming practice to instead think of the tables as classes of sorts, and the records as objects.

An ORM (Object Relational Mapping) is a higher level API, built on top of one of the above lower level, SQL-based APIs. One selling feature of an ORM is that it provides convenience methods for automatically treating database records as objects and SQL operations as object-level function calls. In some cases ORMs provide the ability to automatically generate the tables necessary simply by programming in the intended object-oriented way.

Although it is impossible to eliminate all SQL use, it is possible to do so for most common database accesses. Summarizing, the advantages of ORMs are that they:

RedBean Php ORM

RedBean Php is a Php-based ORM based on the PDO API and so can function using a variety of database systems including MySQL, PostgreSQL, Oracle, SQLite, etc. The home page is this:
www.redbeanphp.com
RedBean Php requires Php version at least 5.3 with relevant PDO drivers installed. One of the great points about RedBean Php is the simplicity of installation. The entire installation code is held in a single file:
rb.php
All you have to do is include this file into your scripts. This file defines the crucial RedBean class R which is the creator of all the objects and source of operations through function calls like this:
R::operation
Knowing the guts of what is going on in beyond the scope of this course, but the "R::" prefix indicates that operation is a static function within the R class.

Demo Script

Log into the shell for reference.

We want to compare the output of our program with what can be viewed from the mysql client discussed in MySQL Database. Log into your roadrunner account using a Secure Shell Client like PuTTy on Windows. See Web Template for reference. Once you are in the shell, access your MySQL database like this (the easiest way):
$ mysql -pLOGIN  LOGIN
mysql> 
Save this window. As we go along we'll test the effects of what we've done by running SQL commands.

Install the RedBean Php file

The file you need for this demo can be found in the WebSample application created in the Web Template document, so make sure you've done the installation there. We will use our usual test folder:
wp
Activate this roadrunner URL in a separate tab through this link:
http://roadrunner.cs.wcupa.edu/~LOGIN/wp/
We want to copy the rb.php file from
WebSample/include/rb.php    into    wp/include
There are many ways to achieve this outcome. Let's assume we're using Dreamweaver. Within the wp, folder, if necessary, create the subfolder:
include
Locate rb.php within the WebSample application:
WebSample/include/rb.php
Right-click, select Edit → Copy, then click on
wp/include/
Right-click, select Edit → Paste.

Alternatively, you can copy it directly from roadrunner from the secure shell (PuTTy).
$ cd public_html
$ ls
$ cd wp
$ ls
$ cp /var/www/samples/rb.php  include/
$ ls include/
Note the terminal dot in the last command.

Create the initial test file

Back to Dreamweaver. Within the wp folder create the file:
db_test.php
Add our usual testing content within the body:
<pre>
<?php
/* code insertions go in here */
 
?>
</pre>
select

Add the database connection code

Insert the following Php code.
require_once "include/rb.php";
 
$db = "LOGIN";
$user = "LOGIN";
$pass = "LOGIN";
$url = "mysql:host=localhost;dbname=$db";
 
echo "connecting\n";
 
R::setup( $url, $user, $pass );
R::freeze(true);
select
Activate db_test.php from the browser. You should see "connecting" in the output. If something is wrong you will get a verbose display. This part introduces the RedBean function:
R::setup
which establishes the connection to your database. The last statement call:
R::freeze(true);
is for efficiency in later operations. RedBean can operate in a so-called fluid mode whereby it creates and alters the database tables based on the operations used. This is a very useful feature of modern ORM packages, but we will not make use of it, and so we are telling RedBean to not operate in fluid mode and thereby make subsequent operations more efficient.

Drop any previous version of the thing table

Continuing on, append this code following what we already have:
 
$sql = "drop table if exists thing";
echo "$sql\n";
R::exec($sql);
select
Again activate db_test.php from the browser. In addition to "connecting" you should see the "drop table if exists thing" output. Here we have the new function:
R::exec
This operation is considered to be "low level" because it uses the SQL commands directly. It can be used for any SQL statement which modifies the database tables in some way, like:
CREATE TABLE, DROP TABLE, UPDATE, INSERT, REPLACE, DELETE, ...
We will only need R::exec to create or drop the initial database tables.

Create the thing table

Continuing on, append this code following what we already have and then activate db_test.php from the browser.
 
$table_def = "
id int auto_increment primary key not null,
item varchar(30) unique not null,
qty int not null default  0
";
 
$sql = "create table thing ($table_def)";
echo "$sql\n";
R::exec($sql);
select
This table definition has some new features compared to the thing table used in the MySQL Database document:
  1. id int auto_increment primary key not null

    The id field is qualified first by int auto_increment which has the effect of assigning an integer id to each record starting from 1 and going up whenever a new record is added. The integers id values are never reused from deleted records.

    The primary key qualification tells the database to organize the records so as to make the id field the most common way in which records will be selected and additionally enforces the fact that ids are unique to a record.

    The not null qualification further ensures that only integer values can ever be stored in this field.
  2. item varchar(30) unique not null

    The unique qualifier says that we cannot have records with duplicate item field values. The not null qualification means that a record must specify the item field.
  3. qty int not null default 0

    We mentioned that the not null qualification means that a record must specify the item field. Adding the default 0 means that we can leave it unspecified and this value will be entered.
From the shell that you have opened running the mysql client, try this:
mysql> describe thing;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| item  | varchar(30) | NO   | UNI | NULL    |                |
| qty   | int(11)     | NO   |     | 0       |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Insert records into the table

Continuing on, append this code following what we already have. Key in the first group and then copy/paste the last 3.
// ...
 
$thing = R::dispense("thing");
$thing->item = "book";
$thing->qty = 26;
R::store($thing);
print_r($thing->getProperties());
 
$thing = R::dispense("thing");
$thing->item = "table";
$thing->qty = 11;  
R::store($thing);   
print_r($thing->getProperties());
 
$thing = R::dispense("thing");
$thing->item = "pencil";
R::store($thing);
print_r($thing->getProperties());
 
// oddly enough, this "works" too
$thing = R::dispense("thing");
$thing->qty = 55;
R::store($thing);
print_r($thing->getProperties());
We've introduced two new functions. The first,
R::dispense
is used to create a "fresh" object, meant to be stored into the thing table. As you see, we manipulate this as we do other objects, however it has a very complex underlying structure which makes it a "database" object.

The table fields item and qty correspond to data members of this object. The qty field is considered optional because it lacks the "not null" qualification.

Once we have set the fields appropriately, this function is used to actually create the record in the thing table:
R::store
As much as we might like to confirm the record contents with the statement:
print_r($thing)
you will find that this, unfortunately, gives far too much information because each $thing is actually a RedBean database object as we have indicated. Instead, we use access the properties array and print that:
print_r($thing->getProperties());
A key point to make is that this is our first real usage of the ORM. We could have achieved the same effect through an SQL INSERT statement delivered via R::exec; however, the type of record creation used in our code is far more intuitive once you're used to working with Php objects.

Activate (refresh) db_test.php from the browser. Verify the content of the table from the mysql client in the shell:
mysql> select * from thing;
+----+--------+------+
| id | item   | qty  |
+----+--------+------+
|  1 | book   |   26 |
|  2 | table  |   11 |
|  3 | pencil |    0 |
|  4 |        |   55 |
+----+--------+------+
4 rows in set (0.00 sec)

Missing item field

When we created the record:
$thing = R::dispense("thing");
$thing->qty = 55;
R::store($thing);
print_r($thing->getProperties());
we omitted the important item field. In some ways this is a "misinterpretation" on RedBeanPhp's part. It is treating the missing field value as empty, when it probably should be null. See what happens when you try enforcing the null value:
$thing = R::dispense("thing");
$thing->item = NULL;
$thing->qty = 55;
R::store($thing);
print_r($thing->getProperties());
Now RedBean complains vociferously, throwing an exception which our program has not handled. We will explain how to handle such exceptions later.

Update and delete

Continuing on, append this code following what we already have and then activate db_test.php from the browser.
// ...
 
echo "ROW CHANGES\n";
 
// example of an update
$thing = R::load("thing", 1);  // id = 1
$thing->qty += 5;             // quantity goes up by 5
R::store($thing);             // update it  
 
// example of a delete
$thing = R::load("thing", 2);  // id = 2
R::trash($thing);             // delete it
Activate (refresh) db_test.php from the browser. Verify the new contents of the table from the mysql client in the shell:
mysql> select * from thing;
+----+--------+------+
| id | item   | qty  |
+----+--------+------+
|  1 | book   |   31 |
|  3 | pencil |    0 |
|  4 |        |   55 |
+----+--------+------+
3 rows in set (0.00 sec)
The record with id=1 has had an increase by 5 of the qty field; the record with id=2 has been deleted.

This code introduces three new functions. The first is
R::load
This is actually a SELECT SQL query underneath the surface. The second parameter indicates the id number of the record to be accessed. Access by id is the most efficient and usually the most common type of access of individual preexisting records. In the first case we update the record by modifying various fields and then putting it back with:
R::store
Deletion is achieved by accessing a record and then calling:
R::trash

Commonality of INSERT and UPDATE

The SQL syntax of INSERT and UPDATE is quite different, but RedBean, like most ORMs expresses these two tasks with very similar code

INSERT is
$thing = R::dispense("thing"); // new thing
$thing->field = value;         // set fields
R::store($thing);              // insert the record
UPDATE is:
$thing = R::load("thing",$id); // existing thing
$thing->field = value;         // modify fields
R::store($thing);              // commit changes to record
In particular, the R::store operation is used for both situations. The difference is the $thing argument, whether it is a "new thing" and (almost) all fields need to be set, or it is an "old thing" where we update only a few of the fields.

Dump the contents of the table

Finally, append this code following what we already have:
// ...
 
$all_things = R::find("thing");
foreach($all_things as $thing) {
  echo "$thing->id, $thing->item, $thing->qty\n";
}
 
Activate (refresh) db_test.php from the browser to see the final output:
DUMP
1, book, 31
3, pencil, 0
4, , 55
This introduces the new function
R::find
which, when used in this general way, fetches all records from the table. It can be controlled to supply both WHERE clauses which restrict the rows and ORDER clauses which sort the output.

The full test script

Here is the full contents of the body:
<pre>
<?php
require_once "include/rb.php";
 
$db = "LOGIN";
$user = "LOGIN";
$pass = "LOGIN";
$url = "mysql:host=localhost;dbname=$db";
 
echo "connecting\n";
 
R::setup( $url, $user, $pass );
R::freeze( true );
 
$sql = "drop table if exists thing";
echo "$sql\n";
R::exec($sql);
 
$table_def = "
id int auto_increment primary key not null,
item varchar(30) unique not null,
qty int not null default 0
";
 
$sql = "create table thing ( $table_def )";
echo "$sql\n";
R::exec($sql);
 
$thing = R::dispense("thing");
$thing->item = "book";
$thing->qty = 26;
R::store($thing);
print_r( $thing->getProperties() );
 
$thing = R::dispense("thing");
$thing->item = "table";
$thing->qty = 11;  
R::store($thing);   
print_r($thing->getProperties());
 
$thing = R::dispense("thing");
$thing->item = "pencil";
R::store($thing);
print_r($thing->getProperties());
 
// oddly enough, this "works" too
$thing = R::dispense("thing");
$thing->qty = 55;
R::store($thing);
print_r($thing->getProperties());
 
echo "UPDATE\n";
 
$thing = R::load("thing", 1);
$thing->qty += 5; 
R::store( $thing );
 
echo "DELETE\n";
 
$thing = R::load("thing", 2);
R::trash($thing);
 
echo "DUMP\n";
 
$all_things = R::find("thing");
foreach($all_things as $thing) {
  echo "$thing->id, $thing->item, $thing->qty\n";
}
?>
</pre>

Php/MySQL in web applications

Our test program we created above is totally unrealistic. A database table is never created, accessed, updated and deleted all within a single program; there is no point in having a database if we start from scratch each time! Each part of what we've done occupies its own special place in a web application:
  1. Create/Drop Table: The notion of destroying data is an anathema to any database application. Create and drop table operations must be protected from inadvertent access. For us, table creation will be relegated to Php scripts intended to be executed in the command line shell; they will all be placed in the web-inaccessible setup folder.

    As far as dropping a table, this is primarily done during development when a backup of the table exists or there is an easy way to regenerate the table.
  2. Insert/Modify/Delete records: Insertions and updates of records are common feature in web applications, but they may be relegated to only a select group of users such as administrators. In many situations insertion of records is permitted by the world at large, such as name/information collection, blog insertion, etc.
  3. Retrieval and record display: Retrieval and display of record implicitly or explicitly via SELECT operations is usually open to the world at large. It is the essence of what makes web database applications so useful. In many cases display of certain tables, or certain fields within a table are subject to restrictions.

The MySQLi API

As mentioned above, the MySQLi API is suitable for MySQL only. Nevertheless, it is very popular. You have to use explicit SQL commands. Here are the steps corresponding to what we've done using RedBeanPhp.

Connection

$db   = "LOGIN";
$user = "LOGIN";
$pass = "LOGIN";
 
$cx = mysqli_connect("localhost",$user,$pass,$db);
if (mysqli_connect_error()) {
  die("failed: " . mysqli_connect_error());  
}
After a connection has been created, it gets used in all subsequent operations. The style can be: We'll prefer the object-oriented style. Note that the error message for not obtaining the connection cannot be object-oriented because the object, $cx, may not be defined.

Drop/Create Table

$sql = "drop table if exists thing";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
}
 
$table_def = "
id int auto_increment primary key not null,
item varchar(30) unique not null,
qty int
";  
 
$sql = "create table thing ($table_def)";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
}
Note the object-oriented error information:
$cx->error           //  not $cx->error()
The equivalent functional style is this:
mysqli_error($cx))

Insert elements

$sql = "insert into thing (item,qty) values ('book',26)";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
}
 
$sql = "insert into thing (item,qty) values ('table',11)";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
}  

Dump Table

$sql = "select * from thing";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
} 
while($row = mysqli_fetch_assoc($result)) { 
  $thing = (object) $row;
  echo "$thing->id, $thing->item, $thing->qty";
} 
Unfortunately, there is no suitable foreach construct to loop through the rows; the while construct is necessary. The casting by "(object)" is not necessary. Without it, $row would be used as a Map.

Update/Delete

$sql = "update thing set qty=qty+5 where id=1";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
} 
 
$sql = "delete from thing where id=2";
$result = $cx->query($sql);
if (empty($result)) {
  die("failed: $cx->error\n");
}  

Error handling

MySQLi will always proceed onwards despite errors, and so we have to test every operation for errors and report them as we have done above. This is one disadvantage over RedBeanPhp which throws exceptions on errors, causing the control to not proceed forward.

Argument escaping in MySQLi

A requirement of direct SQL usage not apparent in this simplistic example is that string variables must generally be escaped for insertion into SQL code. For example, this would cause an error:
$item = "John's Book";
$qty = 34;
$sql = "insert into thing (item,qty) values ( '$item', $qty )";
$result = $cx->query($sql);
The problem is the embedded single quote in the value of $item. The preferred way to solve this problem is using the Php addslashes function which will insert a backslash character in front of every single or double quote. The revised correct approach thus adds another level of complication:
$item = "John's Book";
$qty = 34;
$item_esc = addslashes($item);
$sql = "insert into thing (item,qty) values ( '$item_esc', $qty )";
$result = $cx->query($sql);

The PDO API

The Php PDO (Php Data Objects) has these key points:

The point about exception handling is noteworthy because virtually every SQL-based execution has the possibility of generating an error. Without exception generation, each statement must be tested for errors and dealt with immediately after execution.

Database Connection

Connects to a data source where the connection information is provided as a string referred to as a data source name. Assuming that the MySQL DBMS is on localhost and that the database is LOGIN, the data source name would be:
$url = "mysql:host=localhost;dbname=LOGIN"
Additional property=value pairs can be added as necessary. Given the additional $user and $pass variables for a login/password, the connection is created by this call:
$cx = new PDO($dsn, $user, $password);
The connection creation execution is the only one which automatically generates an exception on failure. JDBC-like exception generation for all subsequent SQL executions can be achieved by setting the error mode:
$cx->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
The two other error mode values are these: With either of these two settings, one must test the value of the PDO::errorCode() function and do the necessary operations if an error is discovered.

Simple query execution

Simple execution of non-SELECT statements can be done using the exec member function like this:
$sql = "some SQL operation like CREATE, UPDATE, INSERT";
$numrows = $cx->exec($sql);
The output, $numrows, is the number of affected rows.

For SELECT statements, the simple one-liner uses the query function:
$sql = "SELECT ... FROM ...";
$res = $cx->query( $sql );
The return value, $res, can be used to deliver the row data in a number of ways. Single rows from $res can be retrieved, one at a time, via:
$row = $res->fetch();
The $row object is an array which holds the field data in both indexed and associative form. For example, if the first field in the SELECT statement were "id", then both of these are defined and equal:
$row[0]   and   $row['id']
There is also the option to retrieve all the rows as an array of arrays:
$all = $res->fetchAll();
In many cases we want to process the rows as they are retrieved, and so this is quite common:
foreach ($res as $row) {
  // do something with the indexed/associative array $row
}

Prepared statements

Prepared statements serve to make repeated usage more efficient and to provide "insertion slots" to insert (possibly complicated) values. The execution format is this:
$sql = "...";
$stm = $cx->prepare( $sql );
$sth->execute( ... );
This prepare/execute style permits both SELECT and non-SELECT statements. For non-SELECT statements we would be likely to use it like this:
$num_affected_rows = $stm->execute( ... );
For SELECT statements, after $stm->execute( ... ), the $stm object acts like $res in the section above, namely, we can do:
$row  = $stm->fetch();                   // one row at a time
$rows = $stm->fetchAll();                // all rows
foreach ($stm as $row) { /* .. */ }      // loop through the rows
PDO has a number of different ways to create the insertion slots, including the JDBC-style with the ? character. Associating a ? position to a value can also be done in a manner similar to JDBC's set<type> function; however, the simplest way is to pass them as an array in the execute function like this:
$sql = "INSERT INTO my_table VALUES (?, ?, ?)";
$stm = $cx->prepare( $sql );
$stm->execute( array($first, $second, $third) );
You have to be careful to always pass an array, i.e., not just the values.


© Robert M. Kline