March 2022

MySQL Schema

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

REST API

rest_api.php

<?php

header('Access-Control-Allow-Origin: *');

$servername = "localhost";
$username = "diituser";
$password = "%TGBbgt5";
$dbname = "ecom";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
  echo "Error: " . $e->getMessage();
}




switch($_SERVER['REQUEST_METHOD']) {
    case 'GET':

        if(isset($_REQUEST['id']) && $_REQUEST['id'] != '') {
            $id = $_REQUEST['id'];
            $sql = "SELECT * FROM users WHERE id = $id";
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $records = $stmt->fetch();
        } else {
            $sql = "SELECT * FROM users";
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
            $records = $stmt->fetchAll();
        }
        echo json_encode($records);

    case 'DELETE':

        $data = json_decode(file_get_contents('php://input'), true);

        $id = $data["id"];
        $sql = "DELETE FROM users WHERE id = $id";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);

    case 'POST':

        $data = json_decode(file_get_contents('php://input'), true);

        $name = $data["name"];
        $age = $data['age'];
        $city = $data['city'];
        $sql = "INSERT INTO `users` (`id`, `name`, `age`, `city`) VALUES (NULL, '$name', '$age', '$city'); ";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);

    case 'PUT':

        $data = json_decode(file_get_contents('php://input'), true);

        $id = $data['id'];
        $name = $data["name"];
        $age = $data['age'];
        $city = $data['city'];
        $sql = "UPDATE users SET `name` = '$name', age = '$age', city = '$city' WHERE id = $id";
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        echo json_encode(["result" => "success"]);
}


$conn = null;

FRONT END

script.js

const api_url = "rest_api.php";
function loadData(records = []) {
  var table_data = "";
  for (let i = 0; i < records.length; i++) {
    table_data += `<tr>`;
    table_data += `<td>${records[i].name}</td>`;
    table_data += `<td>${records[i].age}</td>`;
    table_data += `<td>${records[i].city}</td>`;
    table_data += `<td>`;
    table_data += `<a href="edit.php?id=${records[i].id}"><button class="btn btn-primary">Edit</button></a>`;
    table_data += "&nbsp;&nbsp;";
    table_data += `<button class="btn btn-danger" onclick=deleteData('${records[i].id}')>Delete</button>`;
    table_data += `</td>`;
    table_data += `</tr>`;
  }
  //console.log(table_data);
  document.getElementById("tbody").innerHTML = table_data;
}
function getData() {
  fetch(api_url)
    .then((response) => response.json())
    .then((data) => {
      console.table(data);
      loadData(data);
    });
}
function getDataById(id) {
  fetch(`${api_url}?id=${id}`)
    .then((response) => response.json())
    .then((data) => {
      console.log(data);
      document.getElementById("id").value = data.id;
      document.getElementById("name").value = data.name;
      document.getElementById("age").value = data.age;
      document.getElementById("city").value = data.city;
    });
}
function postData() {
  var name = document.getElementById("name").value;
  var age = document.getElementById("age").value;
  var city = document.getElementById("city").value;
  data = { name: name, age: age, city: city };
  fetch(api_url, {
    method: "POST",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: JSON.stringify(data),
  })
    .then((response) => response.json())
    .then((data) => {
      console.log(data);
      window.location.href = "index.php";
    });
}
function putData() {
  var id = document.getElementById("id").value;
  var name = document.getElementById("name").value;
  var age = document.getElementById("age").value;
  var city = document.getElementById("city").value;
  data = { id: id, name: name, age: age, city: city };
  fetch(api_url, {
    method: "PUT",
    headers: {
      Accept: "application/json",
      "Content-Type": "application/json",
    },
    body: JSON.stringify(data),
  })
    .then((response) => response.json())
    .then((data) => {
      console.table(data);
      window.location.href = "index.php";
    });
}
function deleteData(id) {
  user_input = confirm("Are you sure you want to delete this record?");
  if (user_input) {
    fetch(api_url, {
      method: "DELETE",
      headers: {
        Accept: "application/json",
        "Content-Type": "application/json",
      },
      body: JSON.stringify({ id: id }),
    })
      .then((response) => response.json())
      .then((data) => {
        console.log(data);
        window.location.reload();
      });
  }
}

index.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link active" aria-current="page" href="#">Listing</a>
                        <a class="nav-link" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <table class="table table-striped table-hover text-center">
        <thead>
            <th>Name</th>
            <th>Age</th>
            <th>City</th>
            <th>Action</th>
        </thead>
        <tbody id="tbody">
        </tbody>
        <tfoot>
        </tfoot>
    </table>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
    getData();
</script>

</html>

add.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link" href="index.php">Listing</a>
                        <a class="nav-link active" aria-current="page" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <h3>Add Document</h3>
    <form onsubmit="return false;">
        <div class="mb-3">
            <label for="name" class="form-label">Name</label>
            <input type="text" class="form-control" id="name" autofocus>
        </div>
        <div class="mb-3">
            <label for="exampleInputPassword1" class="form-label">Age</label>
            <input type="text" class="form-control" id="age">
        </div>
        <div class="mb-3">
            <label for="city" class="form-label">City</label>
            <input type="text" class="form-control" id="city">
        </div>
        <button class="btn btn-primary" onclick="return postData()">Submit</button>
        <a href="index.php" class="btn btn-primary">Cancel</a>
    </form>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
</script>

</html>

edit.php

<html>

<head>
    <title>Project</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" crossorigin="anonymous"></script>
</head>

<body class="d-flex flex-column h-100 container">
    <header>
        <nav class="navbar navbar-expand-lg navbar-light bg-light">
            <div class="container-fluid">
                <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
                    <div class="navbar-nav">
                        <a class="nav-link" href="index.php">Listing</a>
                        <a class="nav-link active" aria-current="page" href="add.php">Add New</a>
                    </div>
                </div>
            </div>
        </nav>
    </header>
    <h3>Edit Document</h3>
    <form onsubmit="return false;">
        <input type="hidden" class="form-control" id="id">
        <div class="mb-3">
            <label for="name" class="form-label">Name</label>
            <input type="text" class="form-control" id="name" autofocus>
        </div>
        <div class="mb-3">
            <label for="exampleInputPassword1" class="form-label">Age</label>
            <input type="text" class="form-control" id="age">
        </div>
        <div class="mb-3">
            <label for="city" class="form-label">City</label>
            <input type="text" class="form-control" id="city">
        </div>
        <button class="btn btn-primary" onclick="return putData()">Update</button>
        <a href="index.php" class="btn btn-primary">Cancel</a>
    </form>
    <footer class="footer mt-auto py-3 bg-light">
        <div class="container text-center">
            <span class="text-muted">right &copy; 2021</span>
        </div>
    </footer>
</body>
<script src="script.js"></script>
<script>
    const urlParams = new URLSearchParams(window.location.search);
    const id = urlParams.get('id');
    getDataById(id);
</script>

</html>

Month: March 2022

Python

Online Tool

Software to be installed for Python

PHP CRUD CHEAT SHEET

REST API

FRONT END