Creating Your First PHP Application: Part 1

Editor’s Note: This is a guest post from Brian Muse, our lead developer on You Rather. He’ll be guiding you through a three part journey of PHP applications over the next few days.

This tutorial is intended for readers who know the very basics of PHP and Object Oriented Programming (OOP) and would like to create a basic web application.

To make this a little bit clearer, I’ve split this tutorial up into three separate posts. Each post will cover a major step in setting up a basic PHP web application.

Series Overview

We’ve got a lot of ground to cover. Here’s a general outline about what to expect from each post in this series:

Part 1 – Setting up the project and creating your first class

• Creating an outline of the project
• Setting up your files and folders
• Creating a class to handle database operations: DB.class.php

Part 2 – Building the rest of the backend

• Creating a User class
• Creating a UserTools class
• Registration / Logging in / Logging out

Part 3 – Building the front end

• Forms
• Form Handling
• Displaying session data

Setting up the Project

Creating a Road Map

It’s always a good idea to know where you’re going. Before you start creating and coding files it’s best to set your goals, map out the project and make decisions about your folder structure and what files you’ll need to make to accomplish your goal. The goal for this project is fairly simple: Create a basic PHP web application with user registration, the ability to log in and out and a way for users to update their settings.

Files and Folder Structure

An OOP PHP project utilizes classes and objects to perform many of the operations that the application requires. When planning, you should think about what classes you will need. For this project we’ll be making three classes. The first is the User class, which will hold information about a particular user and a basic save() function. Another class, UserTools will contain functions that have to do with users, such as login(), logout(), etc. The final class is the first class we’ll be coding: the database class. This class will handle connecting to the database, updating, inserting new rows, retrieving rows, and more.

Aside from classes, we’ll utilize a file called global.inc.php. This file will be called on every page and will perform general operations that we commonly require. For example, it is this file that will handle connecting to the database on each page.

The rest of the files are the pages the user will navigate around. These include index.php, register.php, login.php, logout.php, settings.php and welcome.php.

The final directory structure should look like the image below:

Creating your database and users table

You must have MySQL installed on your server to continue. You’ll first have to create a new database for your application. Within that database to create the users table we’ll be using for this tutorial, use the following SQL:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `join_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

The “id” field is used as the primary key and will be the main unique identifier that we’ll use to differentiate between users in the database. The “username” is also defined as a unique key. Other fields include “password” (which will be stored after it is hashed), “email”, and “join_date” (an sql datetime variable).

Creating DB.class.php

The first class we’ll be making for this project is one to handle database operations. The goal is simple: to take the work out of using our database so that we deal with as little SQL as possible and to have data organized and returned in a easily readable format.

Here is the code, with an explanation following:

<?php
//DB.class.php

class DB {

	protected $db_name = 'yourdatabasename';
	protected $db_user = 'databaseusername';
	protected $db_pass = 'databasepassword';
	protected $db_host = 'localhost';

	//open a connection to the database. Make sure this is called
	//on every page that needs to use the database.
	public function connect() {
		$connection = mysql_connect($this->db_host, $this->db_user, $this->db_pass);
		mysql_select_db($this->db_name);

		return true;
	}

	//takes a mysql row set and returns an associative array, where the keys
	//in the array are the column names in the row set. If singleRow is set to
	//true, then it will return a single row instead of an array of rows.
	public function processRowSet($rowSet, $singleRow=false)
	{
		$resultArray = array();
		while($row = mysql_fetch_assoc($rowSet))
		{
			array_push($resultArray, $row);
		}

		if($singleRow === true)
			return $resultArray[0];

		return $resultArray;
	}

	//Select rows from the database.
	//returns a full row or rows from $table using $where as the where clause.
	//return value is an associative array with column names as keys.
	public function select($table, $where) {
		$sql = "SELECT * FROM $table WHERE $where";
		$result = mysql_query($sql);
		if(mysql_num_rows($result) == 1)
			return $this->processRowSet($result, true);

		return $this->processRowSet($result);
	}

	//Updates a current row in the database.
	//takes an array of data, where the keys in the array are the column names
	//and the values are the data that will be inserted into those columns.
	//$table is the name of the table and $where is the sql where clause.
	public function update($data, $table, $where) {
		foreach ($data as $column => $value) {
			$sql = "UPDATE $table SET $column = $value WHERE $where";
			mysql_query($sql) or die(mysql_error());
		}
		return true;
	}

	//Inserts a new row into the database.
	//takes an array of data, where the keys in the array are the column names
	//and the values are the data that will be inserted into those columns.
	//$table is the name of the table.
	public function insert($data, $table) {

		$columns = "";
		$values = "";

		foreach ($data as $column => $value) {
			$columns .= ($columns == "") ? "" : ", ";
			$columns .= $column;
			$values .= ($values == "") ? "" : ", ";
			$values .= $value;
		}

		$sql = "insert into $table ($columns) values ($values)";

		mysql_query($sql) or die(mysql_error());

		//return the ID of the user in the database.
		return mysql_insert_id();

	}

}

?>

The Code Breakdown

After the class definition you’ll see four variable declarations: $db_name, $db_user, $db_pass, and $db_host. These should be set accordingly, based on how you’ve set up your database. You’ll most likely leave $db_host as localhost. These variables are defined as “protected” and as such they will not be accessible from outside the class. From anywhere inside the class, however, they can be retrieved by using $this->db_name, $this->db_user, etc.

The first function is called connect(). This function uses those protected values to open up a database connection. This connection will remain open for usage anywhere on the current page (not just from within the class).

Here’s an usage example for this function from anywhere outside the class (pretty simple, right?):

//create and instance of the DB class
$db = new DB();

//connect to the database
$db->connect();

The second function is called processRowSet(). The purpose of this function is to take a mysql result object and convert it to an associative array, where the keys are the column names. The function loops through each row in the mysql result and the PHP function mysql_fetch_assoc() converts each row to an associative array. The row is then pushed onto an array which is ultimately returned by the function. This formatting makes the data far more readable and easier to use.

There is a second argument called $singleRow which has false as a default value. If set to true, only a single row will be returned instead of an array of rows. This is useful if you’re only expecting a single result to be returned (for example when selecting a user from the database by using their unique id).

The final three functions perform basic MySQL functions: select, insert, update. The goal of these functions is to minimalize the amount of SQL that needs to be written elsewhere in the application. Each basically builds an SQL query based upon the value passed in and executes that query. In the case of select(), the results are formatted and returned. In the case of update(), true is returned if it succeeded. In the case of insert(), the id of the newly inserted row is returned.

Here is a sample of how you might update a user in the database using the update() function:

//create an instance of the DB class
$db = new DB();

$data = array(
	"username" => "'johndoe'",
	"email" => "'johndoe@email.com'"
);

//Find the user with id = 3 in the database and update the row
//the username to johndoe and the email to johndoe@email.com
$db->update($data, 'users', 'id = 3');

As you can see, the tables column names for the columns being updated are the keys and the values are the data that is being set in those columns.

Extra Credit

Try expanding the DB class to include a function for deleting a row from the database.

Expand the select() function to take an array of column names to select with a default of * to select all columns.

What’s up next?

This wraps up part 1 of the series. We’ve managed to organize our folder/file structure for the project and build our first class, the DB class.

In part 2 we’ll build two more classes, User and UserTools. The User class will introduce class constructors. Additionally we’ll start to take a look at global.inc.php and what exactly we’ll be putting in there.