| Today you will learn how to create a crud with php by connecting to a mysql database, on the frontend we will only use javascript to consume the data that mysql will provide us through php. |
PHP
Let's create the connection with mysql
database.php
<?php $connection = mysqli_connect(
"localhost", "root", "", "products_app"
);
if(!$connection) echo 'Error connecting to database';
?>
We are going to create the only html file of the crud, it will contain a form where we will create the products and next to it we will list all the products.
<!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>
How create a simple CRUD (Products) with PHP and Javascript (Fetch) and
Materialize
</title>
(html comment removed: Import Google Icon Font)
<link
href="https://fonts.googleapis.com/icon?family=Material+Icons"
rel="stylesheet"
/>
(html comment removed: Compiled and minified CSS )
<link
rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css"
/>
<link rel="stylesheet" href="custom.css">
<script src="main.js" defer></script>
</head>
<body>
<nav class="blue darken-4">
<div class="nav-wrapper container">
<a href="/" class="btn-floating btn-large pulse cyan"
><i class="material-icons">code</i></a
>
<ul id="nav-mobile" class="right">
<li>
<a href="#!" class="brand-logo"><small>Jfdesousa7</small></a>
</li>
</ul>
</div>
</nav>
<div class="main container">
<h4>
How create a simple CRUD (Products) with PHP and Javascript (Fetch) and
Materialize
</h4>
<div class="row" style="padding-top: 40px">
<div class="col s12"></div>
<div class="col s6">
<div class="card">
<form class="col s12" id="form" autocomplete="off">
<input type="hidden" id="id" value="">
<div class="row">
<h6>New Item
<div class="input-field col s12">
<input
autofocus
placeholder="Item Name"
id="item"
type="text"
class="validate"
/>
</div>
<div class="input-field col s12">
<input
placeholder="Item Price"
id="price"
type="number"
step=".01"
class="validate"
/>
</div>
<button
class="btn waves-effect waves-light"
type="submit"
name="action"
>
Submit
<i class="material-icons right">send</i>
</button>
</div>
</form>
</div>
</div>
<div class="col s6">
<h6>List of Items</h6>
<table>
<thead>
<tr>
<th>Item Name</th>
<th>Item Price</th>
<th></th>
</tr>
</thead>
<tbody id="tbody">
</tbody>
</table>
</div>
</div>
</div>
<footer class="page-footer blue darken-4">
<div class="footer-copyright">
<div class="container">
© 2021
<a class="grey-text text-lighten-3 right" href="https://hive.blog/@jfdesousa7">Visit my hive profile jfdesousa7</a>
</div>
</div>
</footer>
<!-- Compiled and minified JavaScript -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js">
</body>
</html>
For this crud we will use a css framework called Materialize https://materializecss.com/getting-started.html
We will add a few custom styles
custom.css
body {
display: flex;
min-height: 100vh;
flex-direction: column;
}
.main {
flex: 1 0 auto;
}
.page-footer {
padding-top: 0;
}
We will create the main file .js that will communicate the front (browser-javascript) with the back (php-mysql)
main.js
const tbody = document.querySelector("#tbody");
let edit = false;
async function main() {
edit = false;
tbody.innerHTML = "";
const result = await fetch("fetchAllProducts.php");
const data = await result.json();
console.log(data);
return data.map((i) => {
const tr = document.createElement("tr");
tr.id = i.id;
const td1 = document.createElement("td");
const td2 = document.createElement("td");
const td3 = document.createElement("td");
const item = document.createTextNode(i.item);
const price = document.createTextNode(`$ ${i.price}`);
const actions = `<a class="btn-floating btn-small waves-effect waves-light yellow"><i class="material-icons itemEdit">edit</i></a> <a class="btn-floating btn-small waves-effect waves-light red "><i class="material-icons itemDelete">delete</i></a>`;
td1.appendChild(item);
td2.appendChild(price);
td3.innerHTML = actions;
tr.appendChild(td1);
tr.appendChild(td2);
tr.appendChild(td3);
tbody.appendChild(tr);
});
}
document.addEventListener("DOMContentLoaded", function () {
main();
});
const classDelete = "itemDelete";
const classEdit = "itemEdit";
document.body.addEventListener("click", (e) => {
const valor = e.target.className;
const arrayOfClass = valor.split(" ");
if (arrayOfClass.includes(classDelete) || arrayOfClass.includes(classEdit)) {
const element = e.target.parentElement.parentElement.parentElement;
const id = element.getAttribute("id");
if (arrayOfClass.includes(classDelete)) {
deleteItem(id);
} else if (arrayOfClass.includes(classEdit)) {
editItem(id);
}
}
});
// edit item
async function editItem(id) {
const result = await fetch("getSingleProduct.php", {
method: "POST",
body: JSON.stringify({
id,
}),
headers: {
"Content-Type": "application/json",
},
});
const data = await result.json();
console.log(data);
document.getElementById("item").value = data.item;
document.getElementById("price").value = data.price;
document.getElementById("id").value = id;
edit = true;
}
// delete item
async function deleteItem(id) {
await fetch("deleteProduct.php", {
method: "POST",
body: JSON.stringify({
id,
}),
headers: {
"Content-Type": "application/json",
},
});
main();
}
//add item
const form = document.querySelector("#form");
const item = document.querySelector("#item");
const price = document.querySelector("#price");
const id = document.querySelector("#id");
form.addEventListener("submit", async (e) => {
e.preventDefault();
const url = !edit ? "fetchAddProduct.php" : "fetchEditProduct.php";
const result = await fetch(url, {
method: "POST",
body: JSON.stringify({
item: item.value,
price: price.value,
id: id.value,
}),
headers: {
"Content-Type": "application/json",
},
});
const r = await result.json();
console.log(r);
main();
form.reset();
});
We will list all the products we have in the table of products
fetchAllProducts.php
<?php include "database.php";
$array = array();
$query = "select * from products order by id DESC";
$sql = mysqli_query($connection, $query);
while($rows = mysqli_fetch_assoc($sql)){
$array[] = array("id" => $rows["id"], "item" => $rows["item"], "price" => $rows["price"]);
}
echo json_encode($array);
?>
Obtaining a unique product
getSingleProduct.php
<?php include "database.php";
$input = file_get_contents("php://input");
$data = json_decode($input);
$id = mysqli_real_escape_string($connection, $data->id) ;
$query = "select * from products where id=".$id."";
$sql =mysqli_fetch_assoc(mysqli_query($connection, $query)) ;
$json = array("item"=>$sql["item"], "price" => $sql["price"] );
if (!$sql) {
die('Failed.');
}
echo json_encode($json);
?>
Adding a new product
fetchAddProduct.php
<?php include "database.php";
$input = file_get_contents("php://input");
$data = json_decode($input);
$item = mysqli_real_escape_string($connection, $data->item) ;
$price = mysqli_real_escape_string($connection, $data->price) ;
$query = "insert into products(item, price) values ('".$item."', ".$price.")";
$sql = mysqli_query($connection, $query);
if (!$sql) {
die('Failed.');
}
echo json_encode('Success');
?>
Eliminando un producto
deleteProduct.php
<?php include "database.php";
$input = file_get_contents("php://input");
$data = json_decode($input);
$id = mysqli_real_escape_string($connection, $data->id) ;
$query = "delete from products where id=".$id."";
$sql = mysqli_query($connection, $query);
if (!$sql) {
die('Failed.');
}
echo json_encode('Success');
?>
Updating a product
fetchEditProduct.php
<?php include "database.php";
$input = file_get_contents("php://input");
$data = json_decode($input);
$item = mysqli_real_escape_string($connection, $data->item) ;
$price = mysqli_real_escape_string($connection, $data->price) ;
$id = mysqli_real_escape_string($connection, $data->id) ;
$query = "update products set item='".$item."' , price=".$price." where id=".$id."";
$sql = mysqli_query($connection, $query);
if (!$sql) {
die('Failed.');
}
echo json_encode('Success');
?>
Final Look
And with those friends we reached the end of the tutorial, I hope you enjoyed it and until next time! |