PHP Tutorial–Guest Book–System Definition and Requirements

In this lesson I’m going to illustrate the system definition and requirements for the Guest Book application.

It is an important step in developing any application, since this step gives us an orientation about the system and its functionality, what is expected from the system to do. actually the system definition comes from keywords from customers and end users, usually try to make it clear for what they need in the application, and what they expect the system to do. 

As developers it is our role to define the requirements for the system to be developed. In our example the system definition for the Guest Book is: A system that allows visitors of the site to post their comments and feedbacks about the site, with the possibility for managing comments and maintain it easily and user friendly.

On the other hand, the system requirements are:

  • a web server, since it is clear that the developed system is going to run on the Internet, so it is a web-based application not windows-based.
  • Database server, in order to store the posts inside and retrieve it easily and on time access. In our example am going to use MySQL server.
  • Programming language, to apply the logic of the developed application and solve achieve the needed system. In our case, we are going to use PHP for this job, therefore the web server must be able to deploy PHP on it, so we might use Apache as server, we might use any server that supports PHP.
  • For user interface, we need XHTML and CSS to provide a user friendly interface, moreover we might use some codes of JavaScript to maintain ease of use and validating user inputs.

We might look for the system as a 3-Tier Layer, the Data Tier (MySQL Server), the Middle Tier or Business Tier (Apache Server and PHP), and the Presentation tier (XHTML, CSS,…).

That’s all for aspects related to the system definition and requirements, our next lesson will focus on analysis and design for the database including the implementation for it. Until then wish you all enjoy the development steps for the Guest Book application, any questions or feedbacks don’t hesitate to share it with us.

To download this lesson as PDF click here: PHP Tutorial–Guest Book–System Definition and Requirements

Cheers,

Anas Jaghoub

Continuing with the PHP Tutorial Series

Dear followers of the PHP Tutorial, hope you all enjoyed the PHP, and got benefits from this simple tutorial. As I promised you before, the next series of the PHP Tutorial focus on developing web-based applications using PHP.

To make the development more useful and close to real world applications, we need to focus on the user interface, and cover aspects related to it, so we moved for two short tutorials that covers the User Interface aspects using XHTML and CSS, and learned the basics for developing a cross browser compatible pages, and how to apply standards in developing the web page.

From Now on, we are going to develop our first we application in this tutorial, which is a guest book, that allow users to post comments and there’ll be a simple admin panel to manage the comments.

The development for the Guest Book goes on these steps:

1. System definition and requirements.

2. Analysis and design for the database (Includes implementing the database).

3. Implementing the system logic (PHP).

4. User interface design.

5. Deployment.

Hope you enjoy the development process for the application, and if you have any questions or feedback don’t hesitate to tell us about it.

Cheers,

Anas Jaghoub

PHP Tutorial–Lesson 5: Introduction to PHP- Part III

PHP and MySQL

•Our previous application displays all employees in one web page, how about displaying information about one employee?

•To display information about a single employee we have to specify some information about this employee, such as his id.

•Our next application, will enhance the previous one, and improve it to display information for a single employee.

Example:

<?php

// inquery.php

$form=‘<form name=“employee” method=“get” action=“employee.php”>

Employee No: <input type=“text” name=“id”><Br>

<input type=submit value=“Go”>

</form>’;

echo $form;

?>

<?php

// Employee.php

// last modified 13/oct/2009

require(“../include/config.php”);

If(isset($_GET[‘id’]))

$id = $_GET[‘id’];

else die(“Unexpected value for employee id”);

$sql = “SELECT * FROM employee WHERE id=$id”;

$result = mysql_query($sql);

If(!$result) die(“MySql Error: <br>“.mysql_error());

If(mysql_num_rows($result) == 0)

echo “No information available<Br>”;

else

{

$row = mysql_fetch_array($result);

echo $row[‘id’].”<br>”;

$row[‘name’] .”<br>”;

echo $row[‘department’] .”<br>”;

echo $row[‘jobTitle’] .”<br>”;

echo $row[‘salary’] .”<br>”;

echo $row[‘telephone’] .”<br>”;

echo $row[‘address’] .”<br>”;

echo $row[‘cv’] .”<br>”;

}

?>

Notes:

•Inquery.php Script:

•It is a GUI screen, that allows you to view information about any employee by filling his id number.

•In this script I chose id, because it is unique, but you can inquiry using any other info related to the employee, such as his name, salary.

•Form method is get, because we want to get information from db.

•GET method propagate form data in the URL to the destination which is in our example: employee.php

•Before submitting the form, you might use your JavaScript skills to check whether the user has entered expected values or not, this will save time, and prevent server from hacking.

•After submitting the form, URL will look like this: yourdomain/employee.php?id=“value of id field”

•Employee.php Script:

•This script is an enhancement for the previous one in the last lesson, you can refer to it here: PHP Tutorial–Lesson 5: Introduction to PHP- Part II.

So am going to illustrate new things in it.

•$_GET[‘id’]: is a built-in array that holds variables propagated with URL. It is an associated array, and its key is variable name propagated with URL.

•Next we check for $_GET[‘id’] if it has value or not, by passing it to function isset. This will tell us whether the id has a value or not.

•If not. Then it will alert the user that id field is required and must be filled.

•In this case you might use javascript and follow single/double quotes rules, to make your application more user interactive.

•If $_GET[‘id’] has value then it seems to be that it had been set through your form. But not completely correct because it might be filled through hacking script. Anyhow, we expect that it had been filled through the form.

•You have to validate, each user inputs before entering it to database.

•Next, we check whether there is an employee whose id is the given id, using a built-in function offered by php which is mysql_num_rows() that returns an integer number of records that matches your query.

•We didn’t loop through all records, because it is only one record at most, and only for one employee, remember we restricted it with a unique value which is id.

End of Lesson 5, To download this lesson as PDF file click here: PHP Tutorial–Lesson 5: Introduction to PHP- Part III

Best Wishes,

Anas Jaghoub

PHP Tutorial–Lesson 5: Introduction to PHP- Part II

PHP and MySQL

MySQL query through PHP script:

•Remember rule of single/double quotes when dealing with non-php script inside php script? We’ll apply it now with MySQL queries.

•Each MySQL query will be stored in a variable called for example $sql.

•We’ll use a built-in function that executes the query, the function is mysql_query(), and takes as parameter $sql value i.e. MySQL query.

•The returned value of this function is true or false, we’ll store it in a variable called for example $result.

•Finally, we’ll check whether your query executed successfully or not.

•These steps will be followed at each time we want to run MySQL query using PHP.

•We’ll suppose that you saved your config.php file that includes your connection configuration in an external directory called include. for more information on how to configure connection to MySQL server revise Previous Lesson on the Link: PHP Tutorial–Lesson 5: Introduction to PHP- Part I

Example:

<?php

// Employees.php

// Created in 13/10/2009

require(“../include/config.php”);

$sql = “SELECT * FROM employee”;

$result = mysql_query($sql);

If(!$result) die(“MySql Error: <br />“.mysql_error());

$tableHeader = ‘<table border=“1” width=“100%”>

<tr>

<th width=“10%”>ID</th>

<th width=“30%”>Emplyee Name</th>

<th width=“30%”>Department</th>

<th width=“20%”>Job Title</th>

<th width=“10%”>Salary</th>

<th width=“10%”>Telephone</th>

</tr>’;

echo $tableHeader;

while($row = mysql_fetch_array($result))

{

echo “<tr>”;

echo “<td>”.$row[‘id’].”</td>”;

echo “<td>”.$row[‘name’].”</td>”;

echo “<td>”.$row[‘department’].”</td>”;

echo “<td>”.$row[‘jobTitle’].”</td>”;

echo “<td>”.$row[‘salary’].”</td>”;

echo “<td>”.$row[‘telephone’].”</td>”;

echo “</tr>”;

}

echo “</table>”;

?>

Notes:

•Require: in order to make connection with MySQL server, We used require to insure that this script depends completely on connecting to db. If the file is not found then the script will not work, and prevent it from errors. remember difference between require and include in PHP Tutorial – Lesson 2: Introduction to PHP – Part III at Server Side Include SSI part.  

•../include/config.php: if the included file in a parent directory, and your script in a child directory, then the way to reach the included file is putting ../ At beginning of the address. If both the included file and the script are in same directory then we directly write it like this: require(“config.php”);

•Purpose of this script is displaying all employees information in the company.

•The MySQL query for this case is SELECT.

•$sql is a variable that stores a non-php code, in our case it stores MySQL query.

•Note until this line, your query has not yet been executed.

•$result is a variable that stores MySQL response for your query. Its value depends on the returned value of mysql_query().

•mysql_query($sql) is the function that makes your query become executed. This function takes one parameter which is MySQL query. And returns the response of MySQL. If the query succeeded then response will be stored in $result. Else false value will be stored in $result.

•After that we checked whether $result has been executed successfully or not. If not then we force the program to stop execution by calling die function and printing the error message from MySQL using mysql_error function.

•These couple of lines will be used at every MySQL query executed from php script, regardless what the query is.

•$tableHeader is a variable that holds html format for table.

•We stored this in a variable in order to be able to use it as needed.

•When mysql_query returns MySQL response about a query, it returns all records regardless how much they are in the same variable $result.

•PHP offers a built-in function called mysql_fetch_array() that separate each record in the returned query and save it in an array called $row.

•$row is a multi-dimensional associated array, which means we refer to each cell in it by key value, not index number.The key value of $row is your table columns.

•Finally, we loop through all records using while loop. Until reaching null value to $row which means this is end of records.

To download this lesson as PDF file click here: PHP Tutorial–Lesson 5: Introduction to PHP- Part II

Best Wishes,

Anas Jaghoub

PHP Tutorial–Lesson 5: Introduction to PHP- Part I

PHP and MySQL

•Our previous examples focused on how to write correct MySQL syntax.

•Now, we are going to learn how to take advantages of MySQL queries in our PHP applications.

•PHP, offers number of built-in functions that deal with MySQL db.

•These functions communicate with MySQL db. and allow us to run MySQL queries through PHP Script.

Connectivity to MySQL

•Before getting access to database, we had to start a connection with the server where it lies on.

•To start a connection between your script and db. PHP offers function mysql_connect().

•This function takes three parameters, first one is MySQL server address. Second one is username for the user of the db. And last one is password for that user.

Example

mysql_connect(“localhost”,”anas”,”1234”);

•A call to mysql_connect() will start a connection with the specified MySQL server and username and password.

•This function returns a value of true or false.

•If the connection succeeded, then it returns true, so you can complete.

•If the connection failed, then it returns false, which means your script will not work well as expected.

•It is important to know whether the connection succeeded or not. And if not, determine where is the error.

•PHP offers a function that tells you what is the MySQL error message for your query.

•It is important to make your script stop working if the connection failed with your db server, this will increase security of your application.

Example:

<?php

$host = “localhost”;

$username = “anas”;

$password = “123”;

$connected = mysql_connect($host,$username,$password);

if(!$connected)

{

die(“Unable to connect to db<br />”.mysql_error() );

}

mysql_close($connected);

?>

Notes:

•$host: is the MySQL server address you want to make connection on.

•$username: is the username of the user who uses the MySQL server.

•$password: is the password of the user who uses the MySQL server.

•$connected: stores the returned value of function mysql_connect. Which is true or false.

•We checked for value of $connected. If it is true, then we can complete our application. Else then we had to stop executing the script, in order to avoid unexpected errors.

•Function die like function exit in C/C++. Its job is stopping the script execution.

•It is important to know what the error that prevented your query succession is. To get this, PHP offers mysql_error() function that returns a string contains the error message that MySQL server responded to you about your query.

•At end of each php script that communicates with MySQL server, it is preferred to close the connection, although this connection will be closed automatically at end of script. It is important to close it manually by calling function mysql_close() with parameter of the connection to be closed.

•Once we had connected to the MySQL server, we have to select which db we want to deal with.

•PHP offers function mysql_select_db() that takes two parameters, first one is database_name, second parameter is the connection.

•This function call comes after starting succeeded connection with db.

<?php

// config.php

$host = “localhost”;

$username=“anas”;

$password=“1234”;

$connected = mysql_connect($host,$username,$password);

if(!$connected) die(“unable to connect to db<br />”.mysql_error());

mysql_select_db(“company”,$connected);

mysql_close($connected);

?>

•To organize your project, and make development acceleration improve smoothly, it is preferred to save previous code in a file called for example: config.php and put this file in a directory named for example include.

•These steps, will save your time, and avoid you write these lines each time you want to connect to your MySQL server. Then include this file in each script you need to communicate with db.

To download this lesson as PDF click here: PHP Tutorial–Lesson 5: Introduction to PHP- Part I

Best Wishes,

Anas Jaghoub

PHP Tutorial–Lesson 4: Introduction to PHP- Part IV

Queries

INSERT:

we had created our db, then created our table, now it is time to insert records to the table.

•Records: in our table employee, a record is an employee information.

•To insert record into our table, or in other words to add an employee to the table. We use the query: INSERT INTO.

•Syntax:

INSERT INTO table_name (col1,col2,…) VALUES (val1,val2,…)

•Notes:

•INSERT INTO instructs your db to add a new record.

•table_name specifies the table you want to add a new record inside.

•(col1,col2,…) specifies attributes you want to add values to it.

•VALUES(val1,val2,…) specifies values of attributes you want to add.

•The order here is very important.

Example:

INSERT INTO employee

(name, socialNo, department, jobTitle, salary, telephone)

VALUES (“Anas”, 1234, ”IT”, ”Programmer”, 1000, ”07x-xxxxxxx”)

Notes:

•NOT NULL fields must be filled on each record insertion.

•NULL fields are optional, you might assign a value to it or not.

•AUTO_INCREMENT: it is optional to assign a value for an AUTO_INCREMENT field, if you want to assign its value manually, then be careful, value must be unique. I prefer leaving it to the db to assign it a suitable value.

•Previous query, will add a new row to table employee, and will look like this:

image

SELECT:

•Till now, we completed the first inquiry of our customer, which is storing employees information.

•Now, we are going to execute second inquiry which is accessing stored information.

•To get access for stored records in MySql table, we use the SELECT query.

Syntax:

SELECT * FROM table name

Example:

SELECT * FROM employee

• The * means get all table columns values.

•Executing this query will print all records in the table employee with all data stored inside.

WHERE Statement:

often, we want to access specific record, and get data belongs to it. Our previous query selects all records, and this will make it hard to view and get information from it.

•To access specific record/s in a table, we add some properties to our query, and restrict it in a number of records.

Example:

•To get information about all employees in IT department we might run this query:

SELECT * FROM employee

WHERE department = “IT”

•To get information about all employees whose salary greater than 1000, query looks like this:

SELECT * FROM employee

WHERE salary > 1000

Operators used with WHERE statement:

= Equal.

<> Not equal.

> Greater than.

< Less than.

>= Greater than or equal.

<= Less than or equal.

BETWEEN: between an inclusive range.

LIKE: search for a pattern.

ORDER BY:

used to order selected records ascending or descending.

Syntax:

•SELECT * FROM table_name ORDER BY column_name ASC| DESC

•table_name: is the table we want to select records from it.

•column_name: data to be sorted.

•ASC | DESC: method for sorting, by default it is sorted ascending.

Example:

SELECT * FROM employee

WHERE salary >1000

ORDER BY salary DESC

•This query will display all employees whose salary greater than 1000 and order them descending.

UPDATE:

used to update record values.

•Sometimes we need to update the value of a specific field, for example in our table employee, suppose one employee his job title has been upgraded from head-department to manager, or has a bonus 10% on his salary. So we want to modify this employee information.

Syntax:

•UPDATE table_name

SET column1=val1, column2= val2

WHERE column3 = val3

Example:

UPDATE employee

SET salary=1200

WHERE id=1

Notes:

•If we want to update more than one attribute we separate each one except the last with a comma.

•If you want to modify an attribute, and assign the same value to all records, then omit WHERE from your query.

DELETE:

Sometimes, you’ll notice that some records are not needed in your table, for example, an employee resigned from the company. Then there is no need to keep their information in your db.

Syntax:

•DELETE FROM table_name WHERE column=val

Example:

DELETE FROM employee

WHERE id=1

•This query will delete the employee whose id is 1.

•If we omit WHERE from the DELETE query, then all records in the table will be deleted

•When executing DELETE query AUTO_INCREMENT field will not be decremented.

End Of Lesson 4, To download this lesson as PDF click here:  PHP Tutorial–Lesson 4: Introduction to PHP- Part IV

Best Wishes,

Anas Jaghoub

PHP Tutorial–Lesson 4: Introduction to PHP- Part III

•Today, I’ll put you in a project, and try to analyze it step by step Smile 

•Suppose, a customer has a company, and asks you for a method for storing his employee information, and accessing it when needed.

•You should put in mind, that your customer in best case has minimum knowledge about databases, and all what he need is what, not how.

•Its your turn to ask the customer, about nature of data he wants to collect.

•Let’s say the customer told you he wants to store employee name, social number, department, job title, salary, cv, telephone, address.

•These little words from your customer, helps you imagine how your table structure will look.

Analysis Step:

•Analysis step simply means determining proper data types for data you are going to collect.

•Let’s start:

•Employee name: name its proper data type is string, it is possible to make its type text or longtext, but we want to design quick, suitable, and with the less cost that meets requirements for our table.

•Social number: social number consists of number of bits, so int is suitable, we might thought about double here, but remember that double and float are used when your number consists of decimal numbers. So best choice here is int.

•Department: might be int or string! Because, some companies label their departments by numbers for example: department 1, department 2… etc. and some companies might label their departments by name, for example: Computer department, Human Resource department… etc.

•Job title: string, possible values: administrator, manager, head-master, consultant… etc.

•Salary: double.

•CV: text, we might thought about string, but in fact, string is not enough, because cv’s sometimes are big, and might contain from more than one page, so string does not meet your customer requirement, longtext is true, but is too big. We might not use all the storage offered by longtext.

•Telephone: int, but I prefer string, because you might separate post-code from number by dash – .

•Address: string.

•What else?

•Your customer told you all information he want to collect about his employees. But there still another thing, will help you in your application, and facilitate your job, which is, give each employee an id.

•This id must has a properties of uniqueness, numerical and increments.

•Why?

•Uniqueness: this id should be unique, to differ each employee from another. if you work in a company that has more than 100 thousands of employees or customers, you will notice that there might be high similarity in some names. so you can’t differ one from another. But when you deal with numbers no way to find two numbers identical.

•To make id unique, its type should be int and has a property of incrementing.

Design Step:

Once we had analyzed our customer inquiry. Then it is the time to design our table, or in other words, create our table.

CREATE TABLE employee

(

id int  AUTO_INCREMENT,

PRIMARY KEY(id),

name varchar(150) NOT NULL,

socialNo int NOT NULL,

department varchar(100) NOT NULL,

jobTitle varchar(50) NOT NULL,’

salary double NOT NULL,

cv text,

telephone varchar(25) NOT NULL,

address varchar(200)

)

Notes:

•CREATE TABLE: a query known in MySql db, its function is creating a table, in our example it creates a table called employee.

•Table attributes (columns) enclosed within parenthesis () and each attribute except last is followed by a comma.

•NOT NULL: means that this info must be filled with suitable data. We use it where an attribute must has a value. If it does not matter whether this field has value or not, we don’t write NOT NULL.

•AUTO_INCREMENT: means that this is a counter and increments on every insertion to the table by one.

•PRIMARY KEY: means this field value should not be repeated in any row. ( unique)

•Each table has only one primary key. And this field cannot be null.

•varchar: tells the db that this field value is character, and maximum size of character is determined by integer number. varchar(25) means this field is a string that at most its length is 25 character.

•Cv, and address fields might be null.

Previous Query will generate an empty table looks like this one.

image

To download this lesson click here: PHP Tutorial–Lesson 4: Introduction to PHP- Part III

Best Wishes,

Anas Jaghoub