CAN302 – Technologies for e-commerce Lab 2

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CAN302 – Technologies for e-commerce

Lab 2 – PHP and Database

Aim

The aim of this lab is to learn how to handle database through different tools.

Tips:

1. If you are not sure why you are doing something, ask a TA.  This is what they are here for.

2. The M-Dev-Store online videos are good references while our labs have different focus. If you want to be an expert, you are recommended take both labs and on-line videos.

3.  The forums @ LMO are available for questions and discussions.

4.  These labs are expected take more than the 2 allocated hours.  You should complete them in your own time before the next lab.  Practice makes perfect!

Database and MariaDB:

1.   A collection of related pieces of data, whose purpose is to solve the data management needs of an institution is called a Database. Database Management Systems (DBMS), on the other hand,  are very complex software that save the data on the secondary storage devices and which are    used to manipulate databases. SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database. Though there are    many different DBMS like Oracle, MariaDB, MySQL, SQL Server …, the SQL is same for all of         them. However, the tool used to operate different DBMS is different from each other.                  “phpMyAdmin” is a web tool that can operate MariaDB. We can use it to create database, table

and Create (insert), Read (query), Update, Delete data or CRUD.

Let’s make the 1st  demo as following:

In above, we get data from database and show them as a table in the webpage. There are two buttons: by “add”, we can add new data into database; by “search”, we can filter the result in the table by conditions.

First of all, we need to create a database and then a table to hold such data. We can use                 “phpMyAdmin” for that purpose. The data structure is as:



In above, a database named “ lab2” was created and a table named “user” was created with four columns: id, first_name, last_name and email.

To show the content on the webpage as the demo, the html part code is as:

From line62 to line72 are PHP code, the dynamic data are retrieved from database and output to the html by using “while” loop.

To interact with server, there are mainly two methods: GET and POST. GET method is used to       appends form data to the URL in name or value pair. If you use GET, the length of URL will             remain limited. It helps users to submit the bookmark the result. GET is better for the data which does not require any security or having images or word documents. POST is a method that is         supported by HTTP and depicts that a web server accepts the data included in the body of the      message. POST is often used by World Wide Web to send user generated data to the web server or when you upload file. To use POST method, we need to use the

tag from line77 to        line88. Please notice the bootstrap elements have been used. In thetag (line78), the         action will determine the target webpage. In this demo, we keep it blank. That means the form    will post to the same page.



Receive and process the GET or POST requests are basic functions of Web servers. The PHP code for this lab is as:

“mysqli” is a build-in tool to operate MySQL type database, the code from line4 to line7 is to      open a new connection to database. In which,  localhost means the MySQL server address, root and “” are the username and password for the database and “lab2” is the name of database.

To receive the posted data, we may have a safe function to avoid “null” error. Which is from

line10 to line13.

The rest logics are:

Receive all post parameters first. If user click “add” button, then insert the received data into      table “user” . If user click “search” button, then add the conditions to SQL statement, otherwise  retrieve all data from table “user” . The retrieved data will be outputted to web page by the code from line62 to line72.

Be careful, the code here is only a demo purpose. It is very weak. For example, the SQL injection can cause the database error easily.

Database and sqlite3:

2.    SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-    featured, SQL database engine. SQLite is the most used database engine in the world.  The demo here will do the same function with the sqlite3 type database or DBMS.

We need to prepare the SQLite database same as the MariaDB one. The following PHP code can make it:

Please note another build-in class “SQLite3” has been used to operate the sqlite3 type database. By execute this code (you can run the php in command line mode or configure your IDE that can run the php directly in your IDE), a file named “mysqlitedb.db” will be created in the same directory of the php file and a user table will be created.

To migrate from the MariaDB to sqlite3, we need to modify the code accordingly. The changed code is as:

and:

Now you can try to verify all functions by visiting the updated webpage. You may encounter the database read-only warning, you need to adjust the permission accordingly.


PDO :

3.    SQLite or MySQL? Each database has its advantages and disadvantages. There is no answer which database should be chosen if the requirements are unknown.

In the previous demos, we use two different tools to connect two different types database. Then we need to adjust the PHP code accordingly. The code is tightly coupled with the database type. Simply it limits the flexibly on database choices.

“PDO” is another choice. Thanks OO programming, PDO has implements the connections to different databases and hide the details. So, the same code can be used for different type    databases.

The updated PHP code is as:

and:

Switch to SQLite is very simple now. Only the DBMS information need to be changed as:

Through this lab, you can gain the basic ideas how PHP interact with database. However, due to  time limitation, the operation of database cannot be explained in details and many demo code    are very fragile.



2023-03-28

PHP and Database