Step 1) Run the SQL script on MySQL Workbench: CREATE TABLE IF NOT EXISTS `crud_
ID: 3711644 • Letter: S
Question
Step 1) Run the SQL script on MySQL Workbench:
CREATE TABLE IF NOT EXISTS `crud_company`
(`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`address` VARCHAR(45) NULL,
`phone` VARCHAR(15) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
insert into crud_company (`name`) values ('Apple');
insert into crud_company (`name`) values ('Amazon');
insert into crud_company (`name`) values ('Google');
Step 2) Build backend web services to enable the following functions: i) list all the companies; and ii) add acompany.
Step 3) Build a frontend web page (like crud.html) to i) list all the companies in the database and ii) add newcompanies.
Explanation / Answer
Following is the answer:
index.php
<?php require_once 'php_action/db_connect.php'; ?>
<!DOCTYPE html>
<html>
<head>
<title>PHP CRUD</title>
<style type="text/css">
.manageMember {
width: 50%;
margin: auto;
}
table {
width: 100%;
margin-top: 20px;
}
</style>
</head>
<body>
<div class="manageMember">
<a href="create.php"><button type="button">Add Member</button></a>
<table border="1" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th>Name</th>
<th>Contact</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?php
$sql = "SELECT * FROM members WHERE active = 1";
$result = $connect->query($sql);
if($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<tr>
<td>".$row['name']."</td>
<td>".$row['contact']."</td>
<td>".$row['address']."</td>
<td>
<a href="edit.php?id=".$row["id']."'><button type='button'>Edit</button></a>
<a href="remove.php?id=".$row["id']."'><button type='button'>Remove</button></a>
</td>
</tr>";
}
} else {
echo "<tr><td colspan='5'><center>No Data Avaliable</center></td></tr>";
}
?>
</tbody>
</table>
</div>
</body>
</html>
create.php
<!DOCTYPE html>
<html>
<head>
<title>Add Member</title>
<style type="text/css">
fieldset {
margin: auto;
margin-top: 100px;
width: 50%;
}
table tr th {
padding-top: 20px;
}
</style>
</head>
<body>
<fieldset>
<legend>Add Member</legend>
<form action="php_action/create.php" method="post">
<table cellspacing="0" cellpadding="0">
<tr>
<th>Name</th>
<td><input type="text" name="name" placeholder="Name" /></td>
</tr>
<tr>
<th>Contact</th>
<td><input type="text" name="contact" placeholder="Contact" /></td>
</tr>
<tr>
<th>Address</th>
<td><textarea cols="19" rows="7" name="address"></textarea></td>
</tr>
<tr>
<td><button type="submit">Save Changes</button></td>
<td><a href="index.php"><button type="button">Back</button></a></td>
</tr>
</table>
</form>
</fieldset>
</body>
</html>
edit.php
<?php
require_once 'php_action/db_connect.php';
if($_GET['id']) {
$id = $_GET['id'];
$sql = "SELECT * FROM members WHERE id = {$id}";
$result = $connect->query($sql);
$data = $result->fetch_assoc();
$connect->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>Edit Member</title>
<style type="text/css">
fieldset {
margin: auto;
margin-top: 100px;
width: 50%;
}
table tr th {
padding-top: 20px;
}
</style>
</head>
<body>
<fieldset>
<legend>Edit Member</legend>
<form action="php_action/update.php" method="post">
<table cellspacing="0" cellpadding="0">
<tr>
<th>Name</th>
<td><input type="text" name="name" placeholder="Name" value="<?php echo $data['name'] ?>" /></td>
</tr>
<tr>
<th>Contact</th>
<td><input type="text" name="contact" placeholder="Contact" value="<?php echo $data['contact'] ?>" /></td>
</tr>
<tr>
<th>Address</th>
<td><textarea cols="19" rows="7"><?php echo $data['address'] ?></textarea></td>
</tr>
<tr>
<input type="hidden" name="id" value="<?php echo $data['id']?>" />
<td><button type="submit">Save Changes</button></td>
<td><a href="index.php"><button type="button">Back</button></a></td>
</tr>
</table>
</form>
</fieldset>
</body>
</html>
<?php
}
?>
remove.php
<?php
require_once 'php_action/db_connect.php';
if($_GET['id']) {
$id = $_GET['id'];
$sql = "SELECT * FROM members WHERE id = {$id}";
$result = $connect->query($sql);
$data = $result->fetch_assoc();
$connect->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>Remove Member</title>
</head>
<body>
<h3>Do you really want to remove ?</h3>
<form action="php_action/remove.php" method="post">
<input type="hidden" name="id" value="<?php echo $data['id'] ?>" />
<button type="submit">Save Changes</button>
<a href="index.php"><button type="button">Back</button></a>
</form>
</body>
</html>
<?php
}
?>
php_action/create.php
<?php
require_once 'db_connect.php';
if($_POST) {
$name = $_POST['name'];
$contact = $_POST['contact'];
$address = $_POST['address'];
$sql = "INSERT INTO members (name, contact, address, active) VALUES ('$name', '$contact', '$address', 1)";
if($connect->query($sql) === TRUE) {
echo "<p>New Record Successfully Created</p>";
echo "<a href="../create.php"><button type='button'>Back</button></a>";
echo "<a href="../index.php"><button type='button'>Home</button></a>";
} else {
echo "Error " . $sql . ' ' . $connect->connect_error;
}
$connect->close();
}
?>
php_action/db_connect.php
<?php
$localhost = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "php_crud";
// create connection
$connect = new mysqli($localhost, $username, $password, $dbname);
// check connection
if($connect->connect_error) {
die("connection failed : " . $connect->connect_error);
} else {
// echo "Successfully Connected";
}
?>
php_action/remove.php
<?php
require_once 'db_connect.php';
if($_POST) {
$id = $_POST['id'];
$sql = "UPDATE members SET active = 2 WHERE id = {$id}";
if($connect->query($sql) === TRUE) {
echo "<p>Successfully removed!!</p>";
echo "<a href="../index.php"><button type='button'>Back</button></a>";
} else {
echo "Error updating record : " . $connect->error;
}
$connect->close();
}
?>
php_action/update.php
<?php
require_once 'db_connect.php';
if($_POST) {
$fname = $_POST['name'];
$contact = $_POST['contact'];
$comment = $_POST['address'];
$id = $_POST['id'];
$sql = "UPDATE members SET name = '$name', contact = '$contact', address = '$address' WHERE id = {$id}";
if($connect->query($sql) === TRUE) {
echo "<p>Succcessfully Updated</p>";
echo "<a href="../edit.php?id=".$id.""><button type='button'>Back</button></a>";
echo "<a href="../index.php"><button type='button'>Home</button></a>";
} else {
echo "Erorr while updating record : ". $connect->error;
}
$connect->close();
}
?>
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.