Sending Shopping Cart Data to Database for PHP and MySQL Web Development
Conventional Web Development with PHP and MySQL – Part 11
Forward: In this part of the series, you learn how to send the shopping cart data to the database for PHP and MySQL web development.
By: Chrysanthus Date Published: 2 Aug 2012
Introduction
Shopping Cart String
You can assemble all the data in the shopping cart into a string and send to a PHP file at the web server. This PHP file will then extract the values from the string and send the data to the database server.
For the shopping Cart the following code can be used:
<script type="text/ECMAScript">
function formPurchaseStr()
{
purchaseStr = "";
tableSize = document.getElementById("AT1").rows.length;
if (tableSize > 1)
{
for (k=1; k<tableSize; ++k)
{
purchaseStr += "_" + "quantity=" + document.getElementById("AT1").rows[k].cells[0].innerHTML + "&" + "itemName=" + document.getElementById("AT1").rows[k].cells[1].innerHTML + "&" + "price=" + document.getElementById("AT1").rows[k].cells[2].innerHTML + "&" + "total=" + document.getElementById("AT1").rows[k].cells[3].innerHTML;
}
document.getElementById('purchaseStr').value = purchaseStr;
return true;
}
else
{
alert('The Shopping Cart is empty!');
return false;
}
}
</script>
<form method="POST" action="purchase.php" onsubmit="return formPurchaseStr()">
Money Transfer Email: <input type="email" name="email">
<input type="hidden" name="purchaseStr">
<p style="text-align:center"><button type="submit">Purchase</button></p>
</form>
This code is the HTML Form of the shopping cart plus an extra ECMAScript. All of it should be in the aside element. Apart from the command,
onunload="window.clearInterval(ti)"
all the code concerning the shopping cart is in the aside element. Do not confuse between the tables for the shopping cart and the table for the items (products). The two tables for the shopping cart are in the aside element. The table for the items (4 in this project) is not in the aside element; but it is in the same page as the aside element.
Let us now see how the ECMAScript for the HTML Form operates: There is one function in the script. The function begins by initializing 2 variables. One of the variables named, purchaseStr, will hold the complete string that has all the values of the shopping cart. The other variable named, tableSize, holds the size (length) of the big table.
If the condition returns false, then the if-construct alerts the user that he has not yet selected any item for purchase (“The Shopping Cart is empty!”). If the condition returns true, then the if-block is executed. The if-block uses a for-loop to build up the purchaseStr string. The string separates variable names and values with the = sign; it separates variable name/value pairs with the & sign; it separates item data (for rows) with the underscore. Do not confuse between this format of forming a string with what happens with the HTTP GET method. For the GET method (that sends the string to the server), at the server, the $_GET array would have the name/value pairs. However, with the above string, the HTML Form sends the string to the server, as one un-separable string. The line after the for-loop above, assigns the string (value) to the hidden input text element of the shopping cart HTML Form. At the server, the $_POST array will receive the string as one value. Then PHP regular expression techniques will have to be used to split and extract data from the received string.
In this project, the email (PayPal or MoneyBookers) will also be sent to the server as a separate name/value pair. In a commercial project, the user name should also be sent, so that the server uses it to know the name and address of the buyer. If the user does not have to login before buying, then the user’s credentials (name, address, credit card info or email) will have to be typed at the shopping cart, and sent to the server.
In a commercial project, you should ask PayPal or MoneyBookers to give you their code, that you will associate with the shopping cart. With this association, money will automatically be deducted from the buyer’s PayPal or MoneyBookers account.
For the rest of this article we shall discuss what happens at the server. Everything being equal, there are two servers (software) in the Internet host computer (which is different from the client’s computer): one is the web server (e.g. Apache) and the other is the database server (e.g. MySQL). The PHP file that receives the dataset from the shopping cart HTML Form is in the web server.
One of the variable statement for the superglobal $_POST array in the PHP file, should be:
$purchaseStr = $_POST['purchaseStr'];
This string will have to be split and the item (product) values extracted. The PHP regular expression syntax to split a string is:
array preg_split(string $regex , string $subject)
Here, regex is regular expression in quotes e.g. "/ +/" . This is different from the case of the ECMAScript regular expression scheme, where the regular expression is not in quotes. This PHP function returns an array. You would have to use this, 3 times: the first time is to split the string about the underscores into an array of items (row) data. Next, you split each item data about & to have name value/pairs. And then you split each name/value pair about = to have the names and values. You would not need the names from the name/value pair splits.
So you would finally have the values from the shopping cart in arrays. The script can then go on to connect to the database server, select a database and send the data into the Sales and SaleItems tables. In this case, sending data to the database is a transaction; so you would have to lock and unlock tables.
I will not give you any code for the PHP file at the web server. If you have covered the prerequisite in the first part of this series, and if you have read all the parts of this series, then you should be able to write up the file yourself.
The PHP file can also send an email to the user to inform him of his transaction. I explained how to send such an email in one of the previous parts of the series.
Logged In
When a user is logged in, the password and username are saved in his computer for the session. These two values are available to every page of the site at the browser. They are two sessionStorage values. An HTML Form can read these values and send them to the server to connect to the database. So, the shopping cart HTML Form should read these values and send to the database server for connection to the database. In order to safe time and space I did not take this into account in the project of this series; you should be able to do that. So, once a user is logged in, HTML Forms in different pages, can enable him access the database from those different web pages.
Useful TRIGGER
We are toward the end of the series and it is good I tell you how to use a trigger to update the Products table for quantity-available when the purchase information is recorded at the database. The information here is for the Bookshop database of this series.
It is the SaleItems table that should have the trigger. For the user to use a trigger of the SaleItems table, the root user of the MySQL server has to give the user (buyer) the privilege as follows:
grant TRIGGER on Bookshop.SaleItems to "username"@"localhost";
A Trigger is associated with a database table. So the TRIGGER has to be coded to work for the SaleItems table. The trigger will be engaged on Insert; that is, when a row is inserted in the SaleItems table. The purpose of the trigger is to subtract the quantity of an item (product) inserted (sold) in the SaleItems table from the Products table on the quantity-available column. In that way, when an item is sold, the quantity available (inventory) in the Products table is updated. The user needs to also have the UPDATE privilege for the Products table. This is all I will talk about on Triggers in this series. If you have covered the prerequisite and if you have read all the parts of this series, then you should not have a problem coding triggers for web development.
Summary of Conventional Web Development
In conventional web development, you have a browser for the client in the client’s computer. You have a host computer in the Internet. This Internet computer has the web server and the database server (in theory). The web server has the website. It has the hard coded web pages. The web server has a script language, such as PHP. There are script files written in the scripting language in the web server. Each of these files is a program and carries out its own task. If the client wants something from the database, one of the script files will get it and send the result to the browser at the client’s computer. Any new web page to be formed and sent to the browser, is formed by these script files. Email is sent by a script file.
And this is the end of the series. It was a long ride, but rewarding.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course