PHP MySQL Database Application Development

This assignment focuses on developing a PHP/MySQL web application that includes features like user registration, login, profile management, and updating profile pictures. It involves writing SQL queries to interact with the database, handling user input securely, and implementing dynamic webpage content. The document provides detailed explanations of each aspect of the project, including code snippets for various functionalities.

Running head: PHP AND MYSQL
PHP and MySQL Based Library Website
Name of the Student
Name of the University

1PHP AND MYSQL
Table of Contents
1. Task 1:.........................................................................................................................................2
2. Task 2:.........................................................................................................................................4
3. Task 3:.........................................................................................................................................9
4. Task 4:.......................................................................................................................................13
5. Task 5:.......................................................................................................................................15
6. Task 6:.......................................................................................................................................15
7. Task 7:.......................................................................................................................................16
8. Task 8:.......................................................................................................................................17
Bibliography:.................................................................................................................................18

2PHP AND MYSQL
1. Task 1:
Figure 1: Entity Relationship Diagram
(Source: Created by Author)
Author Table:
Attributes Data Types Key
AID Varchar (40) Primary
Name Varchar (40)
Address Varchar (40)
Contact_Number Int (20)
Book Table:
Attributes Data Types Key
BID Varchar (40) Primary

3PHP AND MYSQL
AID Varchar (40) Foreign (Reference to Author
Table)
Name Varchar (40)
Publisher Varchar (40)
Year_Of_Publish Date
Borrow Table:
Attributes Data Types Key
BorrowID Varchar (40) Primary
BID Varchar (40) Foreign (Reference to Book
Table)
AID Varchar (40) Foreign (Reference to Author
Table)
SID Varchar (40) Foreign (Reference to Staff
Table)
Borrow_Date Date
Return_Date Date
Login Table:
Attributes Data Types Key
MID Varchar (40) Primary
UserID Varchar (40)

4PHP AND MYSQL
Password Varchar (40)
Member Table:
Attributes Data Types Key
MID Varchar (40) Primary
Name Varchar (40)
Age Int (11)
Address Varchar (40)
Contact_Number Int (20)
Email Varchar (40)
image Varchar (40)
Staff Table:
Attributes Data Types Key
SID Varchar (40) Primary
Name Varchar (40)
Position Varchar (40)
Contact_Number Int (20)
2. Task 2:
"select * from login where Password='$password' AND UserID='$username'"

5PHP AND MYSQL
"SELECT Name FROM member inner join login ON member.MID = login.MID where
UserID='$id'"
Before Login:
After Login:

6PHP AND MYSQL
"select image from member inner join login ON member.MID = login.MID where UserID='$id'"
"SELECT * FROM member inner join login ON member.MID = login.MID where
UserID='$id'"

7PHP AND MYSQL
"insert into member (MID, Name, Age, Address, Contact_Number, Email) values
('$count','$name', '$age', '$address', '$contact', '$email') "
"insert into login values ('$count', '$username', '$password')"

8PHP AND MYSQL
"UPDATE member SET Name = '$UN', Age = '$AG', Address = '$AD', Contact_Number
= '$CNT', Email = '$EML' WHERE MID = (Select m.MID from (select * from member) as m
inner join login as l on m.MID = l.MID WHERE l.UserID = '$id')"

9PHP AND MYSQL
3. Task 3:
The Age field is set as int, if charters are tried to insert into the age field then error
messge will appear.

10PHP AND MYSQL
The error message is ‘Either error in update or age is less than 18’.
The image upload section allows a user to choose the picture.
After the image will be selected the name of the original image will appear.
If the image is less than 5 MB, the image will be saved.

11PHP AND MYSQL
The registration page is for gathering the data of the user to store into the database.
As it is seen the above image, all the data has been inputted but the age is less than 18.
Then the page will be redirected to the registration page, the data will not be saved in database.

12PHP AND MYSQL
If the age is greater than 17 that means 18 or more the user will be registered and the
login page will be redirected.
If the user is logged in with the provided user id and password alica and 12345
respectively.

13PHP AND MYSQL
4. Task 4:
$result = mysql_query("insert into member (MID, Name, Age, Address,
Contact_Number, Email) values ('$count','$name', '$age', '$address', '$contact', '$email') ",
$connection);

14PHP AND MYSQL
$result2 = mysql_query("insert into login values ('$count', '$username', '$password')",
$connection);
if (($result == true) && ($result2 == true)){
header("location: LoginPage.php");
}
else{
header("location: RegistrationPage.php");
mysql_close($connection);
}
If both the login id and password is not right then the above message will appear.

15PHP AND MYSQL
The UserID = Alica and Password = ‘12345’ is registered in the database. If logged in
using this id and password then the login will be successful. The image below proofs the
statement.
5. Task 5:
To design the database, at first logical the conceptual database was designed. Then the
conceptual design was developed into logical database design. The ERD is the final result of the
database design. The project is based on user login and profile related activities. So it was
significant to identify which pages are associated to the user profile related activity. The UI of
the page were designed by hand to identify which data is required to show in which page. Such
as the login page will show only fields of user id and password. The registration page will show
all the member and login table fields except MID and image. Then the php code for accessing,
inserting and updating data is used. The forms have been used for sending data to pages that
consist of the php codes. Such as after login button is clicked, the data inputted in login form will
be send to login named php page through post method. In this page, the inserted data will be set
to php variable and sime queries will be run to check authorization. After, login is successful the
user is redirected to home page. If the login is unsuccessful then login page will appear again.

16PHP AND MYSQL
6. Task 6:
DML: DML is abbreviation of data manipulation language. Using DML the developer
can write queries that can be used for inserting, updating and deleting data stored in database
table.
"insert into member (MID, Name, Age, Address, Contact_Number, Email) values
('$count','$name', '$age', '$address', '$contact', '$email') "
"UPDATE member SET Name = '$UN', Age = '$AG', Address = '$AD', Contact_Number
= '$CNT', Email = '$EML' WHERE MID = (Select m.MID from (select * from member) as m
inner join login as l on m.MID = l.MID WHERE l.UserID = '$id')"
QBE: Regarding database management system, QBE or query by example indicates the
technique of creating queries within which the database displays a blank record for a (each) field.
If the condition is entered then the proper result will appear.
Query: This is a set of predefined words used for fetching result or modifying database or
deleting data form database or inserting data into database.
"UPDATE member SET image = '$userpic' WHERE MID = (Select m.MID from (select
* from member) as m inner join login as l on m.MID = l.MID WHERE l.UserID = '$id')"
"select * from login where Password='$password' AND UserID='$username'"
7. Task 7:
"select * from login where Password='$password' AND UserID='$username'"
This query selects all the data from the login table on the condition that the username and
password of the row should be same as user input. Cannot be modified.

17PHP AND MYSQL
"insert into member (MID, Name, Age, Address, Contact_Number, Email) values
('$count','$name', '$age', '$address', '$contact', '$email') "
This query insert data collected from user during registration process into member table.
This query can be improved by adding image at the time of registration.
"SELECT Name FROM member inner join login ON member.MID = login.MID where
UserID='$id'"
This query selects name of the user who is logged in. The name will appear at the right
corner of the menubar. Cannot be modified.
"UPDATE member SET Name = '$UN', Age = '$AG', Address = '$AD', Contact_Number
= '$CNT', Email = '$EML' WHERE MID = (Select m.MID from (select * from member) as m
inner join login as l on m.MID = l.MID WHERE l.UserID = '$id')"
This query is used for updating user data stored in the database. This query is also
consisted of sub query. This query can be improved only by storing the username and password
of user into member table.
8. Task 8:
The user will first access the homepage of the website. In that page, at the right side
corner the login hyperlink can be seen. The user will click on that icon and redirected to
LoginPage. Then in that page below login button ‘register’ hyperlink will be there. After the link
is clicked, the user will be again redirected but this time to ‘RegistrationPage’. The user will
enter all the required details. The age must be equal or greater than 18. After the user is
registered, the user will be redirected to login page again. This time the user will input UserID

18PHP AND MYSQL
and password provided during registration and submit login button. If the user is logged in, then
at the home page, instead of login link the name of the user will appear. If the user clicks on that
link then will be redirected to profile page. The user can logout from the website using logout
link and update profile pic using update button. The user will click on ‘choose file’ and select the
image. After clicking on save button the profile page will appear on the profile screen.