In its most basic form, a web application is typically comprised of multiple files utilizing PHP and MySQL technologies on a LAMP server. PHP provides a powerful means of carrying out your web application's functions by way of server-side scripting while MySQL is used to store information in a database. In this series of posts I will be showing you how easy it is to create a simple web application framework which you can adapt for almost any purpose - all this with only basic PHP and MySQL knowledge.
The following files will be available as a download in the next part, so don't worry about copying and pasting everything.
Planning & Database Design
Before you start creating your web application you need to figure out what your web application will be doing and what types of things will need to be stored in the database. I will be showing you how to handle user accounts in this how to.
You will have one table dedicated to storing user information; things like user name, password, email, session and user id. Depending on what your web application will do you might have more user-related fields. My party finder web application had additional fields for age, city and state. Then you might have another database table for other types of data stored on your web app - if your web app manages events you would probably have a table for events including fields like date, time, street, zip code, city, state, venue name, etcetera.
I'll leave the design of other database tables up to you so I'll continue with setting up a database and table for users. If you want to learn more about database design, take a look at this great article. Here's what I want to create so far: a database with a "users" table containing the fields "user", "password", "email", "session" and "id". The user id can be used later on for making specific MySQL queries on users since I will setup the id to automatically increment with each new user. Setting up auto-incrementing indexes is also just good programming practice.
Assuming you have phpMyAdmin installed on your server, go to the homepage for your phpMyAdmin install and create a new database named "webapp". For those lacking phpMyAdmin, you can simply run the following code once logged into MySQL.
create database webapp;
Create the "users" table by selecting the newly created database "webapp" in phpMyAdmin and finding the "Create new table on database webapp" text at the bottom. Type in "users" for the name of the table as well as "5" for the number of fields. I recommend using phpMyAdmin for the next part - filling out the details for each of the 5 fields.
The next hurdle to overcome regards which data types and attributes to set each field in our users table. The id for each of the users in the table should be an int(7) - an integer with room for 7 digit values. Unless you are expecting to have more than a million users you'll want to keep that value as small as possible. The same goes for most data types for table fields - keep them as small as possible to reduce overhead and increase speed. Cal Henderson of Flickr explains this concept in great detail in his book Building Scalable Web Sites.
The other fields we have - user, password, email and session, will all be of the type "varchar" but might have varying lengths. For email addresses and user names, I think 32 is long enough for typical user names/email addresses. That would be varchar(32). The sessions field will also be varchar(32) due to the way I will setup PHP sessions. Finally, the password field will be set to varchar(32) since I will implement MD5 encryption before each password is stored or changed.
CREATE TABLE `users` (
`id` INT( 7 ) NOT NULL AUTO_INCREMENT ,
`user` VARCHAR( 32 ) NULL ,
`password` VARCHAR( 32 ) NULL ,
`email` VARCHAR( 32 ) NULL ,
`session` VARCHAR( 32 ) NULL ,
INDEX ( `id` )
) TYPE = MYISAM ;
Overview of the users table in phpMyAdmin
Connecting to the Database
Before I begin with the PHP functions for creating and accessing user information, I need to write a few lines of PHP to connect to the MySQL database. In a file called "util.php" I have written the following lines:
<?php
if( !defined('IN_PHP') )
{
die("hacking attempt");
}
mysql_host = "localhost";
mysql_user = "root"; // YOUR USERNAME
mysql_pass = "root"; // YOUR PASSWORD
mysql_db = webapp;
?>The file will be loaded with each file in the web application requiring access to the database. The first few lines at the top are a minor precaution to ensure that no one can directly view this file and thus steal your database info. In all other web app PHP files I will define "IN_PHP", allowing util.php to be accessed.
Now to actually connect to the database, I have a simple function in a new file called "functions.php" that will also be included in each web app file requiring database connectivity. This function is called xmysql_connect() since there is already a PHP function named mysql_connect(). This file also has the lines for that "IN_PHP" thing for basic security.
<?php
if( !defined('IN_PHP') )
{
die("hacking attempt");
}
function xmysql_connect()
{
global mysql_host, mysql_user, mysql_pass, mysql_db;
@mysql_connect(msql_host, mysql_user, mysql_pass) or die('Could not connect to database: ' . mysql_error());
mysql_select_db(mysql_db) or die('Could not select database: ' . mysql_error());
}
?>When xmysql_connect() is run in other web app files that include util.php, functions.php and define "IN_PHP", a successful connection to the database is made. You can see how it accesses the database information from util.php. One thing you will quickly learn about PHP is that you must declare variables as global before they can be read within a function.
Wherever you see die(), that outputs a statement when the action it is OR'd with fails. For example, if I forgot to specify a database name, mysql_select_db() wouldn't be able to run and would output "Could not select database: " and then concatenate (that's what the dot does) the mysql error. Outputting errors like this is good practice and helpful when it comes time to debug.
How 'bout Them Users?
To manage user accounts, there are several necessary functions I have included in the functions.php file. The first function below, create_user(), takes three parameters and feeds them into the database to create a new user. If your web application keeps track of more than just user name, password and email, you will have to edit this function to accommodate for the extra parameters.
The next function, user_exists(), does as it implies and checks to see if a user by the name user already exists in the database. This is used later on when making new users. Meanwhile, get_user() polls the database to get the current, logged in user's information - based on the session. Finally, do_login() and do_logout() do exactly what you think while make_safe() is a very handy function given to me by Josh Pigford that is heavily used later on to ensure that the site is not vulnerable from mischievous data entry (it prevents things like XSS in forms). This is inline with data input validation for forms. There's a fantastic article that goes in-depth about various methods of data validation (things like validating phone numbers, etc) in the O'Reilly book Web Database Applications with PHP & MySQL.
function create_user(user, password, email) {
encpwd = md5(password);
query = "insert into users set user='user', password='encpwd', email='email'";
result = mysql_query(query);
}
function user_exists(user) {
query = "select user from users where user='user'";
result = mysql_query(query);
if(result == NULL)
return false;
line = mysql_fetch_array(result, MYSQL_ASSOC);
mysql_free_result(result);
return line !== FALSE;
}
function get_user() {
query = "select user from users where session='".session_id()."'";
result = mysql_query(query);
if(result == NULL)
return NULL;
line = mysql_fetch_array(result, MYSQL_ASSOC);
mysql_free_result(result);
if(line === FALSE)
return NULL;
return line['user'];
}
function do_login(user, password) {
encpwd = md5(password); //encrypt password with MD5!
query = "select user, password from users where user='user'";
result = mysql_query(query);
if(result == NULL)
return false;
line = mysql_fetch_array(result, MYSQL_ASSOC);
mysql_free_result(result);
if( line['password'] !== encpwd )
return false;
query = "update users set session='".session_id()."' where user='user'";
result = mysql_query(query);
return true;
}
function do_logout(user) {
query = "update users set session='' where user='user'";
//that clears the session
result = mysql_query(query);
}
function make_safe(variable) {
variable = htmlentities(variable, ENT_QUOTES);
if (get_magic_quotes_gpc()) {
variable = stripslashes(variable);
}
variable = mysql_real_escape_string(trim(variable));
variable = strip_tags(variable);
return variable;
}These functions finish off my functions.php file so now I can begin to concentrate on the main PHP file and forms.
The Basic Framework
Now that I have most of the vital functions taken care of, it's time to start coding the index.php file. Every new file will pretty much require a few includes and commands so creating a header.php file that will be included in each file is an excellent idea. My header.php file is composed of the following lines:
<?php
define('IN_PHP', true);
//very very basic security
include('util.php');
include('functions.php');
session_start();
xmysql_connect();
//these next lines are optional
//they provide unique page titles
url = _SERVER['REQUEST_URI'];
pages = Array(
"/about.php" => "» About",
"/blah.php" => "» Blah",
"/blah2.php" => "» Blah 2"
);
title = pages[url];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<link rel="stylesheet" type="text/css" media="screen" href="style.css"/>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<title>My First Web App <?php echo title; ?></title>
</head>First, I defined my basic security precaution of the "IN_PHP" phrase that util.php and functions.php look for, then I included those two files. After that, session_start() is called to initialize the user session. Finally, the xmysql_connect() function from functions.php is executed. There is now an active connection to the database.
The next PHP lines are a nifty way to dynamically alter page title names depending on what page the user is on. You have to manually input the pages and their accompanying page titles in the pages array. The code grabs the current page requsted by the user with REQUEST_URI, looks in the array and snags the appropriate title. Since the page title is later declared as "My First Web App" and then a PHP echo of title, the full title for the hypothetical about.php would be "My First Web App » About".
The header file also links to a CSS file but I won't be covering any CSS or styling in this series of posts. The header file where you load other javascript files your web app might utilize or things like setting an API key if your web app makes uses Google Maps.
This is getting a bit long so I'll be splitting it up into parts. Tune in for the next segment hopefully tomorrow. You might find it easier to just grab my RSS feed. Part 2 and Part 3 have been published.