PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Jul 20, 2017 9:29 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
PostPosted: Tue Aug 11, 2015 6:47 pm 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6254
Location: Montreal, Canada
Lesson 0 - Setup
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 is

Syntax: [ Download ] [ Hide ]
<?php require_once('dbconn.php'); ?>


so let's tackle that before moving on to anything else.

Syntax: [ Download ] [ Hide ]
<?php

error_reporting(0);

$conn = mysql_connect('localhost', 'root', '') or die('Error establishing database connection');
mysql_select_db('squeaker') or die('Cannot select database');

?>


So what's wrong here?

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
Syntax: [ Download ] [ Hide ]
<?php

namespace Squeaker;

use PDO;

class DB {
    protected $db;

    public function __construct(PDO $db) {
        $this->db = $db;
    }
}
 

So there's our base class. We can extend this into a variety of child classes if we wish, say one for users and another for tweets -- I mean squeaks -- but for now I think a single class will suffice. If you're unfamilar with OOP, I highly recommend spending some time reading PHP's OOP manual. I will quickly go over what's happening here.

Syntax: [ Download ] [ Hide ]
namespace Squeaker;

Namespacing is essential, especially when we're working with third party libraries. You create a class called User, some library you're using also has a class called User, PHP needs a way to know which User class is being referred to, and namespacing allows us to do that. You can read more about namespacing in PHP here: http://php.net/manual/en/language.namespaces.basics.php

Syntax: [ Download ] [ Hide ]
use PDO;

Now that we have declared this class to belong to the Squeaker namespace, PHP will assume any classes we reference within this class also belong to the namespace. PDO, of course, does not. The use keyword allows us to specify the full namespace of the class we're referencing. As our class will be using PDO internally, we type hint the constructor to require an instance of the PDO class, which we then store in the object's 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:

Syntax: [ Download ] [ Hide ]
$sql = "SELECT * FROM users WHERE username = '" . $_SESSION['username'] . "' LIMIT 1";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res)) {
    $user_id = $row['id'];
}


Despite the SELECT *, it's fairly obvious we're only interested in retrieving the user ID. A descriptive function name is crucial both to avoid naming collisions and to convey meaning; generic function names like get fail at both. getUserID is definitely more descriptive, so let's use that.

Syntax: [ Download ] [ Hide ]
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;
}


So what's going on here? We've replaced SELECT * with only the fields we're interested in, and used a placeholder for the value we'll be querying on. We create a prepared statement using PDO's prepare() method and pass our arguments in as an array with the placeholders as keys. Finally, as we know we're only retrieving a single column, we return that column's value directly, or false is the query failed.

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.

Syntax: [ Download ] [ Hide ]
require_once dirname(__DIR__) . '/app/DB.php';

$pdo = new PDO('mysql:host=localhost;dbname=squeaker', 'root', '');
$db = new Squeaker\DB($pdo);


Now as we wrap our existing SQL calls in functions, we can replace the existing call with a call to our new function. So this

Syntax: [ Download ] [ Hide ]
$sql = "SELECT * FROM users WHERE username = '" . $_SESSION['username'] . "' LIMIT 1";
$res = mysql_query($sql);
while ($row = mysql_fetch_array($res)) {
    $user_id = $row['id'];
}


becomes this

Syntax: [ Download ] [ Hide ]
$user_id = $db->getUserID($_SESSION['username']);


Make sense? Let's continue on.

Syntax: [ Download ] [ Hide ]
$sql2 = "SELECT * FROM follows WHERE user_id = " . $user_id;
$res2 = mysql_query($sql2);
$following = array();
while ($row = mysql_fetch_array($res2)) {
    $following[] = $row['following'];
}


Now that we've got the user ID, we want to retrieve a list of users this particular user is following. Using very much the same approach as above, let's request only the fields we need, use a descriptive name, and return something sensible.

Syntax: [ Download ] [ Hide ]
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;
}


As you can see, we're following very much the same approach as in the first example. We need to continue working our way through the remaining page scripts, an exercise I will leave to the reader. The finished product can also be compared against what's included in the 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:

Syntax: [ Download ] [ Hide ]
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>';


to this:

Syntax: [ Download ] [ Hide ]
<?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; ?>


Already cleaner and easier to read.

Some important changes we've made.

This snippet found at the top of nearly every page

Syntax: [ Download ] [ Hide ]
$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');
}


has been replaced by a single line.

Syntax: [ Download ] [ Hide ]
$user_id = $db->getUserID($_SESSION['username']);


Meaningless variables -- 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

Quote:
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.


What about that?

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 following

Syntax: [ Download ] [ Hide ]
{
    "require": {
        "vlucas/phpdotenv": "~2.0"
    }
}


If you aren't familiar with Composer, don't worry, this is all quite simple. 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 following

Syntax: [ Download ] [ Hide ]
require_once dirname(__DIR__) . '/vendor/autoload.php';
 

Now that PHP knows where to find the class, let's create our config and load it up. Still at the top level, create a new file called .env. We'll place our database connection information in that file and let phpdotenv load it up. The file should look something like this

Syntax: [ Download ] [ Hide ]
DBHOST = localhost
DBUSER = username
DBPASS = password
DBNAME = squeaker


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 so

Syntax: [ Download ] [ Hide ]
$config = new Dotenv\Dotenv(dirname(__DIR__));
$config->load();

$pdo = new PDO('mysql:host=' . getenv('DBHOST') . ';dbname=' . getenv('DBNAME'), getenv('DBUSER'), getenv('DBPASS'));


That should be working pretty seamlessly, but to make sure we don't commit our credentials to Git, we'll add the .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

Syntax: [ Download ] [ Hide ]
/vendor/
.env


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

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
PostPosted: Wed Sep 30, 2015 1:16 am 
Offline
Site Administrator
User avatar

Joined: Sun May 19, 2002 10:24 pm
Posts: 6883
Very nice!

_________________
Image


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group