please see the attached question file

Homework 9

Table of Contents

Website Navigation 1

Website Issues 2

Summary about CRUD Operations 2

Debug 26

Homework Summary 27

Future Plans 28

Website Navigation

I have added a folder called config which contains database.php. I have also added the various files that support our CRUD operations. They are create.php, read_one.php, delete.php, and update.php.

please see the attached question file 1

Website Issues

I did not find any issue while implementing the travel website. The implementation went smoothly as depicted in the next section while showing screenshots of the CRUD operations of the website.

Summary about CRUD Operations

The website allows creating, reading, updating, deleting of tourist attraction sites from the website. The following screenshot reads all the tourist attraction sites from the database. The page that reads all tourist attraction sites is called attractionsites.php.

please see the attached question file 2

You can read, edit, and delete each record by using the respective buttons. The following screenshot shows us adding a new record to the database. We will click on the “Create New Tourist Site” button as shown from the above screenshot.

The following screenshot shows us a form that will allow us to add a new record.

please see the attached question file 3

The following screenshot shows us filling the form with our new record and when we are done, we will click on “Save Site” button.

please see the attached question file 4

The following screenshot shows that our data has been saved successfully.

please see the attached question file 5

The next screenshot shows our data saved in the database.

please see the attached question file 6

The following screenshot shows us reading one record, the one that I have created.

please see the attached question file 7

The next screenshot shows us editing the record that I had created. We will change the country to Netherlands and then show if it is reflected in our database.

please see the attached question file 8

From the following screenshot we can see that our record has been updated successfully.

please see the attached question file 9

The following screenshot shows our record in the database.

please see the attached question file 10

We can also delete data by clicking the “delete” button depicted in the following screenshot.

please see the attached question file 11

After clicking the “Delete” button, we will get a message asking us whether we are you about deleting the record. The following screenshot shows this.

please see the attached question file 12

After clicking “OK” the record will be deleted from the database and also removed from the website. The following screenshot shows us the success deletion message.

please see the attached question file 13

From the above screenshot we can also see that the record that had “Netherlands” as a country has been deleted. The following screenshot shows the same from the database.

please see the attached question file 14

From the screenshot above we can see that the record that had “Netherlands” as a country and an id of 25 has been removed from the database.

I added several pages to our website to deal with CRUD operations. The first page is the create.php page. This page helps in creating a single record. It has a form that facilitates the user to create a record. The following screenshot shows the form and the code.

please see the attached question file 15

<!DOCTYPE html>

<head>

<title>Add a travel site</title>

</head>

<body>

<ul>

<li><a href="#">Colosseum</a></li>

<li><a href="grandcanal.html">Grand Canal</a></li>

<li><a href="leaningtower.html">Leaning Tower</a></li>

<li><a href="touristattraction.html">Tourist Attraction Sites</a></li>

<li><a href="attractionsites.php">Common Sites</a>

<li><a href="signup.html">Signup</a></li>

<li><a href="contactme.html">Contact me</a></li>

</ul>

</nav>

<h1>Add tourist attraction site</h1>

</div>

<?php

if($_POST){

include 'config/database.php';

try{

$query = "INSERT INTO traveltbl SET site=:site, city=:city, country=:country";

$stmt = $con->prepare($query);

// posted values

$site=htmlspecialchars(strip_tags($_POST['site']));

$city=htmlspecialchars(strip_tags($_POST['city']));

$country=htmlspecialchars(strip_tags($_POST['country']));

//binding the parameters

$stmt->bindParam(':site', $site);

$stmt->bindParam(':city', $city);

$stmt->bindParam(':country', $country);

if($stmt->execute()) {

echo "<div class='alert alert-success'>Site record was saved successfully.</div>";

}else{

echo "<div class='alert alert-danger'>Unable to save the site record.</div>";

}

}catch(PDOException $exception){

die('ERROR: ' .$exception->getMessage());

}

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">

<tr>

<td>Attraction Site</td>

</tr>

<tr>

</tr>

<tr>

<td>Country</td>

</tr>

<tr>

<td>

<a href="attractionsites.php" class="btn btn-danger">Back to All Sites</a>

</td>

</tr>

</table>

</form>

</div>

</body>

</html>

I also added an update.php page which deals with updating a record. It also has a form where the user can update the selected record. The following screenshot shows the form and the code.

please see the attached question file 16

<!DOCTYPE html>

<head>

<title>Update a record</title>

</head>

<body>

<ul>

<li><a href="index.html">Colosseum</a></li>

<li><a href="grandcanal.html">Grand Canal</a></li>

<li><a href="leaningtower.html">Leaning Tower</a></li>

<li><a href="touristattraction.html">Tourist Attraction Sites</a></li>

<li><a href="attractionsites.php">Common Sites</a>

<li><a href="signup.html">Signup</a></li>

<li><a href="contactme.html">Contact me</a></li>

</ul>

</nav>

<h1>Update a site</h1>

</div>

<?php

$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID is not found.');

include 'config/database.php';

try{

$query = "SELECT id, site, city, country FROM traveltbl WHERe id = ? LIMIT 0,1";

$stmt = $con->prepare($query);

$stmt->bindParam(1, $id);

$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

$site = $row['site'];

$city = $row['city'];

$country = $row['country'];

}catch(PDOException $exception){

die('ERROR: ' .$exception->getMessage());

}

<?php

if($_POST){

try{

$query = "UPDATE traveltbl SET site=:site, city=:city, country=:country WHERE id = :id";

$stmt = $con->prepare($query);

$site=htmlspecialchars(strip_tags($_POST['site']));

$city=htmlspecialchars(strip_tags($_POST['city']));

$country=htmlspecialchars(strip_tags($_POST['country']));

$stmt->bindParam(':site', $site);

$stmt->bindParam(':city', $city);

$stmt->bindParam(':country', $country);

$stmt->bindParam(':id', $id);

if($stmt->execute()) {

echo "<div class='alert alert-success'>Record was updated successfully.</div>";

}else{

echo "<div class='alert alert-danger'>Unable to update the record.</div>";

}

}catch(PDOException $exception){

die('ERROR: ' . $exception->getMessage());

}

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . "?id={$id}");?>" method="post">

<tr>

<td><input type="text" name='site' value="<?php echo htmlspecialchars($site, ENT_QUOTES); ?>" class='form-control'/></td>

</tr>

<tr>

<td><input type='text' name='city' value="<?php echo htmlspecialchars($city, ENT_QUOTES); ?>" class='form-control' /></td>

</tr>

<tr>

<td>Country</td>

<td><input type='text' name='country' value="<?php echo htmlspecialchars($country, ENT_QUOTES); ?>" class='form-control' /></td>

</tr>

<tr>

<td>

<a href='attractionsites.php' class='btn btn-danger'>Back to all tourist sites</a>

</td>

</tr>

</table>

</form>

</div>

</body>

I also added a read_one.php page which shows a single record that has been selected. The following screenshot shows one record selected and the code follows.

please see the attached question file 17

<!DOCTYPE html>

<head>

<title>Read One Record</title>

</head>

<body>

<ul>

<li><a href="index.html">Colosseum</a></li>

<li><a href="grandcanal.html">Grand Canal</a></li>

<li><a href="leaningtower.html">Leaning Tower</a></li>

<li><a href="touristattraction.html">Tourist Attraction Sites</a></li>

<li><a href="attractionsites.php">Common Sites</a>

<li><a href="signup.html">Signup</a></li>

<li><a href="contactme.html">Contact me</a></li>

</ul>

</nav>

<h1>Read Tourist Site</h1>

</div>

<?php

$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID nor found.');

include 'config/database.php';

try{

$query = "SELECT id, site, city, country FROM traveltbl WHERE id = ? LIMIT 0,1";

$stmt = $con->prepare($query);

$stmt->bindParam(1, $id);

$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

$site = $row['site'];

$city = $row['city'];

$country = $row['country'];

}catch(PDOException $exception){

die('ERROR: ' .$exception->getMessage());

}

<tr>

</tr>

<tr>

</tr>

<tr>

<td>Country</td>

</tr>

<tr>

<td>

<a href='attractionsites.php' class='btn btn-danger'>Back to all Attraction Sites</a>

</td>

</tr>

</table>

</div>

</body>

</html>

I also added the delete.php page which deals with the deletion of a record. The following code is for the delete.php.

<?php

include 'config/database.php';

try{

$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found');

$query = "DELETE FROM traveltbl WHERE id = ?";

$stmt = $con->prepare($query);

$stmt->bindParam(1, $id);

if($stmt->execute()) {

header('Location: attractionsites.php?action=deleted');

}else{

die('Unable to delete record');

}

}catch(PODException $exception){

die('ERROR: ' . $exception->getMessage());

}

Debug

PHP shows errors when they arise, and it also shows the type of error and where it is located. The errors were easy to manage and rectify. The following screenshot shows an error page.

please see the attached question file 18

From the above screenshot we can see that I have error of undefined variables and I have also been given the lines where they occur for easy troubleshooting.

Homework Summary

This exercise has helped me to understand CRUD operations and also create a website that communicates with the database. The website is a travel website that shows tourist attraction sites in different countries. The user of the website can now add, delete, and update these sites. The website is inside a folder called Hw9_ksaxena and the database configuration is inside a folder called config. At the root of the website is a Readme file that indicates the deployment and the configuration of the website.

Future Plans

I intend to work on database integrity in the future where a user cannot register with an existing email etc.