A simple crud and search in php and mysql program.
I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me at the following email address for further details. If you want to advertise on my website kindly contact me also in my email address also. Thank you.
My email address is jakerpomperada@gmail.com and jakerpomperada@yahoo.com
Program Listing
crud_search.sql
-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 01, 2021 at 11:29 PM
-- Server version: 10.4.14-MariaDB
-- PHP Version: 7.4.10
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `crud_search`
--
-- --------------------------------------------------------
--
-- Table structure for table `contacts`
--
CREATE TABLE `contacts` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`contact` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `contacts`
--
ALTER TABLE `contacts`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `contacts`
--
ALTER TABLE `contacts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
index.php
<?php
// Connect to the database
require_once "connection.php";
// Delete Table data
if (isset($_GET["del"])) {
$id = preg_replace('/\D/', '', $_GET["del"]); //Accept numbers only
if ($stmt = $con->prepare("DELETE FROM `contacts` WHERE `id`=?")) {
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->close();
$msg = '<div class="msg msg-delete">Contact details deleted successfully.</div>';
} else {
die('prepare() failed: ' . htmlspecialchars($con->error));
}
}
// Display Table data
$tabledata = "";
$sqlsearch = "";
if (isset($_POST["btnSearch"])) {
$keywords = $con->real_escape_string($_POST["txtSearch"]);
$searchTerms = explode(' ', $keywords);
$searchTermBits = array();
foreach ($searchTerms as $key => &$term) {
$term = trim($term);
$searchTermBits[] = " `name` LIKE '%$term%' OR `email` LIKE '%$term%' OR `contact` LIKE '%$term%'";
}
$sqlsearch = " WHERE " . implode(' AND ', $searchTermBits);
}
if ($stmt = $con->prepare("SELECT * FROM `contacts` $sqlsearch")) {
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$tabledata .= '<tr>
<td>'.$row["name"].'</td>
<td>'.$row["email"].'</td>
<td>'.$row["contact"].'</td>
<td>
<a href="update.php?id='.$row["id"].'" class="btnAction btnUpdate" title="Update contact details">✎</a>
<a href="index.php?del='.$row["id"].'" class="btnAction btnDelete" title="Delete contact details">✖</a>
</td>
</tr>';
}
} else {
$tabledata= '<tr><td colspan="4" style="text-align: center; padding:30px 0;">Nothing to display</td></tr>';
}
$stmt->close();
} else {
die('prepare() failed: ' . htmlspecialchars($con->error));
}
// Close database connection
$con->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Simple CRUD with Search in PHP and MySQL</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<?php if(isset($msg)){ echo $msg; }?>
<main class="container">
<div class="wrapper">
<h1>Simple CRUD with Search in PHP and MySQL</h1>
<h2>» Jake R. Pomperada MAED-IT, MIT «</h2>
</div>
<div class="wrapper">
<a href="create.php" class="btnCreate" title="Create new contact">Create New Contact</a>
</div>
<div class="wrapper">
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<input type="text" name="txtSearch" value="<?php if(isset($keywords)){ echo $keywords; }?>" title="Input keywords here" required>
<button type="submit" name="btnSearch" class="btnSearch" title="Search keywords">Search</button>
<a href="index.php" class="btnReset" title="Reset search">Reset</a>
</form>
</div>
<div class="wrapper">
<table>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Contact</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
echo $tabledata;
?>
</tbody>
</table>
</div>
</main>
</body>
</html>
create.php
<?php
// Delete Table data
if (isset($_POST["btnSave"])) {
// Connect to the database
require_once "connection.php";
$name = $con->real_escape_string($_POST["txtName"]);
$email = $con->real_escape_string($_POST["txtEmail"]);
$contact = $con->real_escape_string($_POST["txtContact"]);
if ($stmt = $con->prepare("INSERT INTO `contacts`(`name`, `email`, `contact`) VALUES (?, ?, ?)")) {
$stmt->bind_param("sss", $name, $email, $contact);
$stmt->execute();
$stmt->close();
$msg = '<div class="msg msg-create">Contact details saved successfully.</div>';
} else {
$msg = '<div class="msg">Prepare() failed: '.htmlspecialchars($con->error).'</div>';
}
// Close database connection
$con->close();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Create Data | Simple CRUD with Search in PHP and MySQL </title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<?php if(isset($msg)){ echo $msg; }?>
<main class="container">
<div class="wrapper">
<h1>Simple CRUD with Search in PHP and MySQL</h1>
<h2>» Jake R. Pomperada MAED-IT, MIT «</h2>
</div>
<div class="wrapper">
<div class="title create">
<h2>Create New Contact</h2>
<hr>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post" class="frmCreate">
<input type="text" name="txtName" placeholder="Name" required>
<input type="email" name="txtEmail" placeholder="Email" required>
<input type="number" min="0" name="txtContact" placeholder="Contact Number" required>
<div class="btnWrapper">
<button type="submit" name="btnSave" title="Save contact details">Save</button>
<a href="index.php" class="btnHome" title="Return back to homepage">Home</a>
</div>
</form>
</div>
</main>
</body>
</html>
update.php
<?php
// Connect to the database
require_once "connection.php";
// Get contact details
if (isset($_GET["id"])) {
$id = preg_replace('/\D/', '', $_GET["id"]); //Accept numbers only
} else {
header("Location: index.php?p=update&err=no_id");
}
// Update contact details
if (isset($_POST["btnUpdate"])) {
$name = $con->real_escape_string($_POST["txtName"]);
$email = $con->real_escape_string($_POST["txtEmail"]);
$contact = $con->real_escape_string($_POST["txtContact"]);
if ($stmt = $con->prepare("UPDATE `contacts` SET `name`=?, `email`=?, `contact`=? WHERE `id`=?")) {
$stmt->bind_param("sssi", $name, $email, $contact, $id);
$stmt->execute();
$stmt->close();
$msg = '<div class="msg msg-update">Contact details updated successfully.</div>';
} else {
$msg = '<div class="msg">Prepare() failed: '.htmlspecialchars($con->error).'</div>';
}
}
if ($stmt = $con->prepare("SELECT `name`, `email`, `contact` FROM `contacts` WHERE `id`=? LIMIT 1")) {
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($name, $email, $contact);
$stmt->fetch();
$stmt->free_result();
$stmt->close();
} else {
die('prepare() failed: ' . htmlspecialchars($con->error));
}
// Close database connection
$con->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Update Data | Simple CRUD with Search in PHP and MySQL</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<?php if(isset($msg)){ echo $msg; }?>
<main class="container">
<div class="wrapper">
<h1>Simple CRUD with Search in PHP</h1>
<h2>» Jake R. Pomperada MAED-IT, MIT «</h2>
</div>
<div class="wrapper">
<div class="title update">
<h2>Update Contact</h2>
<hr>
</div>
<form action="<?=$_SERVER['PHP_SELF']."?id=".$id;?>" method="post" class="frmUpdate">
<input type="text" name="txtName" placeholder="Name" value="<?php echo $name; ?>" required>
<input type="email" name="txtEmail" placeholder="Email" value="<?php echo $email; ?>" required>
<input type="number" min="0" name="txtContact" placeholder="Contact Number" value="<?php echo $contact; ?>" required>
<div class="btnWrapper">
<button type="submit" name="btnUpdate" class="btnUpdate" title="Update contact details">Update</button>
<a href="index.php" class="btnHome" title="Return back to homepage">Home</a>
</div>
</form>
</div>
</main>
</body>
</html>
style.css
* {
margin: 0;
padding: 0;
box-sizing: border-box
}
body,
html {
height: 100%;
font-family: sans-serif;
}
body {
background:#13141c;
color: rgba(255, 255, 255, 0.9);
}
main {
padding: 50px 15px;
}
.wrapper {
display: block;
padding-top: 50px;
text-align: center;
min-width: 600px;
}
h1 {
margin-bottom: 10px;
}
h3 {
margin-bottom: 10px;
}
form {
display: flex;
justify-content: center;
box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
}
.title {
display: block;
margin-bottom: 20px;
}
.title h2,
.title hr {
width: 500px;
}
.title h2 {
display: inline-block;
text-align: left;
}
.title hr {
display: block;
margin: 5px auto;
}
.create hr {
border: 1px solid #1BA345;
}
.update hr {
border: 1px solid #FEC001;
}
table {
border-collapse: collapse;
background-color: #ffffff;
color: #333;
box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
font-size: 15px;
font-family: sans-serif;
margin: 0 auto;
min-width: 600px;
}
table th,
table td {
padding: 15px 25px;
text-align: center;
}
thead tr {
background-color: #2196f3;
}
thead th {
font-size: 18px;
color: #ffffff;
text-transform: uppercase;
}
tbody tr {
border-bottom: 1px solid #dddddd;
}
tbody tr:nth-of-type(odd) {
background-color: #ffffff;
}
tbody tr:nth-of-type(even) {
background-color: #f3f3f3;
}
tbody tr:hover {
color: #2196f3;
}
input {
display: block;
width: 360px;
background: #fff;
color: #000;
box-shadow: 0 6px 10px 0 rgba(0, 0, 0, 0.1);
border: 0;
outline: 0;
padding: 15px 25px;
font-size: 18px;
}
.btnReset,
button {
display: block;
background: #2196f3;
color: #fff;
border: 0;
outline: 0;
padding: 0;
cursor: pointer;
padding: 15px 30px;
font-size: 18px;
text-decoration: none;
text-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
}
.btnSearch,
.btnReset {
width: 120px;
}
.btnReset {
background: #FEC001;
}
.btnCreate,
.btnUpdate,
.btnWrapper a,
.btnAction,
.btnAction.btnUpdate {
display: inline-block;
padding: 5px 8px;
border: none;
border-radius: 2px;
color: #ffffff;
text-shadow: 0 0 10px rgba(0, 0, 0, 0.4);
text-decoration: none;
font-size: 18px;
}
.btnWrapper a,
.btnCreate,
.btnUpdate {
padding: 15px 30px;
background: #1BA345;
}
.btnAction.btnUpdate {
margin-right: 5px;
background: #FEC001;
}
.btnDelete {
background: #DE3E44;
}
.btnWrapper a {
background: #dddddd;
color: #13141c;
}
.frmCreate,
.frmUpdate {
display: inline-block;
margin: 0 auto 20px;
}
.frmCreate input,
.frmUpdate input {
display: block;
width: 500px;
margin-bottom: 10px;
}
.frmCreate button,
.frmUpdate button {
display: block;
color: #fefefe;
}
.frmCreate button {
background: #1BA345;
}
.frmUpdate button {
background: #FEC001;
}
.btnWrapper {
display: block;
text-align: left;
}
.btnWrapper button,
.btnWrapper a {
display: inline-block;
border-radius: 0;
}
.btnWrapper button:hover,
.btnWrapper a:hover {
opacity: 0.9;
}
.msg {
position: absolute;
top: -60px;
left: calc(50% - 200px);
padding: 20px;
color: #fefefe;
text-shadow: 0 0 10px rgba(0, 0, 0, 0.2);
z-index: 9999;
width: 400px;
text-align: center;
animation: hideDiv 2.5s ease;
}
@keyframes hideDiv {
0% { top: -60px; }
10% { top: 20px; }
90% { top: 20px; }
100% { top: -60px; }
}
.msg-create {
background: #1BA345;
}
.msg-update {
background: #FEC001;
}
.msg-delete {
background: #DE3E44;
}
