Lesson 2 - Routing and templating
Lesson 2.5 - Cleanup
Lesson 3 - Exceptions and logging
Now that you've had time to get the sample application up and running, and have hopefully looked through the code a little bit, it's time to get started cleaning things up. We certainly have our work cut out for us! Starting at the beginning, let's take a look at
index.php. First thing we see isCode: Select all
<?php require_once('dbconn.php'); ?>Code: Select all
<?php
error_reporting(0);
$conn = mysql_connect('localhost', 'root', '') or die('Error establishing database connection');
mysql_select_db('squeaker') or die('Cannot select database');
?>1. error_reporting(0);
While we don't want errors being displayed to the users of our app, we're certainly not doing ourselves any favours by turning them off altogether. Errors will inevitably occur and all we're doing here is making debugging more difficult.
2. mysql_connect
Just no. MySQLi was introduced in PHP 5.0 way back in 2004. PDO was introduced a year later with the release of PHP 5.1. The PHP site itself has been advising against using the mysql extension for literally longer than I can remember, it was officially deprecated in 5.5 (released two years ago), and has been removed from the upcoming PHP 7.
2b. Connecting to the database as root and, worse, with no password. Your root account absolutely needs to have a password, and each app really should have its own dedicated user. As we're using Git for version control, we also want to make sure to keep our credentials outside of our VCS, away from prying eyes.
3. or die();
This is not acceptable error handling. Clearly, we're not going to be able to do much if we can't connect to the database, but the app should present users with a friendly error page -- and a corresponding HTTP response code -- rather than just throwing up.
So how do we address this? With this being the first lesson, and with database calls being required virtually everywhere in our app, this may be the lesson with the most heavy lifting. In the interest of trimming that down at least a little, we'll focus on wrangling our database issues and leave error handling for a future lesson. We will want errors to be displayed while we're in development, so for now we'll change that to a more reasonable default;
E_ALL.There's no question that mysql_* needs to go and given the option of mysqli or PDO, I'll choose PDO every time. If you have looked through the codebase at all, you may also have noticed that we have database calls repeated across pages, as well as others that are only very slightly different. In the interest of promoting code re-use, let's wrap our database calls in an object.
To keep our concerns separated or, rather, to move toward a separation of concerns, we will move all of our application code out of the document root, starting with the new database class we're about to create. You're free to use any naming convention you like, but I generally place my application code in a directory called 'app', making it fairly obvious at a glance what does what.
app/DB.php
Code: Select all
<?php
namespace Squeaker;
use PDO;
class DB {
protected $db;
public function __construct(PDO $db) {
$this->db = $db;
}
}
Code: Select all
namespace Squeaker;Code: Select all
use PDO;db parameter. With that basic setup out of the way, we can start working our way through the app's various page scripts, finding mysql_query calls, and translating them into functions in our DB class. What does that mean in practical terms? Let's start with
browse.php, since it's the first file listed, and we'll work through a couple of examples. The first query we encounter is this:Code: Select all
$sql = "SELECT * FROM users WHERE username = '" . $_SESSION['username'] . "' LIMIT 1";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res)) {
$user_id = $row['id'];
}get fail at both. getUserID is definitely more descriptive, so let's use that.Code: Select all
public function getUserID($username) {
$query = "SELECT id FROM users WHERE username = :username";
$stmt = $this->db->prepare($query);
$exec = $stmt->execute(['username' => $username]);
return $exec ? $stmt->fetchColumn() : false;
}That's great, but how do we use it? We're already requiring
dbconn.php on every page so, for the time being, let's just instantiate our DB class there.Code: Select all
require_once dirname(__DIR__) . '/app/DB.php';
$pdo = new PDO('mysql:host=localhost;dbname=squeaker', 'root', '');
$db = new Squeaker\DB($pdo);Code: Select all
$sql = "SELECT * FROM users WHERE username = '" . $_SESSION['username'] . "' LIMIT 1";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res)) {
$user_id = $row['id'];
}Code: Select all
$user_id = $db->getUserID($_SESSION['username']);Code: Select all
$sql2 = "SELECT * FROM follows WHERE user_id = " . $user_id;
$res2 = mysql_query($sql2);
$following = array();
while ($row = mysql_fetch_array($res2)) {
$following[] = $row['following'];
}Code: Select all
public function getUsersBeingFollowed($user_id) {
$query = "SELECT following FROM follows WHERE user_id = :user_id";
$stmt = $this->db->prepare($query);
$exec = $stmt->execute(['user_id' => $user_id]);
$following = [];
if ($exec) {
while ($id = $stmt->fetchColumn()) {
$following[] = $id;
}
}
return $following;
}lesson-1 tag in the Git repo.With the change from mysql_ to PDO, we're also getting back specific values or arrays of values rather than result sets. This requires some small changes to how we iterate over our results and to the variable names used. Continuing with our
browse.php example, let's take this opportunity to restructure our markup slightly so we're only echoing out the bits we need to instead of having a large and unwieldy PHP echo block. We can easily go from this:Code: Select all
echo '<ul class="users">';
while ($row = mysql_fetch_array($res3)) {
echo '<li>';
echo "<article><h4><a href='user.php?user=" . $row['id'] . "'>";
if (isset($row['display_name']) && !empty($row['display_name'])) {
echo $row['display_name'] . " (" . $row['username'] . ")";
} else {
echo $row['username'];
}
echo "</a></h4>";
echo '<div class="following pull-right">';
if (in_array($row['id'], $following)) {
echo '<a href="unfollow.php?user=' . $row['id'] . '" class="btn btn-danger">Unfollow</a>';
} else {
echo '<a href="follow.php?user=' . $row['id'] . '" class="btn btn-info">Follow</a>';
}
echo '</div>';
echo "</article>";
echo '</li>';
}
echo '</ul>';Code: Select all
<?php if (!empty($users)): ?>
<ul class="users">
<?php foreach ($users as $user): ?>
<li>
<article>
<h4>
<a href="user.php?user=<?= $user->id; ?>">
<?= !empty($user->display_name) ? $user->display_name : $user->username; ?>
</a>
</h4>
<div class="following pull-right">
<?php if (in_array($user->id, $following)): ?>
<a href="unfollow.php?user=<?= $user->id; ?>" class="btn btn-danger">Unfollow</a>
<?php else: ?>
<a href="follow.php?user=<?= $user->id; ?>" class="btn btn-info">Follow</a>
<?php endif; ?>
</div>
</article>
</li>
<?php endforeach; ?>
</ul>
<?php endif; ?>Some important changes we've made.
This snippet found at the top of nearly every page
Code: Select all
$sql = "SELECT * FROM users WHERE username = '" . $_SESSION['username'] . "'";
$res = mysql_query($sql);
$num_rows = mysql_num_rows($res);
if ($num_rows == 1) {
while ($row = mysql_fetch_array($res)) {
$user_id = $row['id'];
}
} else {
die('Something went wrong');
}Code: Select all
$user_id = $db->getUserID($_SESSION['username']);sql2, for example -- have been done away with.No longer echoing HTML.
Indentation has been fixed.
SQL injection has been mitigated through the use of prepared statement.
Once all the mysql_ calls have been replaced with class methods, we can remove the
mysql_connect string from dbconn.php entirely. There's still lots to do, to be sure, but that's a decent start.But wait, you said
What about that?2b. Connecting to the database as root and, worse, with no password. Your root account absolutely needs to have a password, and each app really should have its own dedicated user. As we're using Git for version control, we also want to make sure to keep our credentials outside of our VCS, away from prying eyes.
Hopefully you've created a dedicated database user for your app, but we still need to remove the credentials from the code being committed to GitHub. This is where we're going to pull in our first Composer package; phpdotenv.
At the top level (ie. in the same level as the app and public directories), we're going to create a new file called
composer.json and populate it with the followingCode: Select all
{
"require": {
"vlucas/phpdotenv": "~2.0"
}
}require simply tells Composer about the dependencies for our project and each property contains the package name as key and the version as the value. In our case, we're pulling in version 2.0 of vlucas/phpdotenv. (The leading tilde actually means any version between 2.0.0 and 3.0.0. Read more about versions on the Composer site).With that file created, we run
composer install from the command line and watch as Composer pulls in the dependencies we've requested and drops them in a vendor directory. In order to actually use these packages, though, we need to tell our project where to find them. We'll do this by requiring Composer's autoloader. Let's head back to our dbconn.php file -- which we can use as a sort of temporary bootstrapping file -- and add the followingCode: Select all
require_once dirname(__DIR__) . '/vendor/autoload.php';
.env. We'll place our database connection information in that file and let phpdotenv load it up. The file should look something like this[text]DBHOST = localhost
DBUSER = username
DBPASS = password
DBNAME = squeaker[/text]
though you will obviously want to replace 'username' and 'password' with the actual username and password of your DB user. We will now replace our PDO call from hardcoded values to using those specified in our
.env file like soCode: Select all
$config = new Dotenv\Dotenv(dirname(__DIR__));
$config->load();
$pdo = new PDO('mysql:host=' . getenv('DBHOST') . ';dbname=' . getenv('DBNAME'), getenv('DBUSER'), getenv('DBPASS'));.env file to our .gitignore (which we'll need to create). While we're at it, we can add the vendor directory to make sure nothing composer installs gets committed. Our .gitignore file should therefore look like this[text]/vendor/
.env[/text]
But wait, if
.env is ignored, how will people using our app know which configuration settings are required? Standard practice is to create a .env.example file with all the required settings but using dummy values. Let's do that now.With that done, we commit our changes to Git and call it a day. We've covered a lot of ground, so if you have any questions about this lesson, please be sure to ask them below.
If you're following along but not tracking the Git repo, here is a link to download this lesson