cult3

How to connect to MySQL through PHP

May 23, 2012

Table of contents:

  1. Setting up your environment
  2. Connecting to MySQL
  3. Connecting to the database
  4. Closing a connection
  5. Advanced PHP MySQL connections
  6. Make a connection within an include file
  7. Using Classes, Objects and Methods
  8. Expanding this example

The real power behind dynamically driven websites is the interaction with a database to store and serve data to the user’s browser. Using a server side launguage like PHP, you can store, query and interact with data from a database. You can’t get very far with a dynamic website without involving a storage system at some point, so interacting with the database will become a crucial element of your website or web application.

Websites like Facebook and Twitter are built using this type of architecture, so if you want to build the next hot web app, you need to learn how it all works!

This is the first of many posts teaching you the in depth details and the behind the scenes of the various aspects of a good website or web application. To ensure you don’t miss any future posts, follow Culttt on Twitter, Facebook, Google Plus or RSS!

Setting up your environment

For the purpose of this tutorial I assume you have already got access to PHP and MySQL. This can either be with access to a webhosting server, or through a local server such as MAMP or WAMP. This software acts like a server, but allows you to run it on your Windows or OS X computer rather than a Linux box. If you are already using Linux, kudos, apt-get what you need and we’ll carry on.

If you don’t already have this set up, go do that now…we’ll wait.

Next you’re going to need to set up a new user in PHPMyAdmin. We need this because every connection to the database must be made through a user account. To do this in PHPMyAdmin go to Priviledges, then click Add a new user. Enter a User name, choose Local from the dropdown menu and enter a password.

Next you need to set up a database and a table.

Click on the PHPMyAdmin logo to return back to the PHPMyAdmin front page.

Enter a name for your database in the “Create new database” field and click Create.

Next click on the SQL tab and copy and past the following into the box. Then click Go. This will create a table called Users with three fields, id, firstName and lastName.

CREATE TABLE Users(
    id int,
    lastName varchar(50),
    firstName varchar(50),
)

Connecting to MySQL

Right, now that you have access to PHP and MySQL, we’ll look at what you need to do to make a connection to MySQL through PHP.

The PHP function we’re going to be using is:

mysql_connect

This function takes 3 arguments in order to work.

These are:

  • The Server address
  • Your Username
  • Your Password

Note: The Username and Password refer to the ones you’ve just set up in PHPMyAdmin and the Server address will usually be Localhost as long as it is located on the same server.

So to make a connection to MySQL, we would write the following.

mysql_connect("localhost", "username", "password");

Remember to replace “username” and “password” with your actual Username and Password!

Save this out as a .php file and load it in your browser. Be ready to be blown away by your awesomeness.

Wait, what?! It’s just a blank page? Don’t worry that’s perfectly normal! When making a connection to the database there is no actual output to the screen. To ensure that our connection is actually working, we can make a little modification to test it.

Update your file with the following code.

$link = mysql_connect("localhost", "username", "password");

if ($link) {
    echo "Yay we have a connection";
} else {
    echo "Oh noes, something went wrong!";
}

Now when you run your file in the browser again you should see one of the two echo statements. If your connection works, change your username and then run the file again to see that you’ve broke it. I think the best way to learn this stuff is to break it, then fix it.

If you are new to PHP and you don’t fully understand line 3 on wards, here is a brief explanation. Basically, this is a statement which checks if something is true or false. By saving the connection into the $link variable, we can test to see if it was successful using the if…else statement. If the connection is made, the $link variable can be seen as true and so we echo the first statement to the screen. On the other hand, if the connection was unsuccessful, the $link variable will be false and so we would echo the other statement to the screen.

Now, going forward, you don’t really want to have this if…else statement wrapped around your connection. However, if for whatever reason your connection fails, you need to made aware of it so you can solve the issue. To do this we need to cause the PHP script to end if the connection was unsuccessful.

To do this, replace your code with the following.

$link = mysql_connect("localhost", "username", "password");

if (!$link) {
    die("Could not connect: " . mysql_error());
}

The code above will attempt to make a connection to MySQL. If the connection is successful the script will just carry on, however if it is unsuccessful your script with die and you will be prompted with an error message.

Connecting to the database

Now that we have connected to MySQL we must connect to the relevent database. On a typical server, you might have any of a number of different databases within the same MySQL installation. To ensure we are connecting to the right data, we must make a connection to the correct database.

To make a connection to the database, add the following chunk of code under your MySQL connection.

// If unable to connect to database throw an error
if (!mysql_select_db(database_name)) {
    die("Could not connect to database: " . mysql_error());
}

Remember to replace database_name with the name of your database!

The above chunk of code will attempt to make a connection to your database. If a connection is made the script will continue, however, if the connection is unsuccessful it will end the script and prompt you with an error. Once again, try breaking it, then fixing it so you understand how it works.

Closing a connection

Whenever we are finished with a connection to MySQL it is important to close it again. Fortunately, this is very easy to accomplish.

Add the following chunk of code to the bottom of your file.

// Close the connection
$close = mysql_close($link);

// If unable to close, terminate and throw error
if (!$close) {
    die("Unable to close connection to the database");
}

Once again, this attempts to close the connection to the database. If it is unsuccessful it will throw an error.

And that is the basics of how to make a connection to MySQL through PHP.

Advanced PHP MySQL connections

As you can see above, making a connection to MySQL through PHP is really very easy. However, there are a couple of things to bare in mind when creating your website or web application’s connection. For instance, if you were going to use the code above to make a connection to MySQL in multiple places around your website or web app, repeating the same code would be extremely bad practice. This is because, at some point in the future you might want to change your Server address, Username or Password. To do that you would have to find every instance of the connection throughout your website and manually change every single one of them.

Object Oriented Programming and Absraction are two important concepts in learning to be a good programmer. Essentially, you should never repeat yourself. So when you find that an aspect of your code is repeated, you should abstract it and replace your code with a reference to the abstraction.

This is a good habit to get into early as it makes your code managable and coherent.

Here are a couple of ways you can connect to MySQL using an OOP (Object Oriented Programming) approach.

Make a connection within an include file

The easiest way to ensure your connection is abstracted to one instance is to simply save the code into a file and then include the file whenever you need it. For example, you might save the following code as connection.php.

$link = mysql_connect("localhost", "username", "password");

if (!$link) {
    die("Could not connect: " . mysql_error());
}

Now whenever you want to use the connection on a page, you simply include the connection file like this.

<?php include "path/to/your/file/connection.php"; ?>

This ensures that you only ever write the connection function once, and if you ever need to change the arguments in the future you can do so in one file and have that change reflected across your whole website.

Using Classes, Objects and Methods

With the introduction of PHP5, true Object Oriented Programming was made possible. Objected Oriented Programming allows the developer to build architecturally sound infrastructure that makes the most out of inheritance, design patterns and the ability to write more clean and managable code.

I won’t go into the ins and outs of how to use Classes and Methods in PHP as it is out of the scope of this tutorial. However, I would suggest you read the following articles from Think Vitamin if you want to find out more.

Now that you’re up to speed with OOP in PHP, I’ll show you an example of a MySQL connection using a class.

First we create a class called database like this.

class database
{
}

Next we declare some properties that we will be using.

class database
{
    // Declare database values
    private $host = localhost;
    private $username = username;
    private $password = password;
    private $database = database;
    private $connection;
}

Next we create a function to make a connection to the database. Notice how we use $this-> to refer to properties within this class.

class database
{
    // Declare database values
    private $host = localhost;
    private $username = username;
    private $password = password;
    private $database = database;
    private $connection;

    /*
    Connect
    */
    public function connect()
    {
        // Make connection
        $this->connection = mysql_connect(
            $this->host,
            $this->username,
            $this->password
        );

        // If unable to connect throw an error
        if (!$this->connection) {
            die("Could not connect: " . mysql_error());
        }

        // If unable to connect to database throw an error
        if (!mysql_select_db($this->database)) {
            die("Could not connect to database: " . mysql_error());
        }

        // Return true on successful connection
        return true;
    }
}

Next we must create a close function to close our connection to the database.

/*
Close
*/
public function close() {
    // Close the connection
    $close = mysql_close($this->connection);

    // If unable to close, terminate and throw error
    if (!$close) {
        die('Unable to close connection to the database');
    }

    // Return true if successful
    return true;
}

Finally, add this chunk of code under you declations near the top of the class.

public function __construct() {
    $this->connect();
}

public function __deconstruct() {
    $this->close();
}

The construct and deconstruct are two “magic methods” that run when the class is instantiated and closed. This means when you create a new instance of your database class, the connection will be made for you so you don’t have to actually call the connect function. And once we’re finished with the class, your connection will automatically be closed for you.

Have a look at 9 Magic Methods for PHP to find out more about construct and deconstruct.

So, putting our database class together, we have the following.

class database
{
    // Declare database values
    private $host = localhost;
    private $username = username;
    private $password = password;
    private $database = database;
    private $connection;

    public function __construct()
    {
        $this->connect();
    }

    public function __deconstruct()
    {
        $this->close();
    }

    /*
    Connect
    Connect to the database
    - Automatically called on load
    */
    public function connect()
    {
        // Make connection
        $this->connection = mysql_connect(
            $this->host,
            $this->username,
            $this->password
        );

        // If unable to connect throw an error
        if (!$this->connection) {
            die("Could not connect: " . mysql_error());
        }

        // If unable to connect to database throw an error
        if (!mysql_select_db($this->database)) {
            die("Could not connect to database: " . mysql_error());
        }

        // Return true on successful connection
        return true;
    }

    /*
    Close
    Close connection to the database
    - Automatically called by __deconstruct
    */
    public function close()
    {
        // Close the connection
        $close = mysql_close($this->connection);

        // If unable to close, terminate and throw error
        if (!$close) {
            die("Unable to close connection to the database");
        }

        // Return true if successful
        return true;
    }
}

Now, whenever you want to interact with your database, you simply make a new instance of your class, like this.

$db = new database();

And then for example, if you had another function that inserted new data into your database called insert(), you would simply run it like this.

$db->insert();

Expanding this example

To further expand this class you could add functions to insert, delete, update or query your database. This would then abstract a good chunk of the natural repetition you will find in your code once you are running queries in multiple places on your website.

One of the beautiful things about classes is that you can expand them so inherited classes can also utilise the powerful functions in the original class. For example you could create a second class called form, but base it on your original database class, like this.

class database
{
    /*
Database functions
*/
}

class form extends database
{
/*
Form functions
*/
}

With the form class you can now add specific form functions but also use the connection and query functions you created in the database class.

I hope this little tutorial on connecting to MySQL through PHP was helpful.

In the future I’ll probably cover a lot more specific areas like this on a variety of topics, so if you have any request, please leave a comment below.

Philip Brown

@philipbrown

© Yellow Flag Ltd 2024.