The aim of this article is to serve as a quick reference guide to create an application to maintain user database.
The code is divided into three basic parts.
First part is to create a Form using HTML.
Second part is to build the CRUD (Create, Read, Update, Delete) code using PHP and MySQL.
Third part is to output the search results.
Code to create a user form
<?php include "templates/header.php"; ?><h2>Add a user</h2>
<form method="post">
<label for="firstname">First Name</label>
<input type="text" name="firstname" id="firstname">
<label for="lastname">Last Name</label>
<input type="text" name="lastname" id="lastname">
<label for="email">Email Address</label>
<input type="text" name="email" id="email">
<label for="age">Age</label>
<input type="text" name="age" id="age">
<label for="location">Location</label>
<input type="text" name="location" id="location">
<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php include "templates/footer.php"; ?> Code for CRUD using PHP and MySQL
<?php
/**
* Use an HTML form to create a new entry in the
* users table.
*
*/
if (isset($_POST['submit'])) {
require "../config.php";
require "../common.php";
try {
$connection = new PDO($dsn, $username, $password, $options);
$new_user = array(
"firstname" => $_POST['firstname'],
"lastname" => $_POST['lastname'],
"email" => $_POST['email'],
"age" => $_POST['age'],
"location" => $_POST['location']
);
$sql = sprintf(
"INSERT INTO %s (%s) values (%s)",
"users",
implode(", ", array_keys($new_user)),
":" . implode(", :", array_keys($new_user))
);
$statement = $connection->prepare($sql);
$statement->execute($new_user);
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) { ?>
> <?php echo $_POST['firstname']; ?> successfully added.
<?php } ?>
<h2>Add a user</h2>
<form method="post">
<label for="firstname">First Name</label>
<input type="text" name="firstname" id="firstname">
<label for="lastname">Last Name</label>
<input type="text" name="lastname" id="lastname">
<label for="email">Email Address</label>
<input type="text" name="email" id="email">
<label for="age">Age</label>
<input type="text" name="age" id="age">
<label for="location">Location</label>
<input type="text" name="location" id="location">
<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?> Code to display the search results
<?php
/**
* Function to query information based on
* a parameter: in this case, location.
*
*/
if (isset($_POST['submit'])) {
try {
require "../config.php";
require "../common.php";
$connection = new PDO($dsn, $username, $password, $options);
$sql = "SELECT *
FROM users
WHERE location = :location";
$location = $_POST['location'];
$statement = $connection->prepare($sql);
$statement->bindParam(':location', $location, PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetchAll();
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>
<?php
if (isset($_POST['submit'])) {
if ($result && $statement->rowCount() > 0) { ?>
<h2>Results</h2>
<table>
<thead>
<tr>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email Address</th>
<th>Age</th>
<th>Location</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<?php foreach ($result as $row) { ?>
<tr>
<td><?php echo escape($row["id"]); ?></td>
<td><?php echo escape($row["firstname"]); ?></td>
<td><?php echo escape($row["lastname"]); ?></td>
<td><?php echo escape($row["email"]); ?></td>
<td><?php echo escape($row["age"]); ?></td>
<td><?php echo escape($row["location"]); ?></td>
<td><?php echo escape($row["date"]); ?> </td>
</tr>
<?php } ?>
</tbody>
</table>
<?php } else { ?>
> No results found for <?php echo escape($_POST['location']); ?>.
<?php }
} ?>
<h2>Find user based on location</h2>
<form method="post">
<label for="location">Location</label>
<input type="text" id="location" name="location">
<input type="submit" name="submit" value="View Results">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?> 
