Using PHP with MySQL - the right way

36 Lionite Mar 27, 2014

PHP and MySQL is one of the most common stacks for web development, even in 2014. With it such a common combination powering so many websites, it is quite shocking that the most common online examples of using it are completely horrible and outdated by at least a decade. Try searching for "php mysql tutorial" and see what you find - it ain't pretty.

In this tutorial I will try and show a simple, yet forward thinking and secure way to use MySQL in PHP. I will explain the reasoning behind each step and compare it with the less desired alternative. This tutorial does not cover installing MySQL on your web server - it focuses on using it in PHP. Perhaps we will cover that in a future tutorial, but that part is usually pretty straightforward.

If it works, don't fix it

If they're so horrible, how come those examples are still so common? because they work. Yes, the code shown on sites like W3Schools and others of its ilk, does work. It's insecure, uses outdated methods, does not protect from change and promotes bad habits, but it does work. Let's look at an example:

<?php
$username="username";
$password="password";
$database="your_database";
$field1-name=$_POST['Value1'];
$field2-name=$_POST['Value2'];
$field3-name=$_POST['Value3'];
$field4-name=$_POST['Value4'];
$field5-name=$_POST['Value5'];
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "INSERT INTO tablename VALUES('','$field1-name','$field2-name',
'$field3-name','$field4-name','$field5-name')";
mysql_query($query);
mysql_close();
?>

What are the problems here?

  • User input is fed directly into query without escaping, making this code vulnerable to SQL injection attacks (or even accidental). We always want to escape user input in database queries, or better yet - use prepared statements (more on that below), so we don't end up with a Bobby Tables situation.
  • mysql_query() function used directly in the code. While this might seem fine for a simple tutorial, those tutorials target inexperienced developers and instill bad habits. You want to abstract database access in case you want to change your database or adapter (PHP extension used to communicate with the database). mysql_query() specifically has been deprecated as of PHP 5.5. It's interesting to note that W3Schools updated their tutorial to use the MySQLi extension, but most tutorials still use the mysql_ functions to demonstrate usage.
  • The error suppression operator is used (@), and a failure on selecting the database kills the script with a generic message (which many of you probably seen in the past on actual production websites). No proper error handling is introduced. How do you debug this script if you're a complete newbie to PHP and MySQL?
  • A database connection is created manually with the credentials in the same script running the query. We would want to abstract creating the connection, so we have a central location to manage it, and likely also keep the credentials in a separate file - especially important if you serve your files directly from the document root directory (web visible). You shouldn't do that anyway, but that is outside the scope of this tutorial (perhaps in a follow up?) - but at least you should protect your database credentials. As a side note, there is no error handling on connecting to the database - which is a much more common error than not being able to select one after connecting.

Doing it the right way

Let's break the process step-by-step, and see how we can improve on the example above.

Choosing an extension

Much of the functionality of PHP is delivered by extensions - plug-in files that are loaded when you start your web server (or PHP process on some configuration). Database access is delivered through extensions, and there are multiple options to choose from.

To keep a long story short, you can go with either the MySQLi or PDO extension and do well. The MySQLi extension supports slightly more use-cases than PDO, while PDO has a uniform API for other databases (such as PostgreSQL and MsSQL), which is nice if you ever need to switch. You can view a more detailed comparison of the various extensions over at the official PHP site.

For the purposes of this tutorial we use the MySQLi extension.

A note about MySQLi:

PHP presents two ways to use MySQLi - procedural (functions) or object-oriented (using the MySQLi set of classes). Both are identical syntax and usage wise, except for the way you call the functions (as standalone or on a MySQLi object). To make things simpler for people who have not yet ventured into object oriented programming (OOP), I use the procedural version. If you do use OOP, it should not be a problem to use this tutorial as a guideline and apply it on the OOP style.

Connecting to the database

The first order of business is connecting to the database. With MySQLi, the function that does that is mysqli_connect():

// Try and connect to the database
$connection = mysqli_connect('localhost',$username,$password,$dbname);

// If connection was not successful, handle the error
if($connection === false) {
    // Handle error - notify administrator, log to a file, show an error screen, etc.
}

Explanation:

  • First we connect to the database with the mysqli_connect() which needs the host name, database username and password as a minimum to create a connection. We also pass the database name as the 4th parameter to select that database for the queries we will run. You can pass additional parameters - port and socket, if your database requires that level of configuration (the defaults work on most installations. You can also change it in your PHP configuration).
  • Then we check if the connection was successful. There are many reasons why the connection could fail - common ones include wrong credentials ( / misconfigured database) and high database usage load which causes the database to refuse new connections. How you handle this error is up to you - common approaches include showing a custom error page to the user, and notifying the administrator (via Email or otherwise) about the error.

Something is missing here though - where do the database credentials come from? As I mentioned previously, it's not good practice to keep those in the database connection script, especially if the PHP files are inside the document root (which means they will be accessible directly from the web). A server error (malfunction or intentional) could cause those files to be displayed as text, revealing your database credentials to everyone viewing it at the time.

If you do not put your PHP scripts inside the document root, but rather include them from outside of it (commonly used with bootstrapping), then this is not a must. I still like to keep my configuration settings in a separate file, which I can refer to any time I need to make overall changes to my PHP application.

Let's create a simple configuration file, which looks like this:

[database]
username = root
password = 1234
dbname = mydb

This is the ini configuration format, for which PHP has very good support out of the box. Save it in a file called config.ini and place it outside of the document root, so it is not accessible from the web. We will now modify our code to load the configuration and use it to create the database connection:

// Load configuration as an array. Use the actual location of your configuration file
$config = parse_ini_file('../config.ini'); 

// Try and connect to the database
$connection = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);

// If connection was not successful, handle the error
if($connection === false) {
    // Handle error - notify administrator, log to a file, show an error screen, etc.
}

We've abstracted the credentials from the connection. But now we're going to go one step higher, and put the connection process inside a function, which we can later use any time we want to access the database. Note that unless you close the connection to the database (as the initial example showed), you only need to connect to the database once during the lifetime of the script.

function db_connect() {

    // Define connection as a static variable, to avoid connecting more than once 
    static $connection;

    // Try and connect to the database, if a connection has not been established yet
    if(!isset($connection)) {
         // Load configuration as an array. Use the actual location of your configuration file
        $config = parse_ini_file('../config.ini'); 
        $connection = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);
    }

    // If connection was not successful, handle the error
    if($connection === false) {
        // Handle error - notify administrator, log to a file, show an error screen, etc.
        return mysqli_connect_error(); 
    }
    return $connection;
}

This our database connection function. I prefixed it with db_, which I will do as well for the rest of the database functions later. If we were using OOP, we could have an even cleaner looking interface by creating a database class which has the operations as the method names (connect(), query(), etc.). An OOP version of this tutorial has been included at the end of the tutorial.

I added one small wrinkle in there - defining $connection as a static variable, which means its state will be kept between function calls to db_connect(). This allows to connect just once to the database, and then just return the connection on future function calls. In the old mysql_connect(), the connection would be kept globally by MySQL. We emulate that behavior here, to avoid establishing a connection every time we need to query the database.

Querying the database

Now that we have a connection, we can start querying the database. The simplest way to go about it is to use the mysqli_query() function.

// Connect to the database
$connection = db_connect();

// Query the database
$result = mysqli_query($connection,$query);

Pretty straightforward. The value of $result will be false if the query failed, otherwise its value depends on the type of query we ran. For SELECT, SHOW, DESCRIBE or EXPLAIN queries, it will return a mysqli_result object, and for all other queries it will return true. As before, let's wrap it up in a function:

function db_query($query) {
    // Connect to the database
    $connection = db_connect();

    // Query the database
    $result = mysqli_query($connection,$query);

    return $result;
}

Now let's take a look at an example query:

// An insertion query. $result will be `true` if successful
$result = db_query("INSERT INTO `users` (`name`,`email`) VALUES ('John Doe','john.doe@gmail.com')");
if($result === false) {
    // Handle failure - log the error, notify administrator, etc.
} else {
    // We successfully inserted a row into the database
}

If $result is false, we would like to have the error message returned by the database. For that purpose we'll create a function that gets it for us:

function db_error() {
    $connection = db_connect();
    return mysqli_error($connection);
}

Then we could use it in the code above:

if($result === false) {
    $error = db_error();
    // Send the error to an administrator, log to a file, etc.
}

Selecting rows

As mentioned above, running a SELECT query (and a couple of other similar types) will return a mysqli_result object. To get the row data itself from the database, we need an additional step that fetches that data. We'll use mysqli_fetch_assoc which retrieves rows from a mysqli_result object as an associated array - the array indexes will be the column names from the database.

// A select query. $result will be a `mysqli_result` object if successful
$result = db_query("SELECT `name`,`email` FROM `users` WHERE id=5");

if($result === false) {
    // Handle failure - log the error, notify administrator, etc.
} else {
    // Fetch all the rows in an array
    $rows = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
}

Now we have an array containing the rows returned from the database. If no rows were returned - the array would be empty. Since fetching rows is a very common use case, we'll wrap it up in its own function:

function db_select($query) {
    $rows = array();
    $result = db_query($query);

    // If query failed, return `false`
    if($result === false) {
        return false;
    }

    // If query was successful, retrieve all the rows into an array
    while ($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
    return $rows;
}

Querying the database now is dead simple:

$rows = db_select("SELECT `name`,`email` FROM `users` WHERE id=5");
if($rows === false) {
    $error = db_error();
    // Handle error - inform administrator, log to file, show error page, etc.
}

If db_select() returned false, we need to handle the error. Otherwise we can use the returned database data in our application.

Escaping dynamic values and user input

In the previous two example queries, I manually placed the values I passed to the database in the query - the user name and email in the INSERT query, and the user id in the SELECT query. If we replace those values with variables that contain unknown values, and especially user input, we need to escape it properly. There are a few characters - notably quotation marks and carriage returns - that will break our query, and in the case of user input allow an attacker to compromise our database (an attack referred to as SQL injection).

For that purpose, we'll use the mysqli_real_escape_string() function. Since it needs a database connection, we'll go ahead and wrap it in its own function. In addition, since we only need to escape strings, we might as well quote the value at the same time:

function db_quote($value) {
    $connection = db_connect();
    return "'" . mysqli_real_escape_string($connection,$value) . "'";
}

If we are not sure of the type of value we pass to the database, it's always best to treat it as a string, escape and quote it. Let's look at a common example - form submission. We'll use our previous INSERT query with user input:

// Quote and escape form submitted values
$name = db_quote($_POST['username']);
$email = db_quote($_POST['email']);

// Insert the values into the database
$result = db_query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");

And there you have it - a safe way to use user input in your queries. As mentioned earlier in the tutorial, an alternative to escaping / quoting values is using prepared statements. MySQLi use of prepared is somewhat verbose and requires more effort to abstract properly to functions like I did here. If you are interested, check out this nice introduction to prepared statements in MySQLi.

Object oriented version

I personally use OOP exclusively when I program in PHP. Since this tutorial is aimed at beginners, I did not want the requirement of OOP proficiency to be able to understand it. In case you come from a background with a different language and have experience with OOP, here is how the above procedural code would look:

class Db {
    // The database connection
    protected static $connection;

    /**
     * Connect to the database
     * 
     * @return bool false on failure / mysqli MySQLi object instance on success
     */
    public function connect() {    
        // Try and connect to the database
        if(!isset(self::$connection)) {
            // Load configuration as an array. Use the actual location of your configuration file
            $config = parse_ini_file('./config.ini'); 
            self::$connection = new mysqli('localhost',$config['username'],$config['password'],$config['dbname']);
        }

        // If connection was not successful, handle the error
        if(self::$connection === false) {
            // Handle error - notify administrator, log to a file, show an error screen, etc.
            return false;
        }
        return self::$connection;
    }

    /**
     * Query the database
     *
     * @param $query The query string
     * @return mixed The result of the mysqli::query() function
     */
    public function query($query) {
        // Connect to the database
        $connection = $this -> connect();

        // Query the database
        $result = $connection -> query($query);

        return $result;
    }

    /**
     * Fetch rows from the database (SELECT query)
     *
     * @param $query The query string
     * @return bool False on failure / array Database rows on success
     */
    public function select($query) {
        $rows = array();
        $result = $this -> query($query);
        if($result === false) {
            return false;
        }
        while ($row = $result -> fetch_assoc()) {
            $rows[] = $row;
        }
        return $rows;
    }

    /**
     * Fetch the last error from the database
     * 
     * @return string Database error message
     */
    public function error() {
        $connection = $this -> connect();
        return $connection -> error;
    }

    /**
     * Quote and escape value for use in a database query
     *
     * @param string $value The value to be quoted and escaped
     * @return string The quoted and escaped string
     */
    public function quote($value) {
        $connection = $this -> connect();
        return "'" . $connection -> real_escape_string($value) . "'";
    }
}

Usage is identical to before, with OOP syntax instead of function calls. Our previous example queries would look like this:

// Our database object
$db = new Db();    

// Quote and escape form submitted values
$name = $db -> quote($_POST['username']);
$email = $db -> quote($_POST['email']);

// Insert the values into the database
$result = $db -> query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");

A select query:

$db = new Db();
$rows = $db -> select("SELECT `name`,`email` FROM `users` WHERE id=5");

And there you have it.

Source files and contributing

All the code found in this tutorial is hosted on GitHub under an MIT license for your convenience. If you have suggestions for improvement or other feedback, use the comments below or submit a pull request. Hope you found it useful!

36 comments


Or enter your name and Email
  • PP Petar Petrov 3 months ago
    Thank you for your great tutorial. Can you make an example using prepared statements?
  • ST Sergio Teixeira 7 months ago
    Hello, i use your function db_connect and db_query(), but i have a question: how i use mysqli_insert_id() and mysqli_affected_rows(), because the value return 0. example: db_query("INSERT INTO .... after this: mysqli_affected_rows(db_connect()); mysqli_insert_id(db_connect()); The value return 0
    • NB Nimitz Batioco 7 months ago
      If you're using the class, you can use this
      public function insert_id() {
              $connection = $this -> connect();
              return $connection -> insert_id;
          }
      
    • NB Nimitz Batioco 7 months ago
      Remember, you can only use this after insert statement has been made. I never tried it using other queries for I believe you can use it only for after insert query has passed.
  • NG Narfle T Garthunk 8 months ago
    So I read above, "If db_select() returned false, we need to handle the error. Otherwise we can use the returned database data in our application." But no example of 'using' the data. Okay, here is an example: Remember he assigned the data to $rows and the database columns are name and email. We take each 'row' in $rows and use it AS $record foreach($rows as $record) { echo $record['name'] . "'s email address is " . $record['email'] ."
    " ; }
  • L Larita 2 years ago
    Hi, How can I check if there are any rows returned by sql Query, and if not then echo something to external file? I'm using procedural.
    • NG Narfle T Garthunk 8 months ago
      So I read above, "If db_select() returned false, we need to handle the error. Otherwise we can use the returned database data in our application." But no example of 'using' the data. Okay, here is an example: Remember he assigned the data to $rows and the database columns are name and email. We take each 'row' in $rows and use it AS $record foreach($rows as $record) { echo $record['name'] . "'s email address is " . $record['email'] ."<br />" ; }
  • MM miss mubashar 2 years ago
    can some body tell me please that how can we seperate database queries from php code??or simply how we can identify database queries?
  • DA Dean Atkins 2 years ago
    Sorry but I'm a complete idiot sometimes so I have to ask how exactly can I echo or print out the values/rows selected from the database when using the db_select function.
    • M MAQ 2 years ago
      i have a same fucking question
    • NG Narfle T Garthunk 8 months ago
      Remember he assigned the data to $rows and the database columns are name and email. We take each 'row' in $rows and use it AS $record foreach($rows as $record) { echo $record['name'] . "'s email address is " . $record['email'] ."<br />" ; }
  • KP khushi parihar 2 years ago
    very essential information!!!!!!!!!!!!A Biggest thank!
  • T tito 2 years ago
    Hi! ¿Where I could put the config.ini file? I have this structure "/public_html/blog/index.php". If I put the file here "/public_html/config.ini", is still accesible. ¿I must use .htaccess or something? Thanks.
    • NG Narfle T Garthunk 8 months ago
      Using the web hosting control panel's file manager, go to the server root directory, the parent of public_html. Create a new folder and put the config.ini there. To have the script find the file, it is the parent directory of WEB Root (public_html) or ../new_folder/config.ini From the web page: web root / current directory ./ parent directory ../ So for your site, it would be located 2 parents up from blog or ../../new_folder/config.ini Hope this helps.
  • DS Deepak Singh 2 years ago
    Thanks Lionite for such a nice tutorial. Looking forward for more tutorials. Thanks once again.
  • R rossi 2 years ago
    I recommend to put the DB connect into the constructor, this will safe the "$connection = $this -> connect();" statement in each function and the static declaration of the connection which can be turned into a private variable.
  • T Toufoo 2 years ago
    Can someone help us (the beginners), we need sample CRUD codes that implements this.
  • M Manish 2 years ago
    foreach ($rows as $value) { extract($value); echo $id.'
    '; echo $name.'
    '; }
  • AC Alexander Rhett Crammer 2 years ago
    Perfect! Thank you so much.
  • AH Angela M Hill 2 years ago
    I have found some of the above information of value. My database is within WampServer 2.5, running on windows 7(32bit). I have created a web based form for inputting data into the database and have been able to use your functions for that. However, I found debugging my code extremely difficult. I am a beginner with only basic knowledge, and that knowledge has not been much help since the update of the SQL module in WampServer, between the version I used at school and the version I am using now. Once I had been able to eradicate some of the errors, I was no longer getting notifications of them. I also was not getting any notifications of my php scripts working. The only way I knew that it had began to work, was the addition of the records appearing in the database in admin view. I am now wanting to display select queries in html with imbedded php scripts. I can't seem to get the function db_select to display results.
  • TV Todd Vance 3 years ago
    First off..THANK YOU! Learned a TON. But I am getting a weird thing with the db_quote ... I get the values being stored with single quotes in my DB. The only way I can get this to work right is to remove those concatted single quotes and just add them in my query.
  • FJ Fredrik Johansson 3 years ago
    Wow! This was clear! Very nice tutorial, Lionite
  • AR Anthony Rutledge 3 years ago
    This is a top not tutorial for beginners. It is well done, clear, and concise. A top, top notch version would do OOP with prepared statements calling MySQL stored procedures, all while using exceptions as the only error handling mechanism.
  • AR Anthony Rutledge 3 years ago
    One hang up about this piece of code ... self::$connection = new mysqli('localhost',$config['username'],$config['password'],$config['dbname']); ...is that you lose the chance to establish some client connection parameters before attempting the connection. Those who have crossed the UTF-8 bridge from days long past might suggest using the following methods or functions. For those who are interested see $mysqli::options() and $mysqli::real_connect(). http://php.net/manual/en/mysqli.options.php http://php.net/manual/en/mysqli.real-connect.php
  • AR Anthony Rutledge 3 years ago
    The PHP manual, at http://php.net/manual/en/mysqli.persistconns.php, suggest that one can simply "prepend p: to the hostname when connecting" to establish a persistent connection. Which method would be better: static variable or p:hostname? Is this the same scenario? Will a persistent connection last across executions of a script (because a static variable will not)? Example: a contact form that makes a connection to MySQL to generate its CAPTCHA. Then, after gathing input, needs to store contact information. Two connections needed, or just one (remember, you never no where form input may be coming from)?
  • PF php fan 3 years ago
    top notch article! must read :D
  • J jacuna 3 years ago
    what about close connection, release results, etc?
    • AR Anthony Rutledge 3 years ago
      I suppose this may be the "nothing on the back end will go wrong" database connection algorithm. Where error handling is accounted for, it uses the PHP error handler. One improvement might be to use set_error_handler() to create a custom handler, then wrap throw new ErrorException($arg, $arg, ...) to convert catchable PHP errors and catchable user generated errors (using trigger_error()) into exceptions. Better still, set mysqli_driver's report mode to MYSQLI_REPORT_STRICT to have mysqli throw exceptions only. Then, embed carefully try/catch statements at the level of the calling code to catch mysqli_sql_exception or other exceptions. One could also imagine embedding various throw statements into a class's method to alter program of other specific exceptions as needed (by extending mysqli_sql_exception).
  • SU Sergio Urra 3 years ago
    Thanks! Any reason for the different quotation marks? You are using ", ' and ´ in the scripts. Best regards
  • JC Joe coleman 3 years ago
    A fantastic source code based example. Five star explanation.
  • KT Karu Tungalag 3 years ago
    Great article. How do you return specific rows in the selecting rows example above?
    • L LuMa 3 years ago
      Hey, have you figured it out yet? I have the same problem :(
    • C Chris 3 years ago
      edit: fixed
    • AS Adam Shaw 2 years ago
      Hi Karu, I struggled with this also. The $result return is a multidimensional array, like: array(1) { [0]=> array(2) { ["userid"]=> string(1) "1" ["count"]=> string(1) "1" } } To retrieve an individual record and in this case echo it: echo $result[0]['count']; I used the guide at W3schools: http://www.w3schools.com/php/php_arrays_multi.asp Good luck... Adam
    • AS Adam Shaw 2 years ago
      PS I scoured the internet for a guide on procedural mysqli and this is by far the best.
  • MB Maurice Brett 4 years ago
    I have read your article using PHP with MySQL - The right way It really impressed me very much. But I cant seem to make use of it because I am not able to call the functions. Can you give an example?