Notes

Using PHP with MySQL

Edit on GitHub

Databases
2 minutes

Connecting to Database using PDO

The format is like this

1<?php 
2    $db = new PDO("db_engine:db_details", "db_username", "db_user_passowrd");
3?>

Here is an example with values

1<?php 
2    $db = new PDO("mysql:host=189.876.567.123;dbname=shop;port=5678", "developer", "8972g9gh^^%&");
3?>

In database details mysql: tells what database engine to use.

Handling Exceptions

Exceptions happen when something goes wrong. Any code connecting to an external system needs to handle exceptions.

To handle exceptions in PHP we use try and catch

 1<?php 
 2    try {
 3        $db = new PDO("mysql:host=189.876.567.123;dbname=shop;port=5678", "developer", "8972g9gh^^%&");
 4    } catch (Exception, $e) {
 5        // If there is an exception, 
 6        // the code inside these curly braces will get executed
 7        echo "Could not connect to the database."
 8        // Stop any more code from executing using the exit command
 9        exit;
10    }
11    echo "Woo-hoo!"
12?>

Exception is actually an object instantiating PHP’s native exception class. The variable $e will contain the details of the exception. After the exception, stop any more code from executing using the exit command.

It’s a good idea to use a separate try block for each point of interaction.

Querying the Database

To call a method on an object, you first specify the object name followed by a single arrow ->. After that you specify the method name.

1<?php 
2    $db->exec("SET NAMES 'utf8'");
3?>

exec is short for execute SQL command. This method is used to run a sql command on the database. Methods are like functions, they receive arguments. SET NAMES defines the character set, in this case utf8

Handling Exception with PDO

1<?php 
2    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
3?>

The command above will throw an exception when there is an error in the query.

 1<?php 
 2try {
 3    $db = new PDO("mysql:host=123.456.789.987;dbname=shop;port=9876", "db_user", "passowrd");
 4    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 5    $db->exec("SET NAMES 'utf8'");
 6} catch (Exception $e) {
 7    echo "Could not connect to the database";
 8    exit;
 9}
10
11try {
12    $results = $db->query("SELECT name, price FROM products ORDER BY sku ASC");
13    echo "Our query ran successfully";
14} catch (Exception $e) {
15    echo "Data could not be retrieved from the database";
16    exit;
17}
18?>