PHP & MySQL

Now, we’re ready to head back to the PHP and write some code to pull this data out. Let’s start with this:

define("HOST","localhost");
define("DATABASE","dbname");
define("USERNAME","root");
define("PASSWORD","root");
/* Creating database connection */
try {
  $pdo = new PDO("mysql:host=" . HOST . ";dbname=" . DATABASE, USERNAME, PASSWORD);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  $pdo->exec("SET NAMES 'utf8'");
} catch(PDOException $e) {
  die("<div class='alert alert-danger'>ERROR : " . $e->getMessage() . "</div>");
}

host – Remember how I said MySQL is a server? Well, that means it has a host name or IP address that points to it. Since MySQL server is running on the same computer that
username – This is the name of a user on the server, the one you are logging in as. By default, there’s only one user: root. On a real website, you’ll want to create a different user with the right privileges, but for now, using root is fine.
password – Every user has a password: by default, the password for the root user is root. On WAMP, however, there’s no password: it’s a blank string.
database name – This is the name of the database you want to connect to. In our case, that’s php_book.

This should connect successfully. The $mysqli variable now holds our connection object. Now we’re ready to start querying the database. We do so by calling the query method on the $mysqli object, like so:

  $sql = "SELECT * FROM tableName WHERE id = ?";
  $stmt = $pdo->prepare($sql);
  $stmt->bindValue(1, $id);
  $stmt->execute();
  $result = $stmt->fetchAll();

So, after all that, the $results variable will be a mysqli_result object that has several useful properties and methods. If the query failed for some reason, $results would be false. Therefore, we should make sure we have our object:

<?php
  if ($result) { ?>
  <table>
    <tr>
      <th>ID</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Occupation</th>
    </tr>
    <?php foreach ($result as $row) { ?>
    <tr>
      <td><?php echo $row['id']; ?></td>
      <td><?php echo $row['firstName']; ?></td>
      <td><?php echo $row['lastName']; ?></td>
      <td><?php echo $row['occupation']; ?></td>
    </tr>
    <?php } ?>
  </table>
<?php } ?>

You can imagine how this works with our while loop: when the condition is first evaluated, we call fetch_object for the first time and assign its value to $row. Since that will be a record object, which equates to true, we’ll execute the code in the loop.

Next time you come around to evaluating the condition, $row will be assigned the next record, and the loop repeats.

When we’re out of records, $row will be assigned to NULL, and the while loop will stop.

Inside the loop, we’ve got some HTML, with some PHP inside that. You can see how the $row has a property (just like a variable) for every field in our database table, that we’re outputting into the table.