For the absolute beginner
One of the main reasons you might be looking to use PHP is to store and retrieve data from a database. There are numerous database servers out there and many ways for PHP to access them. This tutorial will cover database access using the PDO classes. For easier setup in a learning environment this tutorial will use the SQLite database engine as it is commonly already included in PHP installs and supports an in-memory database so that there is no server to setup for these examples. The SQL used in this tutorial will strive to be simple and portable over many databases as teaching the finer points of SQL, especially as geared towards your specific server, is not within the scope of this document. But keep in mind that PDO does not abstract your SQL queries; it just provides a unified API for interacting with many database engines. If you change engines you will likely need to modify the SQL you used.
To use PDO and SQLite, or any other database, then support for the proper drivers must be
enabled on the installation of PHP you are using. It is fairly common, but not assured,
that PDO and SQLite are already built in. To check this look at the output from phpinfo();
on the server you wish to run your code on. If you are running PHP from command line then
php -m will work.
You should see 'PDO' and 'pdo_sqlite' as modules listed for this tutorial to work
TODO: Instructions on installing
At the most basic a database is composed of one or more tables. Tables organize data two dimensionally into rows and columns. A typical table might look something like:
Table: nationalAnimal
+----+-----------+----------+
| id | country | animal | <-- column headings (fields)
+----+-----------+----------+
| 1 | America | eagle | <-- rows
| 2 | China | dragon |
| 3 | England | lion |
| 4 | India | tiger |
| 5 | Australia | kangaroo |
| 6 | Norway | elk |
+----+-----------+----------+
As you can see, each table has a heading that has the column (field) names and then rows of data. Each row contains a value for each field. Each field can have a specified data type. Each database engine has their own set of data types and you should review the manual for your database of choice for further details. At it's most basic, and all that SQLite supports are: integers, floating point numbers, strings, and binary blobs. Other database engines have support for finer grained versions of the previously mentioned types and also typically have types for storing dates, times, and boolean values.
Database tables should have a primary key. Typically this is a column like the 'id' column displayed above, but the primary key can also be a composite value generated from multiple columns. The idea is to have a unique identifier for a row. In addition many database engines support automatically incrementing an integer value for a column. This is commonly paired with the primary key.
As a brief overview of SQL commands you will be seeing in this tutorial examine the following SQL statements keeping in mind that this tutorial is using the SQLite database engine. The following SQL would create, and display something similar to the above table:
CREATE TABLE `nationalAnimal` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`country` VARCHAR(255),
`animal` VARCHAR(255)
);
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('America', 'eagle');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('China', 'dragon');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('England', 'lion');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('India', 'tiger');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('Australia', 'kangaroo');
INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES ('Norway', 'elk');
SELECT `id`, `country`, `animal` FROM `nationalAnimal`;
These commands can be entered using the sqlite3 command line client if you so desire, but install and setup of the client is beyond the scope of this tutorial. For now just look over the SQL to familiarize yourself with the commands as they are explained.
The first SQL command used above is 'CREATE TABLE'. This command creates the database table to store the data. Because table creation sets up the data types for your fields it is commonly the most engine specific SQL code a developer new to databases will encounter. In this specific example we are creating a table with 'nationalAnimal' as the name with 3 fields for data. First of which is 'id' which is an integer field that is setup to be the primary key and have the value increment automatically. Second and third we have 'country' and 'animal'. Those two fields both hold a string up to 255 characters in length.
The second set of SQL commands we have are 'INSERT'. This command deals with inserting the data into the table. You tell it what table to insert the data into, list out the field names you wish to insert data into, then associate the values to insert into the table. As 'id' is a special column the database engine takes care of automatically inserting the integer. Each insert command inserts a new row into the database.
The third SQL command contained above is 'SELECT'. This command is one of the more commonly used when accessing databases. In a select statement you first select the fields that you want and then indicate which table to select from. The select statement could also be written as SELECT * FROM `nationalAnimal`; but it is generally recommended that you specify which fields you want so that you can save memory by ignoring fields that are not needed.
The first step in using PDO is establishing a connection to the database. This is done with the PDO class constructor. For the purposes of this tutorial we will be using SQLite's in memory database so the connection string will be very simple. As you edit this file and save it, go ahead and run it either via your server, php-compatible pastbin or php-cli. Create a new file, rmdbs-tutorial.php if you require a name, and enter in the following code:
<?php
$db = new PDO('sqlite::memory:');
In this code we create a new instance of the PDO class, stored to the variable $db, in this we pass to the constructor the data source name (dsn) string 'sqlite::memory:' this tells PDO to use the sqlite driver, and the driver specific command of :memory: to create a new database in memory. PDO::__construct has several more options which are outside the scope of this introductory tutorial but can be viewed in the php manual at http://php.net/manual/en/pdo.construct.php.
The exec method of PDO directly executes the SQL and returns the number of rows affected. Mainly this command should be used for executing statements that are commands to your database engine or things like creating tables. Add the following code to your rmdbs-tutorial.php file you created earlier:
$sql_createtable = "CREATE TABLE `nationalAnimal` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `country` VARCHAR(255), `animal` VARCHAR(255))";
$db->exec($sql_createtable);
You may recognize the CREATE TABLE syntax from before. The exec method accepts a string and runs it on the database that you connected to when creating the $db variable. Here we assigned the CREATE TABLE string to $sql_createtable and passed it to the exec method there by running the CREATE TABLE against the database.
If you have user generated input to your database you should ALWAYS use prepared statements. Prepared statements help prevent SQL injection attacks by separating the SQL commands and the data when communicating with the database engine. They can also optimize your application by allowing the client and/or server to cache aspects of the query if you are calling PDOStatement::execute multiple times. One thing prepare cannot do is parameterize table or field names, if you need to allow a user input to select one of those then you should white list options that the user could chose and do not directly associate the input with the statement. PDO::prepare if called successfully will return a PDOStatement object for you to further call methods on to finish the process. In using prepared statements the SQL statement can contain zero or more named or question mark parameter markers. You cannot mix named and ? prameters. This tutorial will only use named parameters as question mark parameters offer no advantage. One caveat to named parameters is you cannot use the same name multiple times. To use question mark parameters you must fill them in the same order they were setup and provide no context for what is expected. This section of the tutorial will examine, in addition to PDO::prepare, the bindParam, bindValue, and execute methods of PDOStatement. Please examine and add the code below to your rmdbs-tutorial.php that was created, then modified, earlier:
$sql_prepare = "INSERT INTO `nationalAnimal` (`country`, `animal`) VALUES (:country, :animal)";
/** mutiple inserts with bindParam **/
$stmt = $db->prepare($sql_prepare);
$stmt->bindParam(':country', $country, PDO::PARAM_STR);
$stmt->bindParam(':animal', $animal, PDO::PARAM_STR);
$foo[] = array('America', 'eagle');
$foo[] = array('China', 'dragon');
foreach($foo as $data) {
$country = $data[0];
$animal = $data[1];
$stmt->execute();
}
/** multiple inserts with execute **/
$stmt = $db->prepare($sql_prepare);
$bar[] = array(':country' => 'England', ':animal' => 'lion');
$bar[] = array(':country' => 'India', ':animal' => 'tiger');
foreach($bar as $data) {
$stmt->execute($data);
// an alternative method below that (could) result in less typing depending on how the array is constructed
// $stmt->execute(array(':country' => $data[':country'], ':animal' => $data[':animal']));
}
/** single insert with bindValue, then execute **/
$stmt = $db->prepare($sql_prepare);
$stmt->execute(array(':country' => 'Australia', ':animal' => 'kangaroo');
$stmt->bindValue(':country', 'Norway', PDO::PARAM_STR);
$stmt->bindValue(':animal', 'elk', PDO::PARAM_STR);
$stmt->execute();
As demonstrated there are multiple ways to pass data into a prepared statement. The simplest, and one used in the rest of this tutorial, is passing parameters directly to the execute method. This tutorial will use passing directly to the execute method from here on out. This is due to that all that is required is passing an array of values with the key matching the parameter.
The first thing to see is our insert statement, from the brief intro to SQL we saw 6 statements with country names and animals. In this one, assigned to $sql_prepare, we have replaced those with the named parameter placeholders :country and :animal. For named parameters they must be a colon (:) followed by the name you wish them to be known by.
The first block of executed statements we insert two rows using bindParam. This binds the variable to the parameter by reference and as such is only evaluated when execute is called. We used this feature to loop through an array holding two country/animal pairs.
The second block shows inserting two more rows by passing the values directly to the execute method. This block of code shows two ways of doing that. One way involves pre-constructing the array to match the format required, the other just passes the values into a new array created as part of the attribute passed to the execute method.
The third block of code shows single row insertions either by passing directly to the execute method, or by using bindValue. In passing directly to the execute method it's largely the same as the previous section, just without the loop to insert multiple rows. The bindValue method is similar to the bindParam but the binding is not passed by reference and subsequently evaluated at execute it is instead bound immediately.
Similar to exec, the query method executes a straight SQL statement but instead of returning the number of rows affected it returns a PDOStatement so that data can be retrieved. Just like exec you should not pass any user inputted data into this method as no attempt is made by it to prevent SQL injection. Please examine and add the following code to your rmdbs-tutorial.php file that you've been editing so far:
$stmt = $db->query("SELECT `id`, `country`, `animal` FROM `nationalAnimal`");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$results[] = 'id:' . $row['id'] . ', country: ' . $row['country'] . ', animal: ' . $row['animal'];
}
var_dump($results);
The query is a direct copy of the one in the SQL introduction. The new feature introduced here is the PDOStatement method of fetch. This, on a row being found, returns the row in the format specified. In the case of the code here, this fetch method is asked to return an associative array, or an array with a named key that matches the field names. If no new row is to be found it returns a boolean false, allowing the while loop to end. This code composes each row a string and adds each row to an array for easy output with var_dump.
TODO: Describe other fetch styles.
The power of using a database is that you can select data meeting specific criteria and sort it before you bring it into PHP. This is advantageous because your database is much better suited to that than PHP. The best policy is to, in so far as it's possible, bring in only the data you need and have it already sorted. This tutorial will cover a few basic methods of limiting and sorting data but for more details you really should read more about SQL in general and your database engine in particular. Please examine the code below and add it to your rmdbs-tutorial.php file that you've been editing so far:
$stmt = $db->prepare("SELECT `id`, `country`, `animal` FROM `nationalAnimal` WHERE `id` != :id ORDER BY `country`");
$stmt->execute(array(":id" => 1));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
$stmt = $db->prepare("SELECT `country`, `animal` FROM `nationalAnimal` WHERE `id` = :id");
$stmt->execute(array(":id" => 3));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($rows);
This short example shows two select statements that are in prepared statements to allow for user input. While in this case the variables are directly inputted they could be replaced with a variable taken from data inputted by the user and would protect against sql injection. The first one selects all the rows except where the id column is a specific number, in this case 1, and then sorts the results by country. The second query selects the rows where id is a specific number. As ID is the primary key there will only be one row selected in this case. Both of these statements are then fetched by a new fetchAll statement. Like fetch, it takes a specific fetch style as a parameter. Unlike fetch, it returns all the rows.
One method that is useful, though slightly vendor specific is lastInsertId(). The lastInsertId method can be called right after an insert and, depending on database drivers, return the id of the last inserted row. Too see an example of this insert the following code after one of the single row execute statements earlier:
$db->lastInsertId();
Error handling with PDO depends on the attributes set for the connection. This can be set as exampled below:
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
By default PDO::ATTR_ERRMODE is set to PDO::ERRMODE_SILENT. In PDO::ERRMODE_SILENT most methods in the PDO and PDOStatement classes will return FALSE on error, and then to obtain information regarding the error the method errorInfo will need to be called on the PDO or PDOSTatement object as appropriate.
The other useful error mode is PDO::ERRMODE_EXCEPTION. This is useful if you are using exceptions for error handling else where in your application. Furthermore, uncaught exceptions tend to be very vocal about a problem and instead of checking for false returns and calling errorInfo every statement you can just wait for the exception to show for that time a quote was slightly off.