Categories
JavaScript Templating PHP VueJS

VueJS PHP CRUD using Web API

I would like to share my CRUD (Create, Read, Update and Delete) using Web API and VueJS in PHP.

Creating the Database Table

Execute SQL Dump file

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 17, 2020 at 08:40 AM
-- Server version: 10.1.38-MariaDB
-- PHP Version: 7.1.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `db_vue_product`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `namex` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `stock` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `namex`, `category`, `qty`, `stock`) VALUES
(150, '1st Product', 'category 1', '4', 'In Stock'),
(151, '2nd Product', 'category 12', '0', 'Out of stock'),
(186, '3rd Product', 'category 3', '22', 'In Stock'),
(187, '4th Products', 'wwd', '2', 'In Stock');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=188;
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 */;

Creating the Connection File

<?php
    class Database{ 
        private $db_host = 'localhost';
        private $db_name = 'db_vue_product';
        private $db_username = 'root';
        private $db_password = '';
        
        public function dbConnection(){
            try{
                $conn = new PDO('mysql:host='.$this->db_host.';dbname='.$this->db_name,$this->db_username,$this->db_password);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                return $conn;
            }
            catch(PDOException $e){
                echo "Connection error ".$e->getMessage(); 
                exit;
            } 
        }
    }
?>

Web API Controller

The Web API Controller consists of the following three Action methods.
– Get all the records from the Product table
– Add and Update Product
– Delete Product

<?php
    header("Access-Control-Allow-Origin: *");
    header("Access-Control-Allow-Headers: access");
    header("Access-Control-Allow-Methods: POST");
    header("Access-Control-Allow-Credentials: true");
    header("Content-Type: application/json; charset=UTF-8");
    // header("Accept-Encoding", "UTF-8");

    require 'database.php';
    $db_connection = new Database();
    $conn = $db_connection->dbConnection();

    // Raw JSON Parameter
       $data=json_decode(file_get_contents("php://input"));
       
    // Extract the Data Fields
    $action = $data->action;
    switch ($action) 
    {
        case "get_product_list":

            $statement = $conn->prepare("SELECT * FROM products");
            $statement->execute();
            $results=$statement->fetchAll(PDO::FETCH_ASSOC);
            print json_encode($results, JSON_UNESCAPED_UNICODE);
            break;

        case "AddUpdate":
          
            $id = $data->id;
            $name = $data->name;
            $category = $data->category;
            $qty = $data->qty;
            $action = $data->btnName;
            
            if($qty > 0) {
              $qty_status = 'In Stock';
            } else if($qty < 1) {
              $qty_status = 'Out of stock';
            }

            if ($action =='SAVE') {
                  $statement = $conn->prepare("SELECT * FROM products WHERE id='$id'");
                  $statement->execute();
                  $results=$statement->fetchAll(PDO::FETCH_ASSOC);
                  if ($results > 1) {
                    $statement = $conn->prepare("INSERT INTO products (id, namex, category, qty, stock) 
                    VALUES ('', ?, ?, ?, ?)");
                    $statement->bindParam(1, $name, PDO::PARAM_STR);
                    $statement->bindParam(2, $category, PDO::PARAM_STR);
                    $statement->bindParam(3, $qty, PDO::PARAM_STR);
                    $statement->bindParam(4,$qty_status);
                    $statement->execute();
                   
                  } else {
                  }
  
            } else if ($action =='UPDATE') {
                  $id = $data->id;
                  $statement = $conn->prepare("SELECT * FROM products WHERE id='$id'");
                  $statement->execute();
                  $results=$statement->fetchAll(PDO::FETCH_ASSOC);
                  if ($results > 1) {
                    $statement = $conn->prepare("UPDATE products SET namex=?, category=?, qty=?, stock=? WHERE id=$id");
                    $statement->bindParam(1, $name, PDO::PARAM_STR);
                    $statement->bindParam(2, $category, PDO::PARAM_STR);
                    $statement->bindParam(3, $qty, PDO::PARAM_STR);
                    $statement->bindParam(4,$qty_status);
                    $statement->execute();
                  } else {
                  }
            }
            break;

        case "Delete":
        
            $id=$data->id;

            $statement =$conn->prepare("DELETE FROM products WHERE id = '$id'");
            $statement->execute();
            break;
    }
?>

Creating a Vue instance with the Vue function

const app = new Vue({
	el: "#my-app",
	data: {
		errors: [],
		searchQuery: '',
		products: [],
		id: "",
		name: null,
		category: null,
		qty: null,
		btnName: "SAVE",
		formTitle: "ADD PRODUCT"
	},
	methods: {

		addProduct: function () {
			this.id = "";
			this.name = "";
			this.category = "";
			this.qty = "";
			this.btnName = "SAVE";
			this.formTitle = 'ADD PRODUCT';
		},

		getAllProduct: function () {
			var self = this;
			axios({
				method: "POST",
				url: "./library/api.php",
				dataType: 'json',
				contentType: 'application/json',
				data: JSON.stringify({ "action": "get_product_list" }),
			}).then(function success(response) {
				self.products = response.data
			});
		},

		deleteProduct: function (id) {
			var self = this;
			if (confirm("Are you sure you want to remove it?")) {
				axios({
					method: "POST",
					url: "./library/api.php",
					data: { 'id': id, 'action': 'Delete' }
				}).then(function success(response) {
					self.getAllProduct();
					let message = "Product was successfully deleted";
					self.flashMessage(message, 'success');
				});
			}
		},

		update_data: function (id, name, category, qty) {
			this.id = id;
			this.name = name;
			this.category = category;
			this.qty = qty;
			this.btnName = "UPDATE";
			this.formTitle = 'EDIT PRODUCT';
		},

		InsertUpdateProduct: function (event) {
			var self = this;

			if (this.name === '' || this.category === '' || this.qty === '') {
				this.errors = [];
				if (!this.name) this.errors.push("Product Name required.");
				if (!this.category) this.errors.push("Category required.");
				if (!this.qty) this.errors.push("Quantity required.");
			} else {
				axios({
					method: "POST",
					url: "./library/api.php",
					dataType: 'json',
					contentType: 'application/json',
					data: JSON.stringify({ id: this.id, name: this.name, category: this.category, qty: this.qty, btnName: this.btnName, "action": "AddUpdate" }),
				}).then(function success(response) {
					$('#Productform').modal('hide');
					self.errors.splice(0);
					this.name = "";
					this.category = "";
					this.qty = "";
					this.id = "";
					self.getAllProduct();
					if (self.btnName == 'SAVE') {
						let message = "Product was successfully Added";
						self.flashMessage(message, 'success');
					} else if (self.btnName == 'UPDATE') {
						let message = "Product was successfully Updated";
						self.flashMessage(message, 'success');
					}
				});
			}
		},

		filterItems: function (products) {
			var self = this;
			return products.filter(function (product) {
				let regex = new RegExp('(' + self.searchQuery + ')', 'i');
				return product.namex.match(regex);
			})
		},

		flashMessage: function (message, type = 'success') {
			if (type === 'success') {
				iziToast.success({
					title: 'Success :',
					message: message.charAt(0).toUpperCase() + message.substr(1).toLowerCase(),
					timeout: 4000,
					// theme: 'dark',
					animateInside: true,
					position: 'topRight',
					icon: 'fa fa-heart'
				});
			} else if (type === 'error') {
				iziToast.error({
					title: 'Error :',
					message: message.charAt(0).toUpperCase() + message.substr(1).toLowerCase(),
					timeout: 4000,
					// theme: 'dark',
					animateInside: true,
					position: 'topRight',
				});
			} else if (type === 'info') {
				iziToast.info({
					title: 'Info :',
					message: message.charAt(0).toUpperCase() + message.substr(1).toLowerCase(),
					timeout: 4000,
					// theme: 'dark',
					animateInside: true,
					position: 'topRight',
				});
			} else {
				iziToast.warning({
					title: 'Success :',
					message: message.charAt(0).toUpperCase() + message.substr(1).toLowerCase(),
					timeout: 4000,
					// theme: 'dark',
					animateInside: true,
					position: 'topRight',
				});
			}
		},

	},
	mounted() {
		this.getAllProduct();
	},
});

Creating Product Page

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>Proghubsterzeak</title>
  <link rel="stylesheet" href="./assets/bootstrap/css/bootstrap.min.css">
  <link rel="stylesheet" href="./assets/izitoast/dist/css/izitoast.min.css">
  <style>
  .warning {
    background-color: rgb(255, 140, 0);
    color: #fff;
  }
  </style>
</head>

<body>

  <header>
    <nav class="navbar navbar-light bg-light">
      <span class="navbar-text">
        <a href="https://proghubsterzeak.com/"> https://proghubsterzeak.com</a>
      </span>
    </nav>
  </header>

  <section id="my-app">
    <div class="container">
      <div class="row my-5">
        <button v-on:click="addProduct();" type="button" class="btn btn-info btn-sm" data-toggle="modal"
          data-target="#Productform">Add
          Product</button>
        <div class="ml-auto">
          <input class="form-control ng-pristine ng-valid" v-model="searchQuery" type="text">
        </div>
      </div>
      <div class="row">
        <div class="table-responsive">
          <table class="table-product table table-bordered table-striped">
            <thead class="thead-product">
              <tr>
                <th style="width:5%">No.</th>
                <th>Name</th>
                <th style="width:15%">Category</th>
                <th style="width:5%">Quantity</th>
                <th style="width:15%">Status</th>
                <th style="width:18%">Action</th>
              </tr>
            </thead>
            <tbody>
              <tr v-for="(product, index) in filterItems(products)" :key="product.id">
                <td>{{ product.id }}</td>
                <td>{{ product.namex }}</td>
                <td>{{ product.category }}</td>
                <td>{{ product.qty }}</td>
                <td v-if="product.qty > 10"><span class="badge badge-success">In Stock</span></td>
                <td v-else-if="product.qty <= 10 && product.qty > 0"><span class="badge badge-primary">ALmost Sold
                    Out</span></p>
                <td v-else><span class="badge badge-warning">Out Of Stock</span></td>
                <td>
                  <div class=" buttons text-center">
                    <button v-on:click="update_data(product.id, product.namex, product.category, product.qty);"
                      class="btn btn-primary btn-edit btn-sm" type="button" data-toggle="modal"
                      data-target="#Productform">Edit</button>
                    <button v-on:click="deleteProduct(product.id);"
                      class="btn btn-danger btn-delete btn-sm">Delete</button>
                  </div>
                </td>
              </tr>
            </tbody>
          </table>
        </div>
      </div>
    </div>
    <div id="Productform" class="modal fade" tabindex="-1" role="dialog" aria-labelledby="my-modal-title"
      aria-hidden="true">
      <div class="modal-dialog" role="document">
        <div class="modal-content">
          <div class="modal-header bg-light">
            <h5 class="modal-title" id="my-modal-title">{{ formTitle }}</h5>
            <button class="close" data-dismiss="modal" aria-label="Close">
              <span aria-hidden="true">&times;</span>
            </button>
          </div>
          <p v-if="errors.length" class="col-12">
            <b>Please correct the following error(s):</b>
            <ul>
              <li v-for="error in errors">{{ error }}</li>
            </ul>
          </p>
          <div class="modal-body">
            <form @submit.stop.prevent="submit">
              <input type="hidden" ng-model="id">
              <div class="form-group">
                <label for="my-input">Product</label>
                <input class="form-control" type="text" v-model="name" placeholder="Product Name" />
              </div>
              <div class="form-group">
                <label for="my-input">Category</label>
                <input class="form-control" type="text" v-model="category" placeholder="Category" />
              </div>
              <div class="form-group">
                <label for="my-input">Quantity</label>
                <input class="form-control" type="number" v-model="qty" placeholder="Quantity" required />
              </div>
              <input name="insert" type="submit" class="btn btn-primary btn-add my-2" v-on:click="InsertUpdateProduct()"
                :value="btnName" />
            </form>
          </div>
        </div>
      </div>
    </div>
  </section>

  <script src="./assets/jquery/jquery.min.js"></script>
  <script src="./assets/bootstrap/js/bootstrap.bundle.js"></script>
  <script src="./assets/izitoast/dist/js/izitoast.min.js"></script>
  <script src="./assets/vue/vue.js"></script>
  <script src="./assets/vue/axios.js"></script>
  <script src="./assets/js/script.js"></script>

</body>

</html>

If you want to get complete source code, please click Download Code button on below link. For more information about the system. You can contact me @ Email – larrydaveemol@gmail.com

Let me know what you need just leave a message/comment and I will do my very best to make that happen. I hope this codes will help you. Enjoy Coding!