Connecting to a MySQL Database with PHP

Working directly with the database is something I rarely do any more with the applications I work with. Instead of creating a raw connection and pulling data directly, I usually utilize some sort of customized data layer. (If you don't know what a data layer is, imagine creating a PHP application with no queries. You still have data being pulled, usually from MySQL table, but the data storage and structure is kept independent of your application. When done correctly, this can help immensely with code cleanliness and development.) However, setting up a connection and pulling information is still an important skill set, so here is a post about the basics. If you're looking for information that is more basic than a PHP-MySQL connection, you can read my series on the basics of the SQL language.

There are several ways to connect to a table, but I'd highly recommend using the MySQL Improved Extension, or mysqli, as a simple but powerful way to connect to your MySQL table. Creating a connection is easy...

//define the parameters
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test_db';

//create the connection
$mysqli = new mysqli(
  $host,
  $user,
  $password,
  $database);

//check for connection problems
if(strlen($mysqli->connect_error) > 0)
  trigger_error("Mysqli Error - {$mysqli->connect_error}", E_ERROR);

Obviously, this connection is best used to handle the default database setup on a local server. Hopefully you're working with a non-local database server and using a more secure user/password combination.

Once the connection is created, it's time to start pulling information. Let's assume that you are looking for a user with id '5' and want to pull up their username and email address.

//define the query
$query = "
  SELECT
    `username`,
    `email_address`
  FROM
    `users`
  WHERE
    `id` = '5'
  LIMIT
    1";

//query the table
if($result = $mysqli->query($query))
{
  while($row = $result->fetch_object())
  {
    echo "username: {$row->username} -- email: {$row->email_address}";
  }
}
else
  trigger_error("Mysqli Error - {$mysqli->error}", E_ERROR);

The query is fairly simple. It is good practice to add a LIMIT 1 to queries that only return a single result to squeeze a little bit of speed out of your database engine. After you pull the query successfully (the check in the if statement checks to make sure your query matches the structure of your table), you can loop through the results. You can treat the intial result as an array, looping through them with the while statement and pulling each one out as an object with fetch_object. This chunk of code does not return an error if no results are found - you many want to add a step for that (you can use $mysqli->num_rows to pull the result size).

There is a small change if you're looking to do more than select from a table.

//define the query
$query = "
  INSERT INTO
    `users` (`username`, `email_address`)
  VALUES
    ('jpemeric', 'test@jacobemerick.com')";

//query the table
if($mysqli->query($query) != true)
  trigger_error("Mysqli Error - {$mysqli->error}", E_ERROR);

Since you aren't expecting a result with an executing statement (INSERT, UPDATE, DELETE, CREATE, etc), the query simply returns true or false. There's no results to loop through, so this chunk of code is much simpler.

There are other best practices - escaping a value before you use it in a query (see php.net's mysqli::real_escape_string for more information) is a great way to avoid sql injection. Some of these chunks of code would be better located within a function or class to keep variables out of the global namespace - especially if you use generic variable names like $query and $result like I did above. If you're interested in something more advanced, check out my post on a singleton Database class. However, this is enough to get started with the data layer of any application... Enjoy!