LEARN PHP IN A DAY: The Ultimate Crash Course to Learning the Basics of PHP in No Time (2015)

Chapter 6. Databases

Introduction

In this chapter we will begin our discussion of using databases inside of our PHP applications. For this we will be using the MySQL database engine and the PHPMyAdmin interface that comes with your LAMP package of choice. We assume you have those installed and activated and are ready to continue.

To be able to manipulate databases, we will be using the mysqli API provided with PHP version 5 and later as well as the PDO abstraction layer. Let’s start by looking into what APIs are. Some of the following content is referenced directly from the PHP Manual.

What is an API?

An API (Application Programming Interface) defines the classes, methods, functions and variables that your application will need to call in order to carry out a desired task. In the case of PHP applications that need to communicate with databases the necessary APIs are usually exposed via PHP extensions.

APIs can be procedural or object-oriented. With a procedural API you call functions to carry out tasks, with the object-oriented API you instantiate classes and then call methods on the resulting objects. Of the two the latter is usually the preferred interface, as it is more modern and leads to better organized code.

We have not yet covered OOP (object-oriented programming) in PHP, so if you are not familiar with the concept, you can skip to chapter 10 of this book and then return to this chapter after you have gotten a hang of OOP!

There are several APIs available that will allow you to connect to the MySQL server. We will have a discussion of the options and which to choose depending on your application.

Connectors

MySQL provides documentation that explains all its terms. The term connector refers to a piece of software that allows your application to connect to the MySQL database server. MySQL has a variety of connectors depending on your language, including connectors for PHP.

When your application requires a database, you need to write your code to perform activities such as connecting to the database server, querying the database, updating the database, removing entries and other database-related functions.  Software is required to provide the API that your PHP application will use, and also handle the communication between your application and the database server, possibly using other intermediate libraries where necessary. This software is known generically as a connector, as it allows your application to connect to a database server.

Drivers

A driver is a piece of software designed to communicate with a specific type of database server. The driver may also call a library, such as the MySQL Client Library or the MySQL Native Driver. These libraries implement the low-level protocol used to communicate with the MySQL database server.

By way of an example, the PHP Data Objects (PDO) database abstraction layer may use one of several database-specific drivers. One of the drivers it has available is the PDO MYSQL driver, which allows it to interface with the MySQL server.

Sometimes people use the terms connector and driver interchangeably, this can be confusing. In the MySQL-related documentation the term "driver" is reserved for software that provides the database-specific part of a connector package.

Extensions

In the PHP documentation you will come across another term - extension. The PHP code consists of a core, with optional extensions to the core functionality. PHP's MySQL-related extensions, such as the mysqli extension, and the mysql extension, are implemented using the PHP extension framework.

An extension typically exposes an API to the PHP programmer, to allow its facilities to be used programmatically. However, some extensions which use the PHP extension framework do not expose an API to the PHP programmer.

The PDO MySQL driver extension, for example, does not expose an API to the PHP programmer, but provides an interface to the PDO layer above it.

The terms API and extension should not be taken to mean the same thing, as an extension may not necessarily expose an API to the programmer.

PHP MySQL APIs

PHP offers three main API options to connect to a MySQL server. One of those options, however, is deprecated and we will not include it in our discussion. Here are the three API options:

·      MySQL Extension;

o   This is deprecated in newer version of PHP;

·      MySQLi Extension;

·      PHP Data Objects (PDO);

Each has its own advantages and disadvantages. The following discussion aims to give a brief introduction to the key aspects of each API.

mysqli Extension

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.

The mysqli extension has a number of benefits, the key enhancements over the mysqlextension being:

·      Object-oriented interface

·      Support for Prepared Statements

·      Support for Multiple Statements

·      Support for Transactions

·      Enhanced debugging capabilities

·      Embedded server support

PDO Extension

PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.

Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.

While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn't allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL's support for Multiple Statements.

PHPMyAdmin and getting familiar with MySQL

Let’s start by discussing MySQL and the PHPMyAdmin graphical user interface. We presume that you would prefer to use this more intuitive option to the command line version of MySQL (unless you really want to have more control, in which case, go for it!).

Basics

MySQL is a database server that allows you to create and store multiple databases for use. A database is a place where you store all of the data for a given application. A database consists of multiple tables with entries. Each row of a table represents a database entry and each column of that table represents some particular information. Below is a picture of the PHPMyAdmin interface with a database open showing three tables inside:

Below is a picture of a single table showing multiple rows and columns:

MySQLi

Dual interface

The mysqli extension features a dual interface. It supports the procedural and object-oriented programming paradigm.

Users migrating from the old mysql extension may prefer the procedural interface. The procedural interface is similar to that of the old mysql extension. In many cases, the function names differ only by prefix. Some mysqli functions take a connection handle as their first argument, whereas matching functions in the old mysql interface take it as an optional last argument.

In addition to the classical procedural interface, users can choose to use the object-oriented interface. The documentation is organized using the object-oriented interface. The object-oriented interface shows functions grouped by their purpose, making it easier to get started. The reference section gives examples for both syntax variants.

There are no significant performance differences between the two interfaces. Users can base their choice on personal preference.

Connections

The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

Depending on the connection function used, assorted parameters can be omitted. If a parameter is not provided, then the extension attempts to use the default values that are set in the PHP configuration file.

The resulting parameter values are then passed to the client library that is used by the extension. If the client library detects empty or unset parameters, then it may default to the library built-in values.

Built-in connection library defaults

If the host value is unset or empty, then the client library will default to a Unix socket connection on localhost. If socket is unset or empty, and a Unix socket connection is requested, then a connection to the default socket on /tmp/mysql.sock is attempted.

On Windows systems, the host name . is interpreted by the client library as an attempt to open a Windows named pipe based connection. In this case the socket parameter is interpreted as the pipe name. If not given or empty, then the socket (pipe name) defaults to \\.\pipe\MySQL.

If neither a Unix domain socket based not a Windows named pipe based connection is to be established and the port parameter value is unset, the library will default to port 3306.

The mysqlnd library and the MySQL Client Library (libmysqlclient) implement the same logic for determining defaults.

Connection options

Connection options are available to, for example, set init commands which are executed upon connect, or for requesting use of a certain charset. Connection options must be set before a network connection is established.

For setting a connection option, the connect operation has to be performed in three steps: creating a connection handle with mysqli_init(), setting the requested options using mysqli_options(), and establishing the network connection with mysqli_real_connect().

Executing statements

Statements can be executed with themysqli_query(), mysqli_real_query()andmysqli_multi_query()functions. Themysqli_query()function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Callingmysqli_query()is identical to callingmysqli_real_query()followed bymysqli_store_result().

After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets.mysqli_query() combines statement execution and result set buffering.

PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

The above example will output:

Prepared statements

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.             

The above example will output:

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.             

Conclusion

In this chapter we introduced some concepts related to creating databases and handling database connections using the MySQLi API in PHP. You should now be able to create your own databases using the PHPMyAdmin interface and connect to them with PHP.