How to Retrieve SQL Data with PHP for HTML5 and CSS3 Programming

As an HTML5 and CSS3 programmer, you can use PHP to retrieve SQL data. After a PDO connection is set up, it's pretty easy to use. Here's the overall plan for retrieving data from the PDO connection:

1. Put all PDO code in an exception-handler.

   Data access is inherently dangerous. It's a perfect place for things to go wrong, so use an exception-handler to protect from potential errors. Use the try clause to begin your exception-handler.

     try {

2. Set up your data connection.

   Create a PDO object, setting up your data connection.

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

3. Turn on error-tracking.

   PDO has some features for tracking errors. These are especially useful because the ordinary PHP error codes don't help with PHP problems. Turn on the PDO error-reporting mechanism with the setAttribute() method of the PDO object.

     $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

4. Execute a query.

   The PDO object's query() method allows you to apply a query to the database and returns the result in a special variable.

     $result = $con->query('SELECT * FROM contact');

   The query() method is one of several techniques for getting data from the database. It's a shortcut meant to be used when you're sending an SQL request that's expected to return a result (like a SELECT) statement. Use execute() when you want to pass a command that will not return a result (like a CREATE TABLE or UPDATE) statement.

5. Set the Fetch mode.

   You can tell PDO to return data in a number of formats. For now, choose FETCH_ASSOC. This format returns each record as an associative array. This is the easiest fetch mode to work with. (You can also return each record as a numerically-indexed array, both numeric and associative arrays, and as a special object.)

     $result->setFetchMode(PDO::FETCH_ASSOC);

6. Read the data a row at a time.

   The results of a data query are typically a table, so read the table one row (record) at a time. The $result variable is an ordinary array, so you can easily use a foreach loop to separate the data into rows.

     foreach($result as $row){

7. Each row is an associative array.

   Each row can also be thought of as an array. PDO has a number of ways to extract the data, but you set the fetch mode to associative array in Step 4. This means you can use the associative variant of the foreach loop to very easily separate each row into its name/value pairs.

      foreach ($row as $name=>$value){  

8. Print the field's name and value.

   Now you can simply print out the name and value of the field. Recall you are building HTML output, so you can go with something simple or encode your output in something more sophisticated like a definition list or a table.

      print "<strong>$name:</strong> $value <br />";

9. End all your structures.

   This is a complicated set of instructions. It's really easy to forget a closing structure. Be sure to indent properly and label all your closing braces.

      } // end field loop
      print "<br />";
     } // end record loop

10. Catch exceptions.

    Because all this code happens inside a try block, you need some sort of catch mechanism. Mine simply reports errors.

      } catch(PDOException $e) {
       echo 'ERROR: ' . $e->getMessage();
      } // end try