This PHP Tutorial Series has gone soup to nuts covering all of the fundamental aspects of using PHP to build dynamic websites. Based on all of the concepts covered so far, you should be able to build links to pages, pass data via $_GET in those links, create forms, collect user data via $_POST in those forms, as well as use cookies and sessions to establish state and customized user experiences. One thing we haven’t talked about yet is working with MySQL in PHP. A fantastic place to start if you are new to MySQL is right at our dedicated 10 Part MySQL Tutorial Series. That will give you a fantastic foundation on which to build upon for working with MySQL in PHP. In this part of our PHP Tutorial Series, we’ll take a look at MySQL in terms of using PHP to read and write data, store data, organize data, as well as set up relationships of data.

Database APIs in PHP

First off let’s talk a little bit about database apis in PHP. In plain vanilla terms we have three options that are built into the PHP language. The first is mysql, which is the original MySQL api for PHP. The next option is mysqli, which is the MySQL Improved version of the api. Lastly, PHP has PDO, or PHP Data Objects. In reality, the old mysql can be largely ignored unless you are tasked with maintaining a legacy code base. In the current web development world you’ll be using either mysqli or PDO. Let’s look at a chart to view the differences between the three.

This is a great overview of your options of using PHP to interact with your database. In this episode we’re going to look at mysqli and focus on the procedural approach. This is not to say that you can not use mysqli in an object oriented approach, you very much can. In fact let’s compare the two approaches side by side.

The 5 Steps

Now that we have a bit of a high level overview, let’s look at the five steps of database connectivity in PHP. These five steps are really the bread and butter of connecting to your database and you will use this approach no matter what type of interface you choose, so it makes sense to understand the concept of each step. This will make you a better programmer and give you a better ability to troubleshoot issues when things don’t quite go the way you had planned.

• 1. Create The Database Connection. You can think of this step almost like picking up a telephone and making a call to someone. Once that call is established, you have a valid connection. The same idea applies when dealing with databases.
• 2. Perform A Query. With your knowledge of MySQL, you can now begin running queries using SELECT INSERT UPDATE and DELETE in addition to the other commands available.
• 3. Make Use of Data Retrieved. In the case of getting back any data from a query, you can make use of that data. It may involve emailing that data to someone or just outputting it to the screen.
• 4. Release Memory. Once you’re done with the prior operation, it’s time to release any memory that was used to hold the data from step 3.
• 5. Close The Connection. Once the conversation between PHP and the Database is complete, you need to close the connection. This is much like hanging up the telephone once your conversation is complete.

Opening and Closing Connections

We have the 5 concepts down. Now let’s look at the commands in PHP that make this happen. First up we’ll focus on steps 1 and 5 since we need to know how to open and close connections before we can do anything else. The commands you’ll need are mysqli_connect, mysqli_connect_errno, mysqli_connect_error, and mysqli_close.

database_connect.php

<?php

// open a connection
$dhost = 'localhost';
$duser = 'root';
$dpw = '';
$dname = 'wordpress';
$connection = mysqli_connect($dhost, $duser, $dpw, $dname);

// test the connection
if(mysqli_connect_errno()){
    die('Something went wrong with the database<br><br> '
    . mysqli_connect_error() . ':' 
    . mysqli_connect_errno());
} else {
    var_dump($connection);
}

// close the connection
if(mysqli_close($connection) == true) {
    echo 'Database connection closed.';
}

?>

This is what you get when everything goes right, an object of type mysqli.

object(mysqli)[1]
  public 'affected_rows' => null
  public 'client_info' => null
  public 'client_version' => null
  public 'connect_errno' => null
  public 'connect_error' => null
  public 'errno' => null
  public 'error' => null
  public 'error_list' => null
  public 'field_count' => null
  public 'host_info' => null
  public 'info' => null
  public 'insert_id' => null
  public 'server_info' => null
  public 'server_version' => null
  public 'stat' => null
  public 'sqlstate' => null
  public 'protocol_version' => null
  public 'thread_id' => null
  public 'warning_count' => null

Database connection closed.

Now what happens when things go wrong? Let’s see what happens if we change the credentials for each parameter.

Wrong Host

Something went wrong with the database

php_network_getaddresses: getaddrinfo failed: No such host is known. :2002

Script ended unexpectedly.

Wrong User

Something went wrong with the database

Access denied for user ''@'localhost' to database 'wordpress':1044

Script ended unexpectedly.

Wrong Password

Something went wrong with the database

Access denied for user 'root'@'localhost' (using password: YES):1045

Script ended unexpectedly.

Wrong Database Name

Something went wrong with the database

Unknown database 'imaginary_database':1049

Script ended unexpectedly.

It is human nature to jump right into things and say, “Hey! What could possibly go wrong?!” We know that things can and do go wrong, so it’s best to include the code that can output some useful information to you so that you understand the problem.

Retrieve Data From MySQL Using PHP

You made it past the connection phase. MySQL gave you a green light and said you’re good to go. Now that you are able to get into the database, you need to actually execute some commands to find useful data. Let’s start with mysqli_query, mysqli_fetch_row, and mysqli_free_result. In this example we’re going to fetch all posts from a wordpress database and just var_dump each result to the screen.

<?php

// Step 1
// open a connection
$dhost = 'localhost';
$duser = 'root';
$dpw = '';
$dname = 'wordpress';
$connection = mysqli_connect($dhost, $duser, $dpw, $dname);

// Step 2
// test the connection
if(mysqli_connect_errno()){
    die('Something went wrong with the database<br><br> '
    . mysqli_connect_error() . ':' 
    . mysqli_connect_errno());
}

// Step 3
// define the query
$query1 = "select * from wp_posts";

// build up the query style
$query2 = "SELECT * ";
$query2 .= "FROM `wp_posts` ";
$query2 .= "WHERE `post_title` LIKE '%laravel%' ";
$query2 .= "LIMIT 0 , 30 ";

// Step 4
// run the query to get a resource placed in $result
$result = mysqli_query($connection, $query2);

// Step 5
// test to see if the query was successful
if(!$result) {
    die('The query was not successful.');
} else {
    // Step 6
    // the query was successful so let's get the data if there is some
    // as long as there is another row in the $result, assign it to $row
    while($row = mysqli_fetch_row($result)) {
        // output each row
        var_dump($row);
    }
    
    // Step 7
    // Release the returned data
    mysqli_free_result($result);
}

// Step 8
// close the connection
if(mysqli_close($connection) == true) {
    echo 'Database connection closed.';
}

?>

Note that we use two styles to build up the query. If you like you can simply build the query in one big long string as we do in $query1, or, you can incrementally build the query string using the .= operator like we did with $query2. This sometimes helps to be able to ‘think out’ your query so to speak.

Now we can see why PHP frameworks are so popular. All of this leg work is done pretty much automagically for you. When using raw PHP, you’re going to need to write all of this code to talk to and work with the database. In truth however, it is super important that you can actually do this work using raw PHP for if you can’t, you will be dead in the water if something goes wrong on you when you’re using a framework. This is because you won’t have even the slightest clue as to how the framework might be handling the native code for you. So let’s review the basic idea of the steps needed to make a query.

Make A Database Connection

$connection = mysqli_connect( $dhost , $duser , $dpw , $dname )

Test The Connection

mysqli_connect_errno()

mysqli_connect_error()

Run A Query

$result = mysqli_query( $connection , 'select * from wp_posts' );

Extract Data From Query Result

mysqli_fetch_row( $result )

Free The Memory

mysqli_free_result( $result )

Close The Database Connection

mysqli_close( $connection )

Conclusion

There you have it friends, everything you need to use native PHP to work with a MySQL database. Yes, it almost seems like a lost art in these days of Hyper Capable Modern PHP Frameworks, but it pays to come back to basics to make sure the foundation is solid.

Process Returned MySQL Query Results In PHP PHP Cookies And Sessions How to Use PDO PHP Data Objects Getting Started with Data Manipulation Language in MySQL PHP MySQL CRUD Tutorial Digg Reader Review