Step by step guide for newbie to design a simple web application (part 1)

Design a web application requires hard work and knowledge of HTML, CSS, database and javascript. What is a simple way to start developing PHP web applications for a newbie?

In the past months I received frequently this question. Some readers of this blog often asked to me to publish a "newbie-oriented" post about how to create a web application using PHP. With this step-by-step guide I want to illustrate some basic tips for newbie to design their first (really simple!) web application using PHP, MySQL, Ajax features and MooTools.

About the application
What kind of application we'll develop in this tutorial? I chose a simple to do list application to manage a list of tasks with these basic features:

- insert task
- mark a task as completed
- remove task
- search task

In this first part, I'll explain how to:

- define application structure
- create a new database for your application
- create database tables and relationships
- setup the application home page

Are you ready? Let's go!


1. Application structure
First question is: how to organize our application? We can start using this simple structure which will be modified in the next steps adding new elements (folders and files):

The main folder todo-list will contain all application files and folders; db folder will contains all files to create and manage application database. connection.php enstablishes a database connection in all pages which require interaction with data stored into our database (see step 2.3).


2.1 - Database: create a new DB
Second step:create a new database for our application to store tasks information. We can use phpMyAdmin to create a new DB:

You have to add a name (in this case todo-list) in the input field and click on create button. Your new database will appear on the left panel: the number (0) indicates your database is empty (no tables).

Note: if you are a Mac user I suggest you to take a look at MAMP to configure PHP+MySQL environment on your system. For more info about MAMP read this post.


2.2 - Database: define DB tables
At this point, we have to define database tables. In our simple to do list application we have only a table TASK with these attributes:

Now we have to create this table into our new database. In general, you can create database tables and relationship directly using phpMyAdmin interface or writing SQL code in a separated PHP file ( tables_structure.php). I prefer to use the second option because it's simpler to manage. So, open tables_structure.php and copy and past this code:

<?php
//Database Connection
include('connection.php');

// Create table TASK
$sql="CREATE TABLE TASK (
task_id_pk INT NOT NULL AUTO_INCREMENT,
task_name VARCHAR(250),
task_description LONGTEXT,
task_completed INT, PRIMARY KEY (task_id_pk)
) TYPE=INNODB";
mysql_query($sql);

// Close DB Connection
mysql_close($db_con);
?>

In the first row I included the following line:

//Database Connection
include('connection.php');

...which enstablishes a database connection (see next step). So I added a PHP var $sql which contains SQL code to create a new table "Task". This code:

mysql_query($sql);

...executes a query with SQL code declared into $sql var.

Note: For more info about how to design a more complex database for your web applications (with relationships between tables) take a look at the following links:
Define relationships-entities model (tables, attributes, and relationships)
Create tables and relationships with SQL
How to use PHP and SQL to create tables and relationships
A correct approach to define relationships between database tables


2.3 - Database: connection.php
Enstablishing a database connection using PHP is very simple. You can use the following code only changing connection parameters values ($db_host, $db_name, $username, $password):

<?php
// Connection Parameters
$db_host="localhost";
$db_name="todo-list";
$username="root";
$password="root";
$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);

// Connection
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>

In general $db_host, $username, $password don't require changes. This var:

$db_name="todo-list";

...contains the name of database we created at step 2.1.

Note: you have to include connection.php in each page which uses interaction with database.


2.4 - Database: create tables
Ok, now you can publish tables_structure.php and connection.php on your testing server and load tables_structure.php with your browser to create tables and relationships (in this case tables_structure.php will create only the table TASK ):

If code it's ok, using phpMyAdmin, you can find the new table "TASK" into our todo-list database:

3.1 - Index.php: include connection.php
Open index.php and add this line of code at the beginning of the page to enstablish a database connection:

<?php include('db/connection.php'); ?>

Remember, you have to include this file in each page which interacts with database:

3.2 - Index.php: include MooTools framewoork
How I said, we'll use MooTools framework to add moder ajax interactions to our application. So we have to add a link to MooTools framework into index.php simply copying the following code within the <head> tag of the page:

<script type="text/jscript" src="mootools.svn.js"></script>

3.3 - Index.php: include CSS file
Now, create an external CSS file style.css in a new folder CSS:

This file will contains CSS code to design application interface elements. So, in the <head;> tag add a link at this CSS file:

<link href="css/style.css" rel="stylesheet" type="text/css" />

Ok, at this point we are ready to design application interface and implement application features. In the next post we'll see how to insert new tasks, mark a task as completed, delete tasks, search tasks and how to design a simple application interface.