PHP A to ZCE: Databases and SQL

By Quentin Zervaas, 18 November 2010 MySQL PHP PostgreSQL Sqlite ZCE Download PDF

This article is part of the series “PHP A to Zend Certified Engineer”. In PHP A to ZCE, I will take you through 26 different yet equally important topics that will help you become a Zend Certified Engineer. Even if you're not interested in sitting the ZCE-PHP exam, these topics will elevate your understanding of PHP to a whole new level and allow you to become the guru in your company. Read more about PHP A to Zend Certified Engineer...

Databases are an extremely useful tool in web development as they allow you to store data about users, customers, e-commerce products and orders, or anything else. In this article I will cover the basics of using databases in PHP, including how to manage data using SQL.

• There are many different database servers that can be used, each offering varying features and licensing options
• PHP has support for many different database types, each of which is documented in the manual (PHP Database Extensions)
• The most commonly used database servers with PHP are MySQL, PostgreSQL and Sqlite. This is primary because they (usually) free and they work on servers that run Apache and PHP
• Each type of database has its own PHP functions. For instance, to perform a query on MySQL, PostgreSQL and Sqlite the mysql_query(), pg_query() and sqlite_query() functions are used respectively.
• Because of this, database and/or SQL abstraction is encouraged. See the section on Database Abstraction below.

Database Design

• A database is made up of a series of tables
• Typically each table is used to store different types of data. For instance, you might have one table to store information about your users and another to store information about your products.
• Each table can have zero or more records. If you have a table to store user information, a single user would correspond to a single record.
• Each table is made up of one or more columns. Each column has its own name and type. For example, one column might a string that holds the user's name, and another might be a date column to hold their date of birth.
• Each table can have a primary key, used to distinguish each row. This allows you to retrieve a specific column (or columns) as required
• Tables can have relationships with other tables. For example, if you have a table that holds e-commerce orders it may have a relationship with the users table so you know which user placed the order.
• When creating a table you can index columns. This allows searching of that column quickly.

The following figure is a basic representation of how a users and orders tables may be designed. In the real-world there would be more columns to hold additional data (not to mention more tables, such as one to hold product data).

Figure 1 A simple example of how database tables relate to each other

Note: The arrows indicate a one-to-many relationship. That is, a single user can have many orders, but a single order only has one user.

Structured Query Language (SQL)

In order to manage the data in a database, Structured Query Language, or SQL, is used.

• There are four basic operations in SQL: Selecting records, inserting records, updating records, deleting records
• This is also known as CRUD (create, read, update, delete)
• For the most part SQL is the same between each database server type, but there are some differences (for instance, the syntax to limit the number of rows returns differs slightly between MySQL and PostgreSQL).

Creating Tables

• Tables are created using a CREATE TABLE statement
• This statement lists all columns and keys that should be created for the table

Listing 1 listing-1.txt

CREATE TABLE users (
    user_id     serial          not null,
    name        varchar(255)    not null,
    country     varchar(2)      not null,

    primary key (user_id)
);

Note: In MySQL and PostgreSQL, the serial column type defines an integer column that auto-increments when a new row is inserted.

Inserting Data

• Data is inserted using an INSERT statement
• You specify a list of columns and values to insert into
• If a column is not defined as NOT NULL you don't need to specify it when inserting.

Listing 2 listing-2.txt

INSERT INTO users (name, country) VALUES ('Quentin', 'AU');

Retrieving Data

• Data is retrieved using a SELECT statement
• There are several key clauses to a select statement:
  • List of columns to retrieve. You can use * to mean every column
  • List of tables those columns belong to
  • Criteria for filtering results, such as returning only users with a country of AU (the WHERE clause)
  • Criteria for sorting the returned data, such as alphabetical by name (the ORDER BY clause
• There are other clauses that can be included, but these are the most important
• Only the list of columns and tables are required
• Clauses must appear in the correct order (columns, tables, where, order, limit).

Listing 3 listing-3.txt

mysql> SELECT name, country FROM users WHERE country = 'AU' ORDER BY 'name';
+---------+---------+
| name    | country |
+---------+---------+
| Quentin | AU      |
+---------+---------+

Updating Data

• Data is updated using an UPDATE statement. This statement operates on a single table.
• This statement contains a list of the columns you want to update and corresponding values to update
• You must also specify a WHERE clause - if you don't, every row will be updated!

The following SQL statement updates the name of all matching users.

Listing 4 listing-4.txt

mysql> UPDATE users SET name = 'Peter' WHERE name = 'Quentin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT name, country FROM users WHERE country = 'AU' ORDER BY 'name';
+-------+---------+
| name  | country |
+-------+---------+
| Peter | AU      |
+-------+---------+
1 row in set (0.00 sec)

Caution: It is strongly recommended that you test your update statements by creating a select statement using the same WHERE clause. This will help you avoid updating incorrect rows.

Deleting Data

• Data is removed using a DELETE statement. This statement operates on a single table.
• You must specify a WHERE clause - if you don't, every row will be deleted!

Caution: It is strongly recommended that you test your delete statements by creating a select statement using the same WHERE clause. This will help you avoid updating incorrect rows.

The following SQL statement deletes all users for the given country code.

Listing 5 listing-5.txt

DELETE FROM users WHERE country = 'AU';

Using MySQL With PHP

The general flow of using MySQL is as follows

1. Connect to database server with mysql_connect()
2. Select a database to operate on using mysql_select_db()
3. Perform select, insert, update or delete query using mysql_query()
4. If performing a select statement you can used the returned result to determine the number of rows using mysql_num_rows(), and you can loop over rows using mysql_fetch_array()
5. Close the connection using mysql_close().

Listing 6 listing-6.php

<?php
    // connect to the database server
    $db = mysql_connect('localhost', 'myUsername', 'myPassword');
 
    // select the database
    mysql_select_db('myDatabase', $db);
 
    // perform a query
    $query = 'select * from users';
    $result = mysql_query($query, $db);
 
    // output the number of rows
    echo sprintf('%d rows found', mysql_num_rows($db));
 
    // loop over the rows and output data
    while ($row = mysql_fetch_array($result)) {
        echo sprintf('%s is from %s', $row['name'], $row['country']);
    }
 
    // close the connection
    mysql_close($db);
?>

Note: Typically you'd make the connection in a bootstrap file so the connection is available from all PHP scripts in your site.

• Whenever you use a PHP variable in a where clause you should call mysql_real_escape_string() to prevent SQL injection
• This is especially true for user-submitted data.

Listing 7 listing-7.php

<?php
    $name = $_POST['name'];
 
    $query = sprintf(
        "select * from users where name = '%s'",
        mysql_real_escape_string($name)
    );
?>

Database Abstraction

Because each database has its own set of functions, database abstraction is commonly used. This is a layer between your PHP script and the database-specific PHP functions.
The following figure demonstrates how database abstraction works. Once you use database abstraction you only need to know the functions of the abstraction layer - you don't need to care about the specific functions (although you should still learn them!)

Figure 2 Database abstraction gives a single code-entry point regardless of the database server type

Note that this diagram doesn't mention a specific abstraction layer (there are many to choose from). One good example is Zend_Db_Adapter. You can also use Zend_Db_Select for SQL abstraction (that is, build SQL statements that are compatible with multiple database servers).

Summary

This article touches on a number of different subjects, but it's really just a primer on SQL and using MySQL in PHP. There's a lot of things to learn when it comes to databases.

Other Options

• Download a PDF version of this article
• Put your PHP knowledge to the test with our online and iPad/iPhone quizzes
• View or post comments for this article
• Browse similar articles by tag: MySQL, PHP, PostgreSQL, Sqlite, ZCE
• Read related articles:
  • PHP A to ZCE: Arrays
  • PHP A to ZCE: Bitwise Operations
  • PHP A to ZCE: Cookies
  • PHP A to ZCE: Error Handling
  • PHP A to ZCE: Functions