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.
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).
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:
To create a database connection, create a new instance of the
Provide the DSN connection string, database username and password in the constructor.
$db = new PDO('pgsql:host=akela.mendelu.cz;dbname=xpopelka', 'xpopelka', 'password');
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
PDOStatement object. The
represents an SQL query and
also its result. One way to obtain the result is calling 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).
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):
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:
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
method of the
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,
query method, don’t do it! Such approach would introduce SQL injection vulnerability.
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:
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
$stmt->debugDumpParams(); function to print the SQL statement and actual values of parameters for
I have named the keys in the
$location variable the same way as the SQL placeholders (
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
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
The first important part is the line
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
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.
catch code I catch exceptions of the
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.
INSERT statement can fail for many reasons, e.g.
Try to simulate some of the possible error conditions to make sure that the error handling is triggered correctly.
nickname of all persons. Order the persons by their
last names and first names (ascending). Make sure to use appropriate error handling.
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.
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.
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.
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 (
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.