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:

 $mysqli = mysqli_connect("localhost", "root", "","php_book"); 
  • 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
  • is running the PHP, we just use the host name localhost.
  • 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:

$result = $mysqli->query("SELECT * FROM characters");

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
  $mysqli = mysqli_connect("localhost", "root", "","php_book");
  $result = $mysqli-> query("SELECT * FROM characters");
  if ($result) { ?>
  <table>
    <tr>
      <th>ID</th>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Occupation</th>
    </tr>
    <?php while ($row = $result-> fetch_object()) { ?>
    <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 } ?>

While the condition is true, the code in the block will continue to execute. In our case, we’re not really using a normal condition: we’re assigning a variable.

$row = $result->fetch_object();

The fetch_object method will return each of the records we got from the database, one at a time. So, the first time you call the method, you’ll get the first record; the second time, you’ll get the second record, and so on. When we’re out of records, fetch_object will return NULL.

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.


comments powered by Disqus