CRUD IMPLEMENTATION USING PHP

AND MySQL

SERVER SIDE INTERNET PROGRAM

ASSIGNMENT

Name : Hafiz Febrinaldo Rafli

Student ID : 030202400013

Group : Individual

Study Program : Informatic

Practicum Date : June 27th, 2025

Lecturer : Sir. Arif Hardi

FACULTY OF COMPUTER

SCIENCE PRESIDENT UNIVERSITY

PEKANBARU

2025
 Table of Contents
EXECUTIVE SUMMARY.........................................................................................................................3
CHAPTER I.................................................................................................................................................4
EXERCISE DISCUSSION.........................................................................................................................4
A. Prepare a SQL query................................................................................................................................
1. Db.php.......................................................................................................................................4
2. index.php...................................................................................................................................4
3. add.php......................................................................................................................................5
4. edit.php......................................................................................................................................7
5. delete.php...................................................................................................................................8
B. Create a simple CRUD application using PHP and the Bootstrap framework..................................
CHAPTER II................................................................................................................................................9
EXPLANATION AND HOW IT WORKS...............................................................................................9
1. Database Connection (db.php)................................................................................................9
2. isplaying Data (index.php).......................................................................................................9
3. Adding New Data (add.php)....................................................................................................9
4. Editing Data (edit.php).............................................................................................................9
5. Deleting Data (delete.php).......................................................................................................9
6. Table Structure (citydb)...........................................................................................................9
7. How to Run the Application..................................................................................................10
8. Results and output..................................................................................................................10
CHAPTER IIICONCLUSION.................................................................................................................11

2
 EXECUTIVE SUMMARY

This assignment focuses on the application of basic database concepts and

web programming, starting with the creation of a database table named CITY
using SQL. The table consists of five main columns: ID, NAME,
COUNTRYCODE, DISTRICT, and POPULATION. Students are required to
write SQL queries to create the table, insert new data, retrieve data based on
specific criteria, and perform update and delete operations. This section aims to
develop a solid understanding of data manipulation using SQL within a database
management system such as MySQL.
The second part of the task challenges students to build a CRUD (Create,
Read, Update, Delete) application using PHP and the Bootstrap framework. This
web application connects directly to the citydb database and enables users to
manage city data through a responsive web interface. By developing files such
as index.php, add.php, edit.php, and delete.php, students practice full-stack web
development with database integration. The assignment not only strengthens
technical skills in backend programming and SQL but also enhances the ability
to design user-friendly interfaces.

3
 CHAPTER I
EXERCISE DISCUSSION
A. Prepare a SQL query
1. Db.php
This PHP script is used to establish a connection between the application
and a MySQL database. The variables $host, $user, $password, and $db store
the database connection details such as the server address, username, password,
and database name (citydb). The mysqli object attempts to connect using these
credentials. If the connection fails, the script will stop and display an error
message: “Connection failed,” along with the specific error information.

<?php
$host = "localhost";
$user = "root";
$password = "";
$db = "citydb";

$conn = new mysqli($host, $user, $password, $db);

if ($conn->connect_error) {
die("Koneksi gagal: " . $conn->connect_error);
}
?>

2. index.php

This code represents the main page of a CRUD application that displays
data from the CITY table in an HTML table format. At the beginning, the
db.php file is included to establish a database connection. A SELECT * FROM
CITY query is executed to retrieve all city records. The results are displayed
using a Bootstrap-styled table, with action buttons provided for editing or
deleting each entry.

<?php
include 'db.php';
$data = $conn->query("SELECT * FROM CITY");
?>

<!DOCTYPE html>
<html>
<head>
<title>City Data</title>
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstr
ap.min.css" rel="stylesheet">
</head>
<body class="container mt-4">
4
 <h2>City List</h2>
<a href="add.php" class="btn btn-success mb-3">+ Add City</a>

<table class="table table-bordered">

<thead>
<tr>
<th>ID</th>
<th>City</th>
<th>Country Code</th>
<th>District</th>
<th>Population</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php while($row = $data->fetch_assoc()): ?>
<tr>
<td><?= $row['ID'] ?></td>
<td><?= $row['NAME'] ?></td>
<td><?= $row['COUNTRYCODE'] ?></td>
<td><?= $row['DISTRICT'] ?></td>
<td><?= $row['POPULATION'] ?></td>
<td>
<a href="edit.php?id=<?= $row['ID'] ?>"
class="btn btn-warning btn-sm">Edit</a>
<a href="delete.php?id=<?= $row['ID'] ?>"
class="btn btn-danger btn-sm" onclick="return confirm('Are you
sure?')">Delete</a>
</td>
</tr>
<?php endwhile ?>
</tbody>
</table>

</body>
</html>

3. add.php

This code handles the "Add City" function in a web-based CRUD

application. At the beginning, it includes the db.php file to establish a
connection with the database. When the form is submitted using the POST
method, the script collects input data (ID, city name, country code, district, and
population) and inserts it into the CITY table using an SQL INSERT query.
After successful insertion, the page redirects the user back to the main data list
(index.php). The HTML section displays a Bootstrap-styled form that allows
users to input new city records in a structured and user-friendly way.

5
<?php
include 'db.php';

if ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = $_POST['id'];
$name = $_POST['name'];
$code = $_POST['code'];
$district = $_POST['district'];
$population = $_POST['population'];

$conn->query("INSERT INTO CITY (ID, NAME, COUNTRYCODE,

DISTRICT, POPULATION)
VALUES ('$id', '$name', '$code', '$district',
'$population')");
header("Location: index.php");
}
?>

<!DOCTYPE html>
<html>
<head>
<title>Add City</title>
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstr
ap.min.css" rel="stylesheet">
</head>
<body class="container mt-4">
<h2>Add New City</h2>
<form method="post">
<input class="form-control mb-2" type="text" name="id"
placeholder="ID" required>
<input class="form-control mb-2" type="text" name="name"
placeholder="City Name" required>
<input class="form-control mb-2" type="text" name="code"
placeholder="Country Code (e.g., IDN)" required>
<input class="form-control mb-2" type="text"
name="district" placeholder="District" required>
<input class="form-control mb-2" type="number"
name="population" placeholder="Population" required>
<button class="btn btn-success" type="submit">Save</button>
<a href="index.php" class="btn btn-secondary">Back</a>
</form>
</body>
</html>

6
 4. edit.php

This code handles the update (edit) functionality of a CRUD web

application. It starts by including the db.php file to establish a connection to the
database. The script retrieves the ID of the city record from the URL using the
GET method and then fetches the corresponding data from the CITY table. If the
form is submitted via the POST method, the input values are collected and used
to update the existing record in the database using an SQL UPDATE query.
Once the update is successful, the user is redirected back to the main page
(index.php). The HTML portion displays a form pre-filled with the existing city
data, allowing users to make changes easily.

<?php
include 'db.php';

$id = $_GET['id'];
$data = $conn->query("SELECT * FROM CITY WHERE ID = $id")-
>fetch_assoc();

if ($_SERVER["REQUEST_METHOD"] == "POST") {
$name = $_POST['name'];
$code = $_POST['code'];
$district = $_POST['district'];
$population = $_POST['population'];

$conn->query("UPDATE CITY SET NAME='$name',

COUNTRYCODE='$code', DISTRICT='$district', POPULATION='$population'
WHERE ID = $id");
header("Location: index.php");
}
?>

<!DOCTYPE html>
<html>
<head>
<title>Edit City</title>
<link
href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstr
ap.min.css" rel="stylesheet">
</head>
<body class="container mt-4">
<h2>Edit City</h2>
<form method="post">
<input class="form-control mb-2" type="text" name="name"
value="<?= $data['NAME'] ?>" required>
<input class="form-control mb-2" type="text" name="code"
value="<?= $data['COUNTRYCODE'] ?>" required>
<input class="form-control mb-2" type="text"
name="district" value="<?= $data['DISTRICT'] ?>" required>

7
 <input class="form-control mb-2" type="number"
name="population" value="<?= $data['POPULATION'] ?>" required>
<button class="btn btn-primary"
type="submit">Update</button>
<a href="index.php" class="btn btn-secondary">Cancel</a>
</form>
</body>
</html>

5. delete.php

This code performs the delete operation in a CRUD application. It begins

by including the db.php file to connect to the database. The script retrieves the
ID of the city to be deleted from the URL using the GET method and then
executes an SQL DELETE query to remove the corresponding record from the
CITY table. After the deletion, the user is redirected back to the main page
(index.php).

<?php
include 'db.php';

$id = $_GET['id'];
$conn->query("DELETE FROM CITY WHERE ID = $id");

header("Location: index.php");
?>

B. Create a simple CRUD application using PHP and the Bootstrap

framework

The CITY table is used to store city data, consisting of columns: ID as the
primary key (unique), NAME for the city name, COUNTRYCODE for the
country code, DISTRICT for the administrative region, and POPULATION for

8
 the number of inhabitants, with appropriate data types for each field.

CHAPTER II
EXPLANATION AND HOW IT WORKS

1. Database Connection (db.php)

The db.php file connects the PHP application to the MySQL database. It defines
the host (localhost), username (root), no password, and the database name (citydb).
If the connection fails, it shows an error message. This file is included in all other
files so they can communicate with the database.

2. isplaying Data (index.php)

The index.php file is the main page that shows all data from the CITY table. It
uses a table styled with Bootstrap to make it clean and responsive. At the top,
there’s an Add City button to create new records. Each row in the table includes Edit
and Delete buttons for updating or removing the data.

3. Adding New Data (add.php)

The add.php file allows users to input new city data. When the form is
submitted, it sends the input values to the database using an INSERT SQL query.
After saving the data, it redirects the user back to the main page (index.php).

4. Editing Data (edit.php)

This file is used to update an existing city entry. It first loads the current data
using the city’s ID, then displays it in a form. After the user edits the values and
clicks Update, the data is saved using an UPDATE query, and the user is redirected
to the main page.

5. Deleting Data (delete.php)

The delete.php file deletes a city record using its ID, which is passed through the
URL. It runs a DELETE query and immediately redirects back to the main page
after removing the entry. This happens when the Delete button is clicked from the
table.

6. Table Structure (citydb)

The database contains one table called CITY with five columns:
 ID (unique number),
 NAME (city name),
9
 COUNTRYCODE (3-letter country code),
 DISTRICT (region),
 POPULATION (total people).
This structure supports simple data management for city information.

7. How to Run the Application

1) Open XAMPP or similar software and start Apache and MySQL.\

2) Open phpMyAdmin from your browser:
http://localhost/phpmyadmin
3) Create a database named citydb, and run the SQL query to create the
CITY table.
4) Save all PHP files (db.php, index.php, add.php, edit.php, delete.php)
inside the htdocs folder, for example:
htdocs/crud-city
5) Open the app in your browser:
http://localhost/crud-city/index.php

8. Results and output

10
 CHAPTER IIICONCLUSION

In conclusion, this CRUD application project successfully demonstrates

how PHP, MySQL, and Bootstrap can be integrated to build a simple but
functional web-based data management system. The system allows users to
create, read, update, and delete records from the CITY table, which includes
essential city information such as name, country code, district, and population.
Through structured files like index.php, add.php, edit.php, and delete.php, each
function is clearly separated, making the code easier to understand and maintain.

This project not only enhances technical skills in server-side

programming and database operations but also improves understanding of how
web applications interact with relational databases. By implementing a real-time
connection to MySQL through PHP, users gain practical experience in handling
data dynamically. Overall, the task provides a strong foundation for building
more complex web applications in the future.

11