Coding - Databases - PHP

PHP Basics: Connecting and Working with a MySQL Database

PHP is a powerful and flexible language on its own, but one of its real strengths is its integration with MySQL, an open-source database package. Like PHP, MySQL is used in some of the most popular content software on the web, including WordPress, Drupal, and Joomla. By connecting your PHP script with a MySQL database, you’ll be able to create truly dynamic data-driven websites and apps.

If you are brand new to PHP, I suggest taking a break from this article and checking out our Introduction to PHP tutorial first. That will introduce you to the basic concepts of PHP programming and teach you the most basic functions. If you’ve done basic PHP programming, or if you’ve already completed our Intro to PHP tutorial, it’s time to get started with MySQL databases.


PHP and MySQL allow you to store data in a structure that can be as simple or as complicated as you need. The example on the left is taken from an ecological study of hyaenas in Africa, where a very complex set of tables and relationships was needed. Each box in the image represents a data table. Today, we’ll be working with much simpler data.

Installing MySQL

As we did with PHP, we are going to assume for the purposes of this tutorial that you have MySQL installed on your machine or server. Virtually all hosting companies install PHP and MySQL standard, because of the popularity of these two packages. If you do need to install MySQL yourself, click here for instructions.

Creating a MySQL User and Database

If you are using a hosting company, you should have access to some sort of control panel, which will include a section on databases. The different control panel programs will differ slightly, but all of them have a database section, where you can add a MySQL database and user. You’ll need to do both of these before moving on with this tutorial, unless you already have them set up. Again, if you are hosting PHP and MySQL yourself, directions are available for creating a MySQL user here. Either way, keep your database name, user name and password handy, because you’ll need them for the next step. You’ll also need the address of your database host. In most cases, this address will simply be “localhost”, since most databases are (and should be) hosted on the same IP address as the website querying them.

Connecting to Your Database

Now that MySQL is installed and you have a user account created, we can get started. PHP has two sets of functions built in to work with MySQL databases, appropriately named mysql and mysqli. mysqli is short for MySQL-Improved, and is the more up to date version, so that’s what we are going to use for this tutorial. The only reason to use mysql, the older set of functions, is if you are using an old version of PHP or MySQL or if you need your code to work alongside older code that you can’t update to the new standard.

Create a new file on your server, and call it connect.php. Open that new file and add the following code:

    $con = new mysqli("localhost", "user", "password", "databasename");

    if($con->connect_errno > 0){
      die('Unable to connect to database [' . $con->connect_error . ']');

Make sure you replace the four variables in quotes with the actual values for your user and database. These two functions create a new mysqli connection, which is stored in the variable $con, then test the connection to make sure there aren’t any errors. If errors are present in the connection (usually because one of the variables has been misspelled), the error will be displayed on the screen when the file is opened in a browser. Test these lines before we move on. If everything is fine, your browser should just show an empty screen at this point.

Creating and Populating a MySQL Table

Now that we have a database connection, we need to create a data table to work with. Start a new PHP file in the same directory as connect.php, called create.php, and fill it with the following code:

    include 'connect.php';
    $createtable = 'CREATE TABLE IF NOT EXISTS customers (
	firstName varchar (250),
	lastName varchar (250),
	customerSince int,
	homeAddress text,
	homePhone varchar(16)

    if(!$result = $con->query($createtable)){
      die('There was an error running the query [' . $con->error . ']');

There are two functions and a variable declaration in this snippet. The first function, include, takes the code from connect.php and runs it, so you don’t have to rewrite the same code in two different files. Most PHP/MySQL apps have multiple files using the same database connection, so this kind of include is pretty common practice. The variable $createtable is a MySQL query for creating a database table. Note three important words in the beginning of the query: IF NOT EXISTS. That means that no matter how many times the code is run, the table will only be created once. The table is called customers, and holds data on customers’ names, addresses, and phone numbers, as well as a field for noting how long they have been customers. The second function, mysqli->query, runs the query and displays an error if there is anything wrong. This is probably the single most important mysqli function, as it allows you to run any MySQL query. Notice that in the code, the function is labeled $con->query to refer to the particular mysqli connection $con.

Once again, if things are set up right, running this file in your browser should produce a blank screen.

Before we move on, let’s add one more function to enter some example data into the table, so we have something to query. Add the following code to create.php, just before the ?> closing PHP tag:

  $customers = array();
  $customers[] = array('John', 'Smith', 1355314332, 
    '252 SW Example Drive, Portland OR 97212', '503-555-5555');
  $customers[] = array('Alice', 'Jones', 1355425443, 
    '30 Example Place, Los Angeles CA 90832', '555-555-5555');

  foreach ($customers as $ind) {
    $newline = "INSERT INTO customers 
      (firstName, lastName, customerSince, homeAddress, homePhone) 
      VALUES ('$ind[0]', '$ind[1]', $ind[2], '$ind[3]', '$ind[4]')";

    if(!$insertcustomer = $con->query($newline)){
      die('There was an error running the query [' . $con->error . ']');

This code snippet creates a multidimensional array, which is basically an array of arrays, each one holding the data necessary to create one row of the customers table. Then, we use a foreach function to parse each row and insert it into the table, once again using the mysqli-query function as we did before to create the table.

That’s been a lot of work just to set up a table with two rows, but now that it’s there, we can play with it.

Querying Your Data Table

The table in our example holds data on customers: their names, contact information, and when they first bought from our hypothetical company. Two types of display pages that might be useful for parsing that information are a master table showing all customers and a record showing the details for each customer. Using the isset function that we went over in the Introduction to PHP tutorial, we can do both with one PHP script.

Create another new PHP file (the last one, I promise), called customers.php, and fill it with the following code:

    include 'connect.php';
    $id = $_GET['id'];
    if (isset($id)) {

      $getinfo = "SELECT firstName, lastName, 
        customerSince, homeAddress, homePhone
        FROM customers WHERE ID = $id";

      if(!$result = $con->query($getinfo)){
        die('There was an error running the query [' . $con->error . ']');
      } else if ($row = $result->fetch_assoc()){
        echo '<h1>Customer Details: '.$row["firstName"].' '.$row["lastName"].'</h1>';
        echo 'Home Address: '.$row["homeAddress"].'<br />';
        echo 'Home Phone: '.$row["homePhone"].'<br />';
        $sincedate = date('d F Y', $row["customerSince"]);
        echo 'Customer Since: '.$sincedate.'<br />';

    } else {

      $getinfo = "SELECT ID, firstName, lastName
        FROM customers ORDER BY lastName ASC";

      if(!$result = $con->query($getinfo)){
        die('There was an error running the query [' . $con->error . ']');
      } else {
        echo '<table><tr><th>First Name</th><th>Last Name</th><th>Customer Details</th></tr>';
        while ($row = $result->fetch_assoc()){
          echo '<tr><td>'.$row["firstName"].'</td>';
          echo '<td>'.$row["lastName"].'</td>';
          echo '<td><a href="customer.php?id='.$row["ID"].'">Link</a></td></tr>';
        echo '</table>';


This bit of code is basically saying that if the url contains an id GET variable like ?id=1, an individual customer record is shown using that ID. If not, then a table is displayed that links to the individual customer record. Try this page in your browser, and it should look something like this:


Click on the link next to “John Smith”, and you should see a screen like this:


Notice the difference between the two queries. In the first one, only one row of the table is being queried, because only one row will match the $id variable. In the second, every row is included, and the rows are put in order by the lastName variable of each.

We also have a new way of using the date function in this example. Rather than display the current date, as it did in our Introduction to PHP tutorial, date is used here to convert an integer (called a UNIX date) into a date format we can read.

We’ve created a database table, filled it with data, and queried that data in two different ways. As always, play around with the code yourself, replacing fields and functions, until you really understand the nuts and bolts of what is happening in this example. With this solid foundation in PHP/MySQL programming, you can move on to progressively more advanced projects. Happy programming!

About the author

Ian Rose is a web developer, blogger, and writer living in Portland, OR. In his day job, he develops WordPress plugins and custom PHP solutions, focusing on nonprofit clients. By night, he attempts to write both fiction and nonfiction. Ian's site is Seaworthy Web Solutions

Share this post

Leave a Comment

Subscribe To Our Newsletter