PHP and MySQL
You are going to use PHP and MySQL for developing this e-commerce site. The question is why PHP and MySQL? The answer is very simple. The PHP and MySQL combination makes the web development task quite easy. A few of their features are listed here:
- Both are open source technologies and are available under the GPL (General Public License). Consequently the cost of developing web applications in this combination is quite low.
- PHP is a powerful language and connects with MySQL server quite easily, making the combination popular for web development.
- The combination can be successfully used under UNIX as well as Windows platforms.
- Performance of the combination is quite high. The code written in PHP for inserting and fetching information from MySQL is very efficient.
- PHP and MySQL are widely supported by the developer community, so you get regular updates.
In this chapter, you will learn about the following:
- Writing your first PHP script
- Using variables in PHP, creating an echo statement, and concatenating strings
- Using HTTP methods to transfer data—GET and POST
- Passing information from one script to another using $_GET, $_POST and $_REQUEST arrays
- Creating a sign-in form
- Applying validation checks
- Writing code for connecting PHP with MySQL
- Executing SQL commands through PHP, storing information in the database table, and accessing information from the database
- Implementing authentication
A PHP file normally contains HTML tags and some PHP scripting code embedded in it. The simplest PHP script that displays PHP’s configuration is shown in Listing 2-1. Before you learn the procedure to run this script, you should have a quick idea about the phpinfo() function that is used in this script.
The phpinfo() function displays information about PHP’s configuration. It displays information that includes the following:
- PHP compilation options
- PHP version
- Server information and environment
- PHP environment
- Different values of configuration options
- PHP license
To run this PHP script, follow these steps:
- Type this script using any editor and save it using the phpdetails.php name in the www subfolder of the wamp directory. For example, if WampServer is installed on the C: drive, save this script in the C:wampwww folder.
- Make sure that WampServer is running, i.e., its icon in the task bar is green. If it’s not, click on its icon and select Start All Services from the menu that pops up.
Once WampServer starts running, open the browser and point at the following address: http://localhost/phpdetails.php. You will get the output as shown in Figure 2-1.
Figure 2-1. PHP script displaying PHP configuration information
Next, a very basic example that displays two lines of text in the output is shown in Listing 2-2.
Again, save this script as phpscript1.php in the www subfolder of the wamp directory. Make sure that WampServer is running, and then open the browser and point at the following address: http://localhost/phpscript1.php. You will get the output shown in Figure 2-2.
Figure 2-2. PHP script displaying the welcome message
In the code shown in Listing 2-1, you can see that a PHP script can be easily embedded with HTML using the opening PHP tag, <?php and the closing PHP tag, ?>.
On finding a PHP script, the web server invokes the PHP engine and passes the script to it. The PHP engine interprets the statements enclosed between the <?php and ?> tags, generates the corresponding HTML code, and passes it back to the web server. The web server sends the HTML document to the client’s browser for display.
A PHP scripting block can be placed anywhere in the document and each statement must end with a semicolon. The semicolon is a separator and is used to distinguish one statement from another.
For storing values and text, you need variables. So, next we’ll discuss variables.
Variables may be used to store the data entered by the user or to store constant numerical values or text. The variable’s value is assigned with the help of the assignment operator (=). All variables in PHP start with a dollar ($) sign symbol. The script shown in Listing 2-3, phpscript2.php, demonstrates how variables are defined and used in PHP.
Output
Welcome John
Sum of 10 and 20 is 30
The most commonly used statement in PHP scripts is echo. It’s covered next.
The echo statement is used for displaying the output on the client’s browser at the current location in the HTML code. The output can be displayed with single quotes, double quotes, or no quotes:
- Single quotes—To display message without any variable or arrays. Example:
echo 'Welcome to our store'; - No quotes—To display value/text assigned to a variable, you don’t need to use quotes. For example, the following lines display text assigned to the variable msg.
$msg = 'Welcome to our store';
echo $msg; - Double quotes—To display value/text assigned to a variable within a string. Example:
$msg = 'Welcome to our store';
echo "Hello! $msg";
To concatenate two or more string variables together, use the dot (.) operator. The script in Listing 2-4, phpscript3.php, shows how two strings are concatenated.
Output:
Hello John! Welcome to our store
In this script, you can see that the first string, "Hello John!", is concatenated to another string, "Welcome to our store" by making use of the dot operator (.) in between.
Note In PHP, you use // to make a single-line comment. For comments extending more than a line, enclose them between a pair of /* and */ symbols.
HTTP Methods for Transferring Data
While developing applications, you might come across a situation where you want the data entered by the users on one web form to be supplied to another for further processing or action. The information from one web form to another is usually passed by two HTTP request methods called GET and POST.
This is the default method of passing data and is considered to be less secure, as it is displayed in the browser’s address bar. When you see something like this in the browser’s address bar:
display.php?name=john&[email protected]
It means the data is being passed using the GET method to the display.php script. The data that is being passed has two variables—name and email_add. Data passed through the GET method is visible to everyone and is also stored in the browser’s history/logs, making it less secure. So, the GET method is typically used to pass unimportant data.
The GET method supports only ASCII characters, hence you cannot pass binary information using this method. Moreover, there is a limit on the amount of information passed through this method. It can be a maximum of 2KB. Some servers handle up to 64KB.
When the HTTP GET method is used, data of the previous form is stored in an array called $_GET array. The data is passed in the form of pairs, variable name(s), and values.
In this method, the information passed is more secure as it is not displayed in the browser’s address bar. Here are a few of the POST method’s features:
- Data is passed directly over the socket connection using secure HTTP protocol, hence data is secure.
- POST method variables are not displayed in the URL. Also, the POST requests do not remain in the browser history.
- No restriction on sending data size.
- Even binary data or ASCII information can be sent.
- When the POST method is used, the data of the current form is collected in the $_POST array.
Passing Information from One Script to Another
To understand the concept of passing of data through the GET and POST methods, you’ll make a form that asks the user to enter their name and e-mail address, as shown in Figure 2-3.
Figure 2-3. Form prompting for name and e-mail address
To create such a form, create a PHP script called userinfo.php with the code shown in Listing 2-5.
This form has two input boxes named name and email_add. The form action points to a PHP file, display.php. The HTTP method used for passing data (name and email_add) is GET.
To pass information between the scripts, three arrays that act as a carrier of data are $_GET, $_POST, and $_REQUEST. You’ll learn how these arrays are used to transmit data one by one.
Using $_GET Array
The $_GET array is where data from the previous form sent using the HTTP GET method is stored. The data from the previous form is sent in the form of pairs: variable name(s) and value(s).
Refer to the form shown in Listing 2-2. When the user clicks the Submit button in it, the URL in the browser’s address bar will appear as shown:
http://localhost/display.php?name=john&[email protected]
You can see that the URL displays all the information that is being passed. The destination PHP script, display.php, can now extract the data from the $_GET array through the code, as shown in Listing 2-6.
This code accesses the name and e-mail address passed by userinfo.php through the $_GET array and displays them on the screen, as shown in Figure 2-4.
Figure 2-4. Name and e-mail address of the user displayed on another form
Using $_POST Array
The $_POST array collects the values sent from a form using the HTTP POST method. To pass data using the POST method, you only need to replace GET in the form’s method attribute with POST in the userinfo.php script shown in Listing 2-2.
As discussed, in the POST method, the $_POST array collects the values from the form. It also means that when the users click the Submit button, the $_POST["name"] and $_POST["email_add"] variables will be automatically filled with the data they entered in the two boxes.
To display the name and e-mail address in the destination PHP script, display.php, you need to replace the $_GET array with the $_POST array, as shown in Listing 2-7.
Besides $_GET and $_POST, there is one more array that is used for storing information about the current form; it’s called $_REQUEST.
Using the $_REQUEST Array
The $_REQUEST array contains the content of $_GET and $_POST. That is, it is used to collect the information from a form that’s sending data by the GET or POST method.
So, in case you don’t know which HTTP method was used by the source PHP script, it is wise to access the information using the $_REQUEST array. To display the name and e-mail address via the $_REQUEST array in the display.php script, you replace $_POST (in Listing 2-7) with $_REQUEST, as shown in Listing 2-8.
Now you know how forms are created and through which HTTP methods. Information from one form can also be transferred to another. Next, you’ll use the knowledge you’ve gained so far to create a sign-up form that enables users to register on your site.
Creating the Sign-Up Form
A sign-up form enables users to register on your site. A sign-up form usually prompts users to enter an e-mail address, password, complete name, address, cell phone number, etc. This information is then stored in a database for future use.
Once their data is stored in a database, users don’t have to re-enter it. It will be automatically fetched upon a successful login. The PHP script, signup.php, is shown in Listing 2-9.
The output of this PHP script is shown in Figure 2-5. You can see that several text boxes are displayed so the users can enter their e-mail addresses, passwords, complete names, address, city, state, country, Zip code, and phone number. The data entered in the respective text boxes is passed to the addcustomer.php script for storing the information in the table. The form is submitted by using the HTTP POST request method. Recall that $_POST is an array that stores the variable names and values sent by the HTTP POST method. It also means that in the addcustomer.php script, the information about the new user will be retrieved via $_POST array.
Figure 2-5. Sign-up form for creating new account
This PHP script seems perfectly okay if the user supplies essential information like an e-mail address, password, etc. correctly. What if the user leaves some of the essential boxes blank?
The previous PHP script does not apply validation checks. Next, let’s learn how to apply validation checks to the sign-up form.
For providing correct input to your application, data validation is a must. Data validation is the process of ensuring that data entered into a web form is correct and in the desired format. Data validation includes checking whether:
- Data is entered in the required fields. No essential field is left blank.
- No mistake is made when entering data. For example, no text is entered into a numerical field and vice versa.
- Data is entered in the desired format. For example, a date is entered in the required format.
You will be using JavaScript to apply validation checks to the sign-up form. The PHP script, validatesignup.php, is shown in Listing 2-10.
The first statement to mention imports the JavaScript file, checkform.js, into the current web page:
<script language="JavaScript" type="text/JavaScript" src="checkform.js"></script>
JAVASCRIPT
Because JavaScript is used in this chapter, you need a quick introduction to it.
JavaScript is a programming language that is used for extending a web site’s functionality by allowing for dynamic pages and implementing validation checks. A few of JavaScript’s features are:
- It’s a lightweight, interpreted programming language.
- It usually executes on the client machine, hence it consumes less server resources and avoids excessive server traffic.
- It’s quite fast in delivering responses. Because it processes and executes on the client's machine, it delivers the response faster than other server-side scripting languages.
- It is relatively easy to learn because its syntax is close to English.
The JavaScript file, checkform.js, contains the code to validate different fields in the validatesignup.php file.
There are two ways to include JavaScript in a web page:
- Place the JavaScript in the <head> element.
- Place the JavaScript in a separate file, save it with the extension .js, and then use the <script> element to include the code file. (By including the JavaScript file, its code will be merged in the HTML at that location.) This approach is preferred, as it keeps HTML code clean and all the JavaScript code in one place
onsubmit="return validate(this);" invokes the validate() function found in the JavaScript file and carries this (the current form as an argument) so that all of its fields can be validated in the validate function. Also, the form will be submitted and will navigate to the addcustomer.php script only if the validate function returns true. If the function returns false (if any of the fields fail in validation), form submission will not take place. Instead, an error will be displayed and the user will be prompted to validate the field.
<span id="emailmsg"></span> defines a location with an ID and an emailmsg that will be used to display error messages if the user enters the wrong e-mail address in the e-mail address box. Similarly, the locations are defined with IDs passwdmsg, repasswdmsg, and usrmsg for the consecutive boxes to display error messages if the password, re-type password, and complete name boxes do not validate.
The JavaScript file, checkform.js, applies validation checks on the sign-up form, validatesignup.php. It’s shown in Listing 2-11.
When the Submit button is clicked, the validate() method is invoked. It checks whether the data is entered correctly in the respective text boxes. The document.getElementById() method is used for searching a web form for an object with the specified ID. The object placed anywhere on the form with the given ID is searched by this method. Statement #1 searches an element on the web form with an ID of emailmsg and assigns it to the object called div (it can be any name). Statement #2 sets the content that will be displayed at the location designated by the emailmsg ID to be red.
The hasChildNodes() method in statement #3 checks if a message has already been displayed at the emailmsg ID location. If an error message has already been displayed, it is removed via the removeChild() method in statement #4. The regular expression in statement #5 checks for a valid e-mail address. If the user enters an invalid e-mail address, the appendChild() method is used in statement #6 to display the error message, "Invalid Email" at the emailmsg ID location, as shown in Figure 2-6. The appendChild() method is for attaching the given node to the document. Remember, a node never appears in the browser window until and unless it is attached to the document using the appendChild() method. The child node can be attached to any element.
Figure 2-6. Invalid e-mail error message appears upon entering an invalid email address
Because an invalid e-mail address has been entered, the user is asked to re-enter it by making the cursor stand at the e-mail address box via the focus() method applied on it through statement #7. Statement #8 returns false so that the form cannot be submitted. The form can be successfully submitted only when the validate() method returns true and that is possible only when data is entered correctly in all the desired fields.
Statement #9 ensures that the length of the password entered is not less than five. Statement #10 ensures that the passwords entered in the Password and ReType Password text boxes are exactly the same. If these passwords don’t match, the "The two passwords don’t match" error message is displayed at the location that is represented by the repasswdmsg ID (see Figure 2-7).
Figure 2-7. The two password don’t match error message appears if the two passwords don’t match
Statement #11 ensures that the user does not leave the complete name text box blank. If any of the validation checks fail, the validate() method returns false. If the desired text boxes pass through different validation checks successfully, the validation method returns true, consequently the form is submitted and the data entered is transferred to the addcustomer.php script for saving into the database table.
In order to save data into the MySQL server’s database table through PHP, you need to understand how the connection is established between PHP and MySQL. You learn how that is done next.
Code for Connecting PHP with MySQL
To connect with a MySQL server, you need to execute the mysqli_connect() method with a valid username and password. The syntax for establishing a connection is:
$variable = mysqli_connect("localhost", $user, $password, $database) or die ("Error Message.");
Note PHP and MySQL version 5 support is no longer bundled with the standard PHP distribution, hence you need to explicitly configure PHP to take advantage of this extension.
In the previous syntax, localhost signifies that MySQL server is installed on the local machine but this string is replaced by the IP address of the server or server name in case you are connecting to a remote server. The $user and $password contain the valid user ID and password supplied by the administrator. The variable $database represents the database that you want to connect to and execute the SQL statements on it for inserting or fetching the desired information. The keyword die is for printing error messages if any of the information is wrong. The following example connects the root user to the shopping database:
$connect = mysqli_connect("localhost", "root", "gold", "shopping") or die ("Please, check the server connection.");
This statement, if successful, returns an object that represents the connection to a MySQL server and the specified database.
Executing SQL Commands Through PHP
After establishing the connection with the database, the next task is to execute the required SQL statement on it. For executing required SQL statements on the database, the mysqli_query method is used with the given syntax:
$result = mysqli_query($connect, $sql) or die(mysql_error());
The $connect variable represents the connection with the MySQL server and $sql represents the SQL statement that you want to execute on the connected database. The $result variable will store the result of the execution of the SQL statement.
The PHP script shown in Listing 2-12 checks whether the connection with the MySQL server has been established.
In the previous code, the connection to the MySQL server is established and the shopping database is selected. Upon successful connection, you get the message shown in Figure 2-8.
Figure 2-8. Message confirms successful connection with the MySQL server and opens the shopping database
Storing Information in the Database Table
The PHP script for storing a new user’s information in the underlying database table is shown in Listing 2-13.
This PHP script saves the information entered by the user in the web form that was displayed through the validatesignup.php script (refer to Listing 2-10) into the customers table of the shopping database. Recall in Chapter 1 that you created the shopping database and the different tables that will be required for this e-commerce site
You can see that first of all, the connection to MySQL server is established and the shopping database is selected. The information of the user-entered invalidatesignup.php script is assigned to the $_POST array. The information in the $_POST array is retrieved and stored in different variables. Thereafter, a SQL statement to insert a record in the customers table is executed and the users are informed about their successful account creation, as shown in Figure 2-9.
Figure 2-9. Message confirming successful user account creation
Accessing Information from the Database
Information that is stored in the database is meant for future use. It means you can access information from the database whenever required. To access information from the database, the following four methods are used:
- mysqli_num_rows()—Returns the count of rows in a given recordset.
- mysqli_affected_rows()—Returns the count of rows affected by the specified SQL command.
- mysqli_fetch_array()—Returns one row at a time from the given recordset.
- extract()—Extracts the columns or fields in the specified row.
Let’s discuss these methods in detail.
mysqli_num_rows()
The mysqli_num_rows() method returns the count of rows that exists in the specified recordset. The syntax for using this method is as follows:
int mysqli_num_rows(recordset)
Where recordset represents the records or rows that are retrieved upon execution of the SQL SELECT statement through the mysqli_query() method.
mysqli_affected_rows()
The mysql_affected_rows() method returns the count of the rows that are affected by a DELETE, INSERT, REPLACE, or UPDATE statement executed in the specified SQL query. This method is used immediately after an SQL statement is executed through the mysqli_query() method. The syntax for using this method is as follows:
int mysqli_affected_rows()
mysqli_fetch_array()
The mysqli_fetch_array() function fetches one row at a time from the specified recordset or array of rows. It gets one row from the given recordset and returns true. Each row is returned either as an associative array or a numeric array. The function returns false when there are no more rows left in the recordset. The syntax for using this method is:
row=mysqli_fetch_array(recordset,array_type)
Where the recordset represents the rows that are returned upon executing the mysqli_query() function.
The array_type parameter is optional and it represents the array format in which the fetched row needs to be returned. Available options for this parameter are:
- MYSQL_ASSOC—Returns a row in associative array format.
- MYSQL_NUM—Returns a row in numeric array format.
- MYSQL_BOTH—The default. Returns a row that can be used as both an associative as well as a numeric array. That is, the array returned has both associative and number indices.
After a row is retrieved, the mysqli_fetch_array() function automatically moves to the next row in the recordset. Each subsequent call to this function returns the next row in the specified recordset. For example, the following statement fetches one row from the specified $result i.e. recordset and returns the row in associative array format:
$row = mysqli_fetch_array($result, MYSQLI_ASSOC)
extract()
The extract() function extracts all the variables or columns stored in the specified array or row. The syntax for using this method is as follows:
extract(array/row)
For example, this extracts all the columns in the specified row:
extract($row);
Let’s now look at how to apply these methods to authenticate a user.
Implementing Authentication
Authenticating a user means determining whether the visitor is already registered on the e-commerce site or not. Applying authentication is a two-step process:
- You have already learned to display and execute a script that enables visitors to sign up and create an account on your site. To verify that that the visitor is already registered, they will be provided with a sign-in form that will prompt them to enter a valid e-mail address and password.
- After entering an e-mail address and password, when the user clicks the Submit button in the sign-in form, they are taken to another script that accesses the customers table and confirms if any customer (row) exists with the supplied e-mail address and password. If a customer exists with the specified e-mail address and password, it means the visitor is already registered to your site and a welcome message will be displayed on the screen. If no row exists in the customers table with the supplied e-mail address and password, it means either the visitor is not registered to your site or has entered the wrong information. Hence, the visitor is provided two links to choose from—one will navigate them to create a new account and the other will allow them to try to sign in again.
The PHP script called signin.php is shown in Listing 2-14. It performs the first step of implementing authentication—displaying the sign-in form.
The script displays two text boxes to the visitor, one for entering an e-mail address and other for entering a password (see Figure 2-10). After the user enters an e-mail address and password and clicks Submit, the information entered in the form will be assigned to the $_POST array and sent to the validateuser.php script to check if any user exists in the customers table with the supplied e-mail address and password.
Figure 2-10. Sign-in form prompting the user to enter a valid e-mail address and password
The PHP script called validateuser.php is shown in Listing 2-15. It performs the second step of authentication—it verifies whether the information entered by the visitor is valid.
As expected, a connection to MySQL server is established and the shopping database is selected. A SQL statement is written to search in the customers table. The SQL statement checks if there is any row in the customers table whose e-mail address and password matches the e-mail address and passwords in the $_POST array. Recall that the e-mail address and password entered in the form displayed through the signin.php script are assigned to the $_POST array and navigation to the validateuser.php.
If a customer exists in the customers table that matches the supplied e-mail address and password, a welcome message is displayed to the user (see Figure 2-11—bottom).
If no row exists in the customers table (that matches the visitor’s e-mail address and password), it is assumed that either the visitor is not yet registered or they entered an invalid e-mail address or password. Consequently, two links are displayed to the visitor to choose from—one to create a new account (validatesignup.php) and another to try to sign in again (signin.php) (see Figure 2-11—top).
Figure 2-11. Message that appears upon entering an incorrect e-mail address or password (top) and the welcome message displayed upon entering a correct e-mail address and password (bottom)
Summary
In this chapter, you learned how to write and run your first PHP script. You also saw how information is passed from one script to another. You learned to get information from the user by creating a sign-up form. To store information about the new customer, you learned about the methods that are required in establishing connections between PHP and a MySQL server.
You learned about creating and executing scripts for storing user information in the customers table. Finally, you learned about the methods required to access information from the database and used that knowledge to authenticate a user (by creating a sign-in script).
In the next chapter, you will learn how to access the products table and display a list of products in it. Also, you will learn to display images of the products. You will learn to implement a search box in the e-commerce site to enable visitors to search the desired products quickly, to remember what visitors like, and finally, you will learn about session handling too.