Mining a User Management System in PHP and MySQL
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
As developed in the book PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, the only real purpose of the database is to validate a user’s credentials when she or he logs in. This is a fine start, but as with any application that’s database-driven, there are always a multitude of ways you can make use of stored data. In this article, I’ll demonstrate a few features you can add using the database as defined in the book, and then show you a few more things made possible by just adding one new column to the database’s users table.
Mining the Existing Data
This is how the users table is defined in the book:
CREATE TABLE `users` ( `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `first_name` VARCHAR(20) NOT NULL, `last_name` VARCHAR (40) NOT NULL, `email` VARCHAR (60) NOT NULL, `pass` CHAR(40) NOT NULL, `user_level` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, `active` char(32) DEFAULT NULL, `registration_date` TIMESTAMP NOT NULL, PRIMARY KEY (`user_id`), UNIQUE KEY `email` (`email`), KEY `login` (`email`,`pass`) )
At the very least, such a table needs to store the user credentials—email address and password being the most common combination. Other particulars about the site’s users, such as names and so forth, would also be put there. From that information alone, a simple COUNT() query can fetch the total number of registered users:
SELECT COUNT(user_id) FROM users
In the book’s specific example, after registering, users receive a confirmation email. To activate the account, the user must click a link in that email, which has the net effect of clearing the active value from the database record. To differentiate between registered users and valid, active users, add a clause to the above query:
SELECT COUNT(user_id) FROM users WHERE active IS NULL
Next, for most database tables, I tend to define a date_added or date_created column, which is an automatically populated timestamp that records when the record was inserted. In this table, that’s registration_date. When you have a column like this in your own tables, another COUNT() query, this time using a WHERE clause, can retrieve the number of people that have registered within a given period of time (say, a day or week):
SELECT COUNT(user_id) FROM users WHERE registration_date > DATE_SUB(NOW(), INTERVAL 7 DAY)
The WHERE clause checks that the registration_date value is greater than the current moment—NOW()—minus seven days.
Adding Data to Mine
In the book, one suggestion I make—which I don’t flesh out there—is to add a last_login column to the users table:
ALTER TABLE `users` ADD `last_login` DATETIME NOT NULL AFTER `active`
This column will be used to reflect the last time each user logged in to the site. After adding the column, you’ll need to change the login.php script so that it updates that column’s value when the user successfully logs in to the site:
$q = "SELECT user_id, first_name, user_level FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
if (@mysqli_num_rows($r) == 1) { // A match was made.
// Register the values:
$_SESSION = mysqli_fetch_array ($r, MYSQLI_ASSOC);
mysqli_free_result($r);
// New code here:
$q = "UPDATE users SET last_login=NOW() WHERE user_id={$_SESSION['user_id']}";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
// End of new code!
mysqli_close($dbc);
Although it doesn’t seem like much, this simple addition brings all sorts of good data to the table (pun intended). For starters, you can now let the user know the last time he or she logged in. To do that, select a formatted version of the last_login value as part of the login query:
SELECT user_id, first_name, user_level, DATE_FORMAT(last_login, '%a, %b %e at %l:%i%p') AS last_login FROM users WHERE (email='$e' AND pass=SHA1('$p')) AND active IS NULL
Once fetched, you can show that date to the user. In the above query, the specific format will be something like Fri, Oct 21 at 11:20AM.
You can also use the last_login column to indicate data about the site’s overall user activity. You can show how many people are currently online—actively using the site—by counting the number of people that logged in within the past X minutes:
SELECT COUNT(user_id) FROM users WHERE last_login > DATE_SUB(NOW(), INTERVAL 15 MINUTE)
That query specifically checks for people that have logged in within the past 15 minutes, but you can increase or decrease this value based upon the actual site activity and how busy you’d like to make your site look.
Conclusion
The power of databases is that once you’ve stored data, you can retrieve it in a myriad of ways, presenting all sorts of information to administrators and public users alike. Even with a simple example, such as a users table, the data already present can be mined more exhaustively than to just validate each user’s login credentials. And, as you’ve now seen, adding just one more bit of information creates several new opportunities.