How to Build a PHP Connection to SQL Databases for HTML5and CSS3 Programming

By Andy Harris

PHP has used a number of mechanisms available to HTML5 and CSS3 programmers for connecting to SQL databases over the years. PHP 5.1 and later now includes a library called PDO (PHP Data Objects) and it’s a significant improvement over the mysql library. Here are a few key features:

  • It works with multiple backends: In the old days, changing a database engine meant re-writing all your code. With PDO, you use exactly the same mechanism with all databases, so it’s much easier to change data engines.

  • It uses object-oriented syntax: PHP supports object-oriented programming, but it uses a slightly different syntax than JavaScript. Object-oriented programming adds some nice features to data access, so this is generally a good thing.

  • It’s provides safer access to data: PDO uses a mechanism called prepared statements which prevent the most challenging kinds of data errors.

With PDO, the connection is an instance of the PDO object. When you make a PDO object, you’re making a connection to the database. The data connection command is chock-full of details:

$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");

There’s a lot of important stuff happening in this line:

  1. Set up a variable to hold the connection.

    The entire point of creating a PDO object is to have a connection object, with various methods for modifying the data and making queries. So the first part of the data connection process is to make a connection object. This one is called $con.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  2. Build a new PDO object.

    Because PDO is object-oriented, use the new keyword to call the PDO object constructor.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  3. Specify the database type.

    MySQL is the most commonly used database system for PHP programmers. However, one of the advantages of PDO is its flexibility. If you change to a different RDBMS, you (theoretically, at least) only need to make one tiny change and the code will still work.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  4. Indicate the host.

    When you’re working on a local XAMPP installation, the host will often be localhost. If you’re on a remote server, you may need to investigate where your databases are hosted. They may be on a completely different machine with its own address.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  5. Specify the database name.

    Within a connection, you might have several databases. Use this part of the connection to determine which database you’re using.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  6. Indicate the username.

    Each database will likely have a specific user determined to be that database’s administrator.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
  7. Provide the password.

    Your program is essentially logging in as the user. This is why it’s good to build a specific user for each application. This allows you to tightly control access to your database.

    $con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");

If you are using the root user with no password, you’re setting up your computer to be hacked.