MySQL is the most commonly used databases because of its ease of use and smooth data handling. In PHP, there are many ways through which one can connect the database to the PHP project.
At the time of writing, PHP provides you with two main ways to connect to MySQL databases:
- mysqli (MySQL improved) — This extension is specifically tied to MySQL, and provides the
most complete access to MySQL from PHP. It features both procedural (function – oriented) and object – oriented interfaces. Because it has quite a large set of functions and classes, it can seem overwhelming if you ’ re not used to working with databases. However, if you know you ’ re only ever going to work with MySQL, and you want to squeeze the most out of MySQL ’ s power from your PHP scripts, then mysqli is a good choice - PDO (PHP Data Objects) — This is an object – oriented extension that sits between the MySQL server and the PHP engine. It gives you a nice, simple, clean set of classes and methods that you can use to work with MySQL databases. Furthermore, you can use the same extension to talk to lots of other database systems, meaning you only have to learn one set of classes and methods in order to create applications that can work across MySQL, PostgreSQL, Oracle, and so on.
Choosing between these two extensions can be a topic of religious debate among PHP developers, which goes to show that both approaches have their strengths and weaknesses, in this article i will only be discussin about PDO, because it ’ s easier and quicker to learn, but once you ’ ve learned PDO you should find that you can transfer your skills to mysqli if needed.
Making a Database Connection in PHP
To make a connection to a MySQL database in your PHP script, all you need to do is create a new PDO object. When you create the object, you pass in three arguments: the DSN, which describes the database to connect to; the username of the user you want to connect as; and the user ’ s password. The returned PDO object serves as your script ’ s connection to the database:
$conn = new PDO( $dsn, $username, $password );
A DSN , or Database Source Name, is simply a string that describes attributes of the connection such as the type of database system, the location of the database, and the database name. For example, the following DSN can be used to connect to a MySQL database called mydatabase running on the same machine as the PHP engine:
$dsn = “mysql:host=localhost;dbname=mydatabase”;
If host isn ’ t specified, localhost is assumed. So, putting it all together, you could connect to your mydatabase database as follows (replacing mypass with your real root password of course):
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;
$conn = new PDO( $dsn, $username, $password );
When you ’ ve finished with the connection, you should close it so that it ’ s freed up for other scripts to use. Although the PHP engine usually closes connections when a script finishes, it ’ s a good idea to close the connection explicitly to be on the safe side. To close the connection, just assign null to your connection variable. This effectively destroys the PDO object, and therefore the connection:
$conn = null;
Handling Errors
Database errors can be notoriously difficult to track down and deal with. One of the nice things about PDO is that you can get it to return MySQL errors in the form of highly descriptive PDOException objects. You can then use the PHP keywords try and catch to handle these exceptions easily and deal with them appropriately.
To set PDO to raise exceptions whenever database errors occur, you use the PDO::SetAttribute
method to set your PDO object ’ s error mode, as follows:
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
Now you can capture any error that might occur when connecting to the database by using a try …
catch code block. If you were writing a sophisticated application, you ’ d probably log the error message to a file, and possibly send an email to the Webmaster informing him of the details of the error. For the sake of these examples, though, you ’ ll just display the error message in the Web page:
try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}
PHP runs the code within the try block. If an exception is raised by PDO, the catch block stores the
PDOException object in $e , then displays the error message with
$e - > getMessage() .
For example, if the $password variable in the script contained an incorrect password, you ’ d see a
message like this appear when you ran the script:
Connection failed: SQLSTATE[28000] [1045] Access denied for user ‘root’@’localhost’ (using password: YES)
Reading Data from SQL in PHP
Now that you ’ ve connected to your database in your PHP script, you can read some data from the
database using a SELECT statement. To send SQL statements to the MySQL server, you use the query method of the PDO object:
$conn- > query ( $sql );
If your SQL statement returns rows of data as a result set, you can capture the data by assigning the
result of $conn – > query to a variable:
$rows = $conn- > query ( $sql );
The result returned by $conn – > query is actually another type of object, called a PDOStatement object. You can use this object along with a foreach loop to move through all the rows in the result set. Each row is an associative array containing all the field names and values for that row in the table. For example:
$sql = “SELECT * FROM fruit”;
$rows = $conn- > query( $sql );
foreach ( $rows as $row ) {
echo “name = “ . $row[“name”] . “ < br / > ”;
echo “color = “ . $row[“color”] . “ < br / > ”;
}
This simple example shows you how to use PDO to connect to a MySQL server and database, read all the rows of a table, and handle any errors that might occur.
First, you need a database and table to work with. This example assumes that you’ve already created the database called mydatabase, and created and populated the table called fruit, as shown in previous sections. If you haven’t, you can easily re-create the database and table by typing the
following into the MySQL command-line tool:
Now save the following script as get_fruit.php in your document root folder, replacing mypass with the password you set for the root user in MySQL, and run the script in your Web browser.