PHP and databases

Dr Andres Baravalle

PHP and databases

  • DBMS
  • Using MongoDB

Before we start

You should now be familiar with Core PHP, including:

  • The syntax of the language: the structure of PHP programs. (e.g. now you know about statements, control structures, iteration, objects, functions etc.)
  • The semantics of the language: how do you construct valid PHP programs (e.g. you do not pass an object as a parameter for strpos())

DBMS

Databases and DBMSes

A database is a organised collection of data. Database Management Systems are the software that manage and control the access to the database (Connolly and Begg, 2010).

A DBMS "works with the operating system to store and modify data and to make data accessible in a variety of meaningful and authorised ways" (Brown et al., 2011).

The aim of DBMSes is to allow users and programmers to select data from storage by referring to the content of the data, not by their physical location.

Databases are for long-term storage

A database typically is used to provide long-term storage for your information. If your web application or web server failes, a database will not loose your information.

Your information will be lost in case of disk failure - that's why redundancy is important.

Databases and DBMSes: making life easier

Before DBMSes, programmers had to develop their own functions to create, read, update and delete (CRUD) data.

That had implications in issues as maintainability, speed of development and performance.

Types of DBMSes

There are (many) different types of databases but in L/WAMP applications the most common architectures are using relational databases, flat-file databases or NoSQL databases.

All databases implement a set of CRUD functions: to create, read, update and delete records.

Relational databases

The relational database is the most common and widespread architecture for organising structured data. Content is stored in tables and accessed through a specialist language, SQL. In many cases, a graphical user interface is available to simplify the interaction with the databases.

Relational DBMSes commonly used in web applications include Oracle (proprietary), SQL Server (proprietary), MySQL/MariaDB (Open Source), PostgreSQL (Open Source) and SQLite (public domain). They are all supported by PHP.

Relational databases: ACID!

Apart from implementing CRUD functions (using SQL), transactions on relational databases are ACID:

  • Atomic: all or nothing. If one part of the transaction fails, it all fails
  • Consistent: each transaction can move a database only between valid states
  • Isolated: each transaction in process but not finished will be isolated from other transactions
  • Durable: once a transaction has been committed, its changes will be reflected in the database (e.g. survives a crash of the system)

Flat file databases

Flat file databases store the data into flat text files (e.g. txt files). You will normally need to develop the CRUD functions to interact with your data storage and while ensuring the security of the data stored.

Flat file databases are typically used only when other solutions are not available or in circumstances where the complexity of the application to develop is minimal.

Flat file databases require the development of your own CRUD functions.

NoSQL databases

NoSQL databases store the data in a loosely structured format and don't (typically) provide the same level of control (at database level) on the results returned (e.g. ordering). They are often highly optimized for simple retrieval and appending operations, with the goal of higher performance. 

NoSQL DBMSes commonly used in web applications include:

Apache CouchDB Apache Cassandra MongoDB
CouchDB Cassandra MongoDB

They are all supported by PHP.

Flat file databases in PHP: example

<?php 
// simple file with user ids, surnames and names 
// e.g.: 
// u111111#John#Brown 
// u111112#John#Johnson 
// etc. 

$file = file("students.txt"); 

// lets find all the users who have John as name 
$needle = "JOHN"; 
$results = array(); 
while (list($key, $val) = each($file)) {    
// $val is a string - we need to split it to get the name    
$student = explode("#", $val);            
	if(strpos($student[1], $needle)!==FALSE) {          		
		$results[] = $student;        
	}   
} 
print_r($results); 
?>

Activities

This week's activities are based on a common scenario where we are using (some) CRUD functions to interact with a database of students and their marks in one or more exams.

Activity #1: flat text database

For Activity #1, develop a flat text database including student number, module name and mark.

After writing the database, create the functions to read the records and output as follows:

Student number Module name Mark
u1111111 IM2081 41
u1111112 IM2081 42
u1111113 IM2081 43

XML databases

XML is "a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable" (Wikipedia, 2013). XML can be used to store structured data in text files and the PHP DOM functions allow to easily interact with the document tree.

Abstraction layers

Web applications typically support more than one database, typically through the use of abstraction modules or abstraction classes.

This allows to use the same code base with different databases - with no need for re-development.

For example, the Drupal project natively supports MySQL/MariaDB, PostgreSQL or SQLite, and supports SQL Server, Oracle and MongoDB with additional modules.

Abstraction layers in PHP

Widely used abstraction layers in PHP include:

  • PDO: supported through a PHP extension
  • ODBC: supported through a PHP extension (largely used to interact with database on Microsoft-based environments)
  • PEAR MDB: PHP module
  • Doctrine: supported through PHP extensions plus and PHP modules
  • Zend DB: supported through PHP extensions plus and PHP modules

Abstraction layer vs native functions

Abstraction layers support code portability. When developing (correctly) using an abstraction layer, you can replace your DBMS.

E.g. you may code an application using MySQL as a back-end and then deploy it on a Oracle back-end.

Whichever abstraction layer you decide to use, this comes with a cost in terms of performance.

Abstraction layers example: using PDO


<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
foreach($dbh->query('SELECT * from FOO') as $row) {         
	print_r($row);
}
?>

What's wrong with DBMSes?

Nothing, for most web applications.

But...

What about very very very large web applications? For example web sites as:

Facebook Twitter Digg

More on NoSQL databases?

More on databases?

We will not cover databases (much) more in depth - you should already be familiar with the concepts.

For database support in PHP, please refer to the PHP on-line documentation.

MongoDB

Introduction to MongoDB

MongoDB (from "huMONGOus" - perhaps alteration of huge + monstrous) is a NoSQL, Open Source database.

MongoDB was originally developed by 10gen, a company set up by Eliot Horowitz and Dwight Merriman (the founder of DoubleClick).

The aims of MongoDB where to:

  • Retain as much of the functionality of traditional relational DBMSes
  • Permit horizontal scaling
  • Ensure an "elegant" interface for developers

The basics

  • A document is the basic unit of data for MongoDB (it's similar to a row in a relational database)
  • A collection is a group of documents (it's the schema-free equivalent of a table in a relational database)
  • A single instance of MongoDB can host multiple independent databases, each of which can have its own collections and permissions

How and when we use MongoDB?

MongoDB is mostly used for large scale web applications. We are going to use it largely to store objects - but it is much more powerful.

Installing MongoDB

MongoDB is already installed on mastodon. To install MongoDB on your home computer, follow the instructions here and here.

Two steps are required: the first is installing the database server, the second to install the PHP module.

While almost no configuration is needed to install MongoDB, installing the PHP module requires being familiar with your php.ini file settings. Do not start doing that lightly.

Be aware that MongoDB can easily grow into using several GBs - you may have to customise your configuration file if you want to trade performance for disk space (typically on development machines).

Installing MongoDB (2)

Installing MongoDB can be quite complex. You are advised to use mastodon instead for your development.

If you are finding it difficult to install MongoDB on your system, you can try winginix. To install winginix, you need to remove Apache and/or XAMPP.

Use this only as a fallback approach and remember to test your application on Mastodon.

Using MongoDB

MongoDB can be used:

  • From the command line
  • From PHP
  • Using some DB management system

You are not going to be able to use MongoDB from the command line, but you will be exploring the other two options.

Connecting to a MongoDB Database Server

To connect to MongoDB from PHP, you need to create a Mongo object, passing username, password, server and port and database name.

The default host is localhost, and the default port is 27017 (you do not normally need to change these parameters)

Connecting to MongoDB database server (authenticating against a database named dbname) at localhost port 27017:


<?php
$connection = new MongoClient("mongodb://username:password@localhost/database_name"); 

print_r($connection);

/*
Mongo Object
(
    [connected] => 1
    [status] =>
    [server:protected] =>
    [persistent:protected] => 
)
*/

?>

Selecting a database

Once you have established the connection with the database server, you use it to access a database:

<?php
$db = $connection->dbname; // opens the database dbname
?>

If you have administrative rights for MongoDB and the database doesn't exist, MongoDB will automatically create the new database for you.

Creating/Selecting a Collection

Once you have accessed a database, you can select a collection:

<?php
$collection = $db->addresses;
?>

Activity #2: Logging into MongoDB

Activities 2-4 will consist in creatinga collection with student numbers, module names and student marks (as in Activity #1).

As a first step, create a new collection called marks. To do it, you will need to:

  1. Set up the connection to MongoDB
  2. Select the collection called marks (if not existing, it will be created automagically)

Storing an object

MongoDB is used to store objects and documents:

<?php
// creating an array 
$student1 = array("student_id" => "u1111111", "module_name" => "cn5109", "mark" => 41);
$collection->insert($student1);
?>

MongoDB: inserting data

<?php 
$connection = new Mongo("mongodb://andres:password@localhost"); 
$collection = $connection->andres->students; 

// creating an array 
$student1 = array("student_id" => "u1111111", "module_name" => "cn5109", "mark" => 41); 
$collection->insert($student1); 

$student2 = array("student_id" => "u1111112", "module_name" => "cn5109", "mark" => 42); 
$collection->insert($student2); 

?>

Activity #3: Forms and MongoDB

Develop a form - like this:

Student data

Save the data into your MongoDB collection.

Primary Keys and ObjectIds

MongoDB uses unique identifiers for each document (called primary keys or objectids). Unless otherwise configured, MongoDB will automatically create an ObjectIds.

The ObjectId is composed of a timestamp, as well as information about the machine it was created on. As an object, it has methods that you can run.

MongoDB: finding one document

The method findOne() is used to find the first matching document in a collection. 

<?php
// It can be called without parameters:
$document = $collection->findOne();
// or using an array with the paramters:
$document = $collection->findOne(array("module_name" => "cn5109"));

print_r($document);

/*
Array (
	[_id] => MongoId Object 
		(
			[$id] => 528c2686eae33e20048b4567
		)     
	[student_id] => u1111111     
	[module_name] => cn5109     
	[mark] => 41 
)

*/
?>

MongoDB: finding all documents

<?php
$cursor = $collection->find();
foreach ($cursor as $key => $val)
{
    echo "$key: ";
    print_r($val);
}
?>

MongoDB: querying 

<?php
$query = array("module_name" => "cn5109"); 
$cursor = $collection->find($query); // will find all the marks for CN5109
foreach ($cursor as $key => $val)
{
    echo "$key: ";
    print_r($val);
}
?>

Activity #4: find all the documents

Create a simple page that lists - in a table - all the data inserted in your collection.

Use the mockup below as guidance:

Marks

MongoDB: updating a collection

You can use the update method to update a collection. Update will accept three paramters:

  • An array, specifying the query
  • A second array, specifying the replacement
  • A third array, with any options
MongoCollection::update(array $criteria, array $new_object [, array $options = array() ])

By default, MongoDB will update only the first matching document. For advanced options, refer to the documentation.

MongoDB: updating a collection - cont'd

<?php
// this is replacing the entire document stored with the content of the array
$collection->update(array("student_id" => "u1111111", "module_name" => "cn5109"), array("mark" => 45));

// this is actually just changing the value
$collection->update(array("student_id" => "u1111111", "module_name" => "cn5109"), array("$set" => array("mark" => 45)));

?>

MongoDB: removing data from a collection

Removing data is as simple as adding data:

<?php
$criteria = array('_id'=> new MongoId('4ba667b0a90578631c9caea1'));
$collection->remove($criteria, array("justOne" => true));
?>

References

Connolly, T. M. and Begg, C. E. (2010) Database Systems: A Practical Approach to Design, Implementation and Management, 5/E, Pearson/Addison Wesley.

Brown, C., DeHayes, D., Hoffer, J., Martin, E. and Perkins, W. (2011) Managing information technology. 7th edn. New Jersey: Pearson Education. ISBN-10:  0132146320 or ISBN-13:  978-0132146326.

Readings

The topics covered in this lecture are not in your textbook. Follow the links included in the slides and below to improve your understanding of PHP and NoSQL and to be able to implement it correctly in your PHP applications.

php.net (2013). Mongo tutorial. Available from: http://us.php.net/manual/en/mongo.tutorial.php

Francia, S. (2012). MongoDB and PHP

Chodorow (2013). MongoDB: The Definitive Guide

This work

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License

Creative Commons License