Working with Database

 

In previous chapters, you have learned how to create PHP scripts, and in the latest chapter also how to work with a database using the SQL language. In this chapter, you’ll learn how to work with the database from within a PHP script. This is a very important step in connecting all the technologies in the stack together.

Getting Started

Before you start, you need to have working credentials to a database, and you should have the sample database imported. Also you should be familiar with creating and running a PHP script.

To create an application which communicates with a database system, you always need some kind of a library. Database libraries are specific to the application language (PHP, Java, C++) and database (PostgreSQL, MySQL, …), so there are hundreds of them.

For PHP, there is a very good built-in library – PDO (PHP Database Objects), which is capable of communicating with multiple databases (including PostgreSQL and MySQL).

Connecting to database

To connect to the database, you need to write a DSN connection string. This is done usually once, so it is not at all important to remember this. For PostgreSQL you should use:

pgsql:host=SERVER_NAME;dbname=DATABASE_NAME

The pgsql is a driver name. For the prepared PostgreSQL server, the connection string would be e.g.:

pgsql:host=akela.mendelu.cz;dbname=xpopelka

To create a database connection, create a new instance of the PDO class. Provide the DSN connection string, database username and password in the constructor.

$db = new PDO('pgsql:host=akela.mendelu.cz;dbname=xpopelka', 'xpopelka', 'password');

Selecting Data

To select data from the database, use the query method of the PDO connection object. Supply a SQL SELECT query as a string to the function. The function will return a PDOStatement object. The PDOStatement represents an SQL query and also its result. One way to obtain the result is calling the fetchAll function.

<?php

$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
$stmt = $db->query("SELECT * FROM person ORDER BY first_name");
print_r($stmt->fetchAll());

The fetchAll function returns a two-dimensional array. It returns an array of result table (person) rows. Each row is an array indexed by column keys, values are table cells. Therefore the following code will print first_name of the second person (as ordered by first_name). I used the print_r function to print the complete array (it’s not beautiful, but it shall be good enough at the moment).

<?php

$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
$stmt = $db->query("SELECT * FROM person ORDER BY first_name");
$persons = $stmt->fetchAll();

echo $persons[2]['first_name'];

Selecting Data with Parameters

You often need to provide dynamic values (obtained from PHP variables and/or HTML forms) to the SQL queries. E.g. assume you need to run a query like this (where Bill is provided by the end-user and stored in a PHP variable):

SELECT * FROM person WHERE first_name = 'Bill';

The solution is to use prepared statements. This means that you prepare a SQL statement with placeholders, then bind values to the placeholders and then execute the statement:

<?php

$personName = 'Bill';

$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
$stmt = $db->prepare("SELECT * FROM person WHERE first_name = :name");
$stmt->bindValue(':name', $personName);
$stmt->execute();

print_r($stmt->fetchAll());

In the above query, I have used a placeholder name :name (placeholder must start with colon :). Then I bind a value to it using the bindValue method of the $stmt PDOStatement object. Last, I execute the statement. Then the result can be printed as in the previous example.

Parameters in SQL queries are not placed inside quotes. They will be added automatically when the query gets executed. In PHP, the value of the parameter needs to be quoted as any other string.

If you are tempted to use the $personName variable directly within the SQL query string, in the query method, don’t do it! Such approach would introduce SQL injection vulnerability.

Inserting Data

Let’s insert a new row in the location table. The principle remains the same as in the above example with the prepared statement. You just need to use the INSERT statement and provide the right parameters to it:

<?php

$location = [
    'name' => 'Big Ben',
    'city' => 'London',
    'country' => 'United Kingdom'
];

$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
$stmt = $db->prepare("INSERT INTO location (name, city, country) VALUES (:name, :city, :country)");
$stmt->bindValue(':name', $location['name']);
$stmt->bindValue(':city', $location['city']);
$stmt->bindValue(':country', $location['country']);
$stmt->execute();

$stmt->debugDumpParams();

Note that there is no fetchAll call, because the INSERT statement does not return a table (or anything useful). Because working with prepared parameters can be a little bit tricky, you can use the $stmt->debugDumpParams(); function to print the SQL statement and actual values of parameters for debugging purposes.

I have named the keys in the $location variable the same way as the SQL placeholders (:name, :city, :country) and also the same way as columns in the location table. This is not at all necessary, because these names are totally unrelated. However, it reduces a lot of confusion to use consistent naming (also saves you a lot of time inventing new names).

Error Control

An important part of communicating with the database is handling errors. There are multiple options, but the easiest way is to use exceptions. The following example extends the previous INSERT example with error handling.

<?php

try {
    $location = [
        'name' => 'Big Ben',
        'city' => 'London',
        'country' => 'United Kingdom'
    ];

    $db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $db->prepare(
        "INSERT INTO location (name, city, country) VALUES (:name, :city, :country)"
    );
    $stmt->bindValue(':name', $location['name']);
    $stmt->bindValue(':city', $location['city']);
    $stmt->bindValue(':country', $location['country']);
    $stmt->execute();
} catch (PDOException $e) {
    echo "Failed to insert location. Error: " . $e->getMessage();
}

The first important part is the line $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); which makes the database driver switch into the mode in which it throws an exception whenever an error occurs in operations.

Second, I wrapped the whole code in a try - catch statement. As the name suggests, the code inside try - catch is executed normally unless an exception occurs. Whenever an exception occurs, the rest of the try code is skipped and the catch code is executed. In the catch code I catch exceptions of the PDOException class – those are exceptions thrown by the PDO database driver. The method getMessage of the exception object returns the actual error message returned by the database.

The above INSERT statement can fail for many reasons, e.g.

  • the database server is not available
  • the database credentials are wrong
  • the inserted values are not allowed in the table (e.g. are too long)
  • there is something wrong with the database structure (e.g. a table does not exist)
  • many others…

Try to simulate some of the possible error conditions to make sure that the error handling is triggered correctly.

Task – Select Data

Select first_name, last_name, nickname of all persons. Order the persons by their last names and first names (ascending). Make sure to use appropriate error handling.

<?php

try {
	$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
	exit("I cannot connect to database: " . $e->getMessage());
}

try {
	$stmt = $db->query("SELECT first_name, last_name, nickname FROM person ORDER BY first_name ASC, last_name ASC");
	print_r($stmt->fetchAll());
} catch (PDOException $e) {
	echo "I cannot execute the query: " . $e->getMessage();
}

Notice that I used two try-catch blocks, one for connecting to the database and one for the actual query. This will become more useful in future, when we need to distinguish between errors in different parts of the code. In the first catch I have used the exit function to terminate immediately the execution of the script.

Task – Select Pattern

Select first_name, last_name, age, height of all persons, whose first name or last name begins with L. Order the persons by their height and age (descending). Make sure to use appropriate error handling. I suggest you to approach the task in parts, first make a working SQL query, then add it to a PHP script.

This was a little test whether you can search for new stuff – Use the AGE function in SQL. The first person should be Leonora Nisbet.

SELECT first_name, last_name, nickname, AGE(birth_day) AS age, height
		FROM person
		WHERE first_name LIKE 'L%' OR last_name LIKE 'L%'
		ORDER BY height DESC, age DESC
<?php

try {
	$db = new PDO('pgsql:host=localhost;dbname=apv', 'apv', 'apv');
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
	exit("I cannot connect to database: " . $e->getMessage());
}

$letter = 'L';

try {
	$stmt = $db->prepare(
		"SELECT first_name, last_name, nickname, AGE(birth_day) AS age, height 
		FROM person 
		WHERE first_name LIKE :pattern OR last_name LIKE :pattern
		ORDER BY height DESC, age DESC"
	);
	$stmt->bindValue(':pattern', $letter . '%');
	$stmt->execute();
	print_r($stmt->fetchAll());
} catch (PDOException $e) {
	echo "I cannot execute the query: " . $e->getMessage();
}

I used an alias in the SQL query to define a new name of the computed column. It is important to know the column name, because we need to reference it in the PHP script.

Summary

In this chapter, you have learned how to use SQL queries from within a PHP script. Non-parametric queries are quite simple (just call the query function). Parametric queries are more complicated (prepare, bindValue, execute function calls). Using proper error control adds further complexity to the script. However the error control is very important, otherwise the application will misbehave in case an error condition occurs.

New Concepts and Terms

  • Database Driver
  • PDO/PDOStatement
  • Prepared Statement
  • Query Parameters
  • Error Control