Categories
JavaScript Templating NodeJS

CRUD app with Node, Express , and MySQL

I would like to share my CRUD (Create, Read, Update and Delete) with Node, Express framework, MySQL and I’m using EJS view engine to manage HTML code .

Prerequisites

to get started follow this article for installation https://medium.com/@bhanushali.mahesh3/creating-a-simple-website-with-node-js-express-and-ejs-view-engine-856382a4578f

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 */;

Server.

const createError = require('http-errors');
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const path = require('path');


const { doProduct, getHomePage } = require('./routes/api');
const port = 2000;

const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'db_angular_product'
});

// connect to database
db.connect((err) => {
  if (err) {
    throw err;
  }
  console.log('Connected to database');
});
global.db = db;

const app = express();

// configure middleware
app.set('port', process.env.port || port); // set express to use this port
app.set('views', __dirname + '/views'); // set express to look in this folder to render our view
app.set('view engine', 'ejs'); // configure template engine
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json()); // parse form data client
app.use(express.static(path.join(__dirname, 'public'))); // configure express to use public folder


// routes for the app
app.get('/', getHomePage);
app.get('/api/:id/:action', doProduct);
app.post('/api/:action', doProduct);


app.get('/search', function (req, res) {
  db.query('SELECT * from products where namex like "%' + req.query.key + '%"',
    function (err, result) {
      if (err) throw err;
      res.render('index.ejs', {
        page: 'proghubsterzeak.com', menuId: 'home', products: result, title: "Add New Product"
      });
    });
});


// catch 404 and forward to error handler
app.use(function (req, res, next) {
  next(createError(404));
});

// error handler
app.use(function (err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

// set the app to listen on the port
app.listen(port, () => {
  console.log(`Server running on port: ${port}`);
});

module.exports = app;

Web API Controller

The Web API Controller consists of the following three Action methods.
– Add and Update Product
– Delete Product

module.exports = {
  getHomePage: (req, res) => {
    let query = "SELECT * FROM `products` ORDER BY id ASC";
    db.query(query, (err, result) => {
      if (err) {
        res.redirect('/');
      } else {
        message = '';
        res.render('index', {
          page: 'proghubsterzeak.com', menuId: 'home', products: result, message: message, title: "Add New Product"
        });
        // console.log(result[0].id);
      }
    });
  },

  doProduct: (request, response) => {
    let productId = request.params.id;
    let message = '';
    let update_id = request.body.update_id;
    let namex = request.body.namex;
    let category = request.body.category;
    let qty = request.body.qty;

    let action = request.params.action;

    switch (action) {
      case "add":

        let nameQuery = "SELECT * FROM `products` WHERE namex = '" + namex + "'";

        db.query(nameQuery, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          if (result.length > 0) {
      
            message = 'Product already exists';
            response.render('index.ejs', {
              page: 'proghubsterzeak.com', menuId: 'home', products: result, message: message, title: "Add New Product"
            });
          } else {

            let query = "INSERT INTO `products` (namex, category, qty) VALUES ('" +
              namex + "', '" + category + "', '" + qty + "')";
            db.query(query, (err, result) => {
              if (err) {
                return response.status(500).send(err);
              }
              response.redirect('/');
            });

          }
        });

        break;

      case "edit":

        let query = "UPDATE `products` SET `namex` = '" + namex + "', `category` = '" + category + "', `qty` = '" + qty + "' WHERE `products`.`id` = '" + update_id + "'";
        db.query(query, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          response.redirect('/');
        });

        break;

      case "delete":

        let deleteUserQuery = 'DELETE FROM products WHERE id = "' + productId + '"';

        db.query(deleteUserQuery, (err, result) => {
          if (err) {
            return response.status(500).send(err);
          }
          response.redirect('/');
        });

        break;

    }
  },
};

Creating Product Page

This is the main page of the app which contains a table to display a list of all the products, modal form to add and Update product record to the database.

<!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>Welcome | <%= page %></title>
<meta name="active-menu" content="<%= menuId %>">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="./assets/bootstrap/css/bootstrap.min.css">
</head>

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

  <section id="my-app">
    <div class="container">
      <div class="row my-5">
        <button 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 typeahead" spellcheck="false" autocomplete="off" name="typeahead" type="text" />
        </div>
      </div>
      <div class="row">
        <div class="table-responsive">
          <% include table %>
        </div>
      </div>
    </div>

  </section>
  <% include modal %>
  
      <script src="./assets/jquery/jquery.min.js"></script>
<script src="./assets/bootstrap/js/bootstrap.bundle.js"></script>
<script src="./assets/ejs/ejs.min.js"></script>
<script src="./assets/js/typeahead.js"></script>
<script>
  $(document).ready(function () {
    $('input.typeahead').typeahead({
      name: 'typeahead',
      remote: 'search?key=%QUERY',
      limit: 10
    });

    $('#myProductTable').on('click', '.edit', function () {
      var product_id = $(this).attr('data-id');
      var product_name = $(this).attr('data-name');
      var product_qty = $(this).attr('data-qty');
      var product_category = $(this).attr('data-category');
      $('#Productform form').attr('action', 'api/edit');
      $('#Productform').modal('show');
      $('#my-modal-title').html('Update Product');
      $('#Productform form .btn').html('Update Product');
      $('.product_name').val(product_name);
      $('.product_qty').val(product_qty);
      $('.product_category').val(product_category);
      $('.product_id').val(product_id);
    })
  });
</script>
</body>


</html>
<% if (products.length > 0) {%>
<table id="myProductTable" 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>
    <% products.forEach((product, index) => { %>
    <tr>
      <td><%= product.id %></td>
      <td><%= product.namex %></td>
      <td><%= product.category %></td>
      <td><%= product.qty %></td>
      <% if (product.qty > 10) {%>
      <td><span class="badge badge-success">In Stock</span></td>
      <% } else if (product.qty <= 10 && product.qty > 0) { %>
      <td><span class="badge badge-primary">ALmost Sold Out</span></td>
      <% } else { %>
      <td><span class="badge badge-warning">Out Of Stock</span></td>
      <% } %>
      <td>
        <div class=" buttons text-center">
          <a href="javascript:void(0);" data-id="<%= product.id %>" data-name="<%= product.namex %>"
            data-category="<%= product.category %>" data-qty="<%= product.qty %>"
            class="btn btn-sm btn-info edit">Edit</a>
          <a href="/api/<%= product.id %>/delete" class="btn btn-sm btn-danger delete">Delete</a>
        </div>
      </td>
    </tr>
    <% }) %>
  </tbody>
</table>
<% } else { %>
<p class="text-center">No product found...</p>
<% } %>

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!

Categories
JavaScript Templating PHP

Minimal Templating with Templater.js

In this application you’ll learn how display data in a table with PHP and MySQL using TemplaterJS it is a tiny JS library for minimal templating.

First we will Create 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

After creating the table, we need to setup your database connection copy and paste the code below in order to connect to the MySQL database server. Let’s create and name it to “database.php” and put the following code inside it.

<?php
    class Database{ 
        private $db_host = 'localhost';
        private $db_name = 'db_angular_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

<?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;
          }
?>

Creating Simple 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">
</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 py-5">
        <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>
              </tr>
            </thead>
            <tbody id="get_product">

            </tbody>
          </table>
        </div>
      </div>
    </div>
  </section>

  <script src="./assets/jquery/jquery.min.js"></script>
  <script src="./assets/templater/templater.min.js"></script>
  <script src="./assets/js/script.js"></script>

  <script id="product_list_data" type="text/template">
    <tr>
      <td>{{ id }}</td>
      <td>{{ namex }}</td>
      <td>{{ category }}</td>
      <td>{{ qty }}</td>
      <td><span class="badge badge-default">{{ stock }}</span></td>
    </tr>
  </script>

</body>

</html>

Ajax Request function

$(document).ready(function () {
	var tmpl = $("#product_list_data").html();
	$.ajax({
		method: "POST",
		url: "./library/api.php",
		dataType: 'json',
		contentType: 'application/json',
		data: JSON.stringify({ "action": "get_product_list" }),
		success: function (prodData) {
			$.each(prodData, function (i, product) {
				const template = templater(tmpl);
				$('#get_product').append(template(product));
			});
		}
	});
})

Download the complete source code. 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!

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!

Categories
AngularJS JavaScript Templating PHP

AngularJS PHP CRUD using Web API

I would like to share my CRUD (Create, Read, Update and Delete) using Web API and AngularJS 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_angular_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_angular_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":
          
            $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 namex='$name'");
                  $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 namex='$name'");
                  $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":
        
            $name=$data->name;

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

Angular Javascript code for register module and create controller.

var app = angular.module("Module_Product", [])

	.controller("Controller_Product", function ($scope, $http, ) {

		/* Load Product*/
		$scope.fetchData = function () {
			$http({
				method: "POST",
				url: "./library/api.php",
				dataType: 'json',
				contentType: 'application/json',
				data: JSON.stringify({ "action": "get_product_list" }),
			}).then(function success(response) {
				$scope.products = response.data;
			});
		};


		/* Insert/Update Product */
		$scope.addData = function () {
			$scope.btnName = "SAVE";
			$scope.formTitle = 'ADD PRODUCT';
			$scope.name = "";
			$scope.category = "";
			$scope.qty = "";
			$scope.stock = "";
		}

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

		$scope.InsertUpdateData = function () {
			if ($scope.name == null || $scope.category == null || $scope.qty == null) {
				alert("Please complete the required fields");
			} else {
				$http({
					method: "POST",
					url: "./library/api.php",
					dataType: 'json',
					contentType: 'application/json',
					data: JSON.stringify({ id: $scope.id, name: $scope.name, category: $scope.category, qty: $scope.qty, stock: $scope.stock, btnName: $scope.btnName, "action": "AddUpdate" }),
				}).then(function success(response) {
					$scope.name = "";
					$scope.category = "";
					$scope.qty = "";
					$scope.stock = "";
					$scope.fetchData();
					if ($scope.btnName == 'SAVE') {
						let message = "Product was successfully Added";
						$scope.flashMessage(message, 'success');
					} else if ($scope.btnName == 'UPDATE') {
						let message = "Product was successfully Updated";
						$scope.flashMessage(message, 'success');
					}
				});
			}
		}

		/* Delete Data*/
		$scope.deleteProduct = function (name) {
			if (confirm("Are you sure you want to remove it?")) {
				$http({
					method: "POST",
					url: "./library/api.php",
					data: { 'name': name, 'action': 'Delete' }
				}).then(function success(response) {
					$scope.success = true;
					$scope.error = false;
					$scope.fetchData();
					let message = "Product was successfully deleted";
					$scope.flashMessage(message, 'success');
				});

			}
		}

		/*Flash Message*/
		$scope.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',
				});
			}
		}

	});

Creating Product Page

<!DOCTYPE html>
<html lang="en" ng-app="Module_Product">

<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 ng-controller="Controller_Product">

  <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>
    <div class="container" ng-init="fetchData()">
      <div class="row my-5">
        <button ng-click="addData();" 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" ng-model="searchKeyword" 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 ng-repeat="product in products | filter: searchKeyword">
                <!-- <tr ng-repeat="product in products track by $index"> -->
                <td>{{$index+1}}</td>
                <td>{{product.namex}}</td>
                <td>{{product.category}}</td>
                <td>{{product.qty}}</td>
                <td><span class="badge badge-primary" ng-class="{warning: product.qty < 1}"> {{product.stock}}</span>
                </td>
                <td>
                  <div class=" buttons text-center">
                    <button class="btn btn-primary btn-edit btn-sm"
                      ng-click="update_data(product.id, product.namex, product.category, product.qty)" type="button"
                      class="btn btn-info btn-sm" data-toggle="modal" data-target="#Productform">Edit</button>
                    <button class="btn btn-danger btn-delete btn-sm"
                      ng-click="deleteProduct(product.namex);">Delete</button>
                  </div>
                </td>
              </tr>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </section>

  <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>
        <div class="modal-body">
          <form>
            <input type="hidden" ng-model="id">
            <div class="form-group">
              <label for="my-input">Product</label>
              <input class="form-control" type="text" ng-model="name" placeholder="Product Name" />
            </div>
            <div class="form-group">
              <label for="my-input">Category</label>
              <input class="form-control" type="text" ng-model="category" placeholder="Category" />
            </div>
            <div class="form-group">
              <label for="my-input">Quantity</label>
              <input class="form-control" type="text" ng-model="qty" placeholder="Quantity" />
            </div>
            <input data-dismiss="modal" name="insert" type="submit" class="btn btn-primary btn-add my-2"
              ng-click="InsertUpdateData()" value="{{btnName}}" />
          </form>
        </div>
      </div>
    </div>
  </div>

  <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/angular/angular.min.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!

Categories
JavaScript Templating PHP

Ajax request with Handlebars.js

In this application you’ll learn how use Handlebars.js to render template and display data in a table with PHP and MySQL.

First get the latest file for handlebars here https://www.npmjs.com/package/handlebars

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 12, 2020 at 01:28 PM
-- 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_product`
--

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

--
-- Table structure for table `tbl_products`
--

CREATE TABLE `tbl_products` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `sku` varchar(100) NOT NULL,
  `price` varchar(50) NOT NULL,
  `qty` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `availability` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_products`
--

INSERT INTO `tbl_products` (`id`, `name`, `sku`, `price`, `qty`, `description`, `availability`) VALUES
(4, 'Second Item', 'sku-1234', '2000', '456', 'Loerem ipsum ', '120'),
(48, 'First Item', 'sku-122', '20', '1000', 'lorem ipsumssss', '0'),
(49, 'Third item', 'sku-55', '800', '6', 'Sana All', '20');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_products`
--
ALTER TABLE `tbl_products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

After creating the table, we need to setup your database connection copy and paste the code below in order to connect to the MySQL database server. Let’s create and name it to “db_conn.php” and put the following code inside it.

<?php 
    $conn=mysqli_connect("localhost", "root", "", "db_product");

        if(!$conn){
            die("Error: Failed To Connect Database!");
        }
?>

Creating Simple Page

First we will create simple page for our application it contains a script template to display records in a table from the “tbl_products” database table. Copy and paste the code below

<!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/bootstrap.min.css">
  <style>
  footer {
    position: absolute;
    width: 100%;
    bottom: 0;
  }
  </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>
  <main class="py-5">
    <script id="product_list_data" type="text/template">
      <tr>
        <td>{{id}}</td>
        <td>{{name}}</td>
        <td>{{sku}}</td>
        <td>{{price}}</td>
        <td>{{qty}}</td>
        <td>{{description}}</td>
        <td>{{availability}}</td>
      </tr>
  </script>

    <section>
      <div class="container">
        <table class="table">
          <table id='load-data' class='table table-striped table-bordered' style='width:100%'>
            <thead class="thead-dark">
              <tr>
                <th>#</th>
                <th>Product Name</th>
                <th>SKU</th>
                <th>Amount</th>
                <th>Quantity</th>
                <th>Description</th>
                <th>Availability</th>
              </tr>
            </thead>
            <tbody id="get_product">
            </tbody>
          </table>
        </table>
      </div>
    </section>
  </main>
  <footer class="text-center py-2 bg-light">
    © 2020 Your Site
  </footer>
  <script src="./assets/jquery/jquery.min.js"></script>
  <script src="./assets/handlebars/handlebars.min.js"></script>
  <script src="./assets/js/render.js"></script>
</body>

</html>

ajax request

$(document).ready(function () {
  var template = $("#product_list_data").html();
  $.ajax({
    url: 'product_list.php',
    dataType: "json",
    success: function (prodData) {

      $.each(prodData, function (i, product) {
        var html = Handlebars.compile(template);
        $('#get_product').append(html(product));
      });

    }
  });
})

Copy and paste the code below and save it as product_list.php. This code will simply retrieve the records from the “tbl_products” table from database and it will return as JSON.

<?php
	require_once 'db_con.php';
	$bacth = array();

	$queryretrieve=mysqli_query($conn,"SELECT * FROM tbl_products");
	$n = mysqli_num_rows($queryretrieve);

	if ($n!=0) {
		while($result = mysqli_fetch_array($queryretrieve)){
			$bacth[] = $result;
	} 
	echo json_encode($bacth);
	}
?>

Download the complete source code. 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!

Categories
JavaScript Templating PHP

Ajax request with Mustache.js

In this application you’ll learn how use mustache.js to render mustache template and display data in a table with PHP and MySQL.

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 12, 2020 at 01:28 PM
-- 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_product`
--

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

--
-- Table structure for table `tbl_products`
--

CREATE TABLE `tbl_products` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `sku` varchar(100) NOT NULL,
  `price` varchar(50) NOT NULL,
  `qty` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `availability` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_products`
--

INSERT INTO `tbl_products` (`id`, `name`, `sku`, `price`, `qty`, `description`, `availability`) VALUES
(4, 'Second Item', 'sku-1234', '2000', '456', 'Loerem ipsum ', '120'),
(48, 'First Item', 'sku-122', '20', '1000', 'lorem ipsumssss', '0'),
(49, 'Third item', 'sku-55', '800', '6', 'Sana All', '20');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_products`
--
ALTER TABLE `tbl_products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

After creating the table, we need to setup your database connection copy and paste the code below in order to connect to the MySQL database server. Let’s create and name it to “db_conn.php” and put the following code inside it.

<?php 
    $conn=mysqli_connect("localhost", "root", "", "db_product");

        if(!$conn){
            die("Error: Failed To Connect Database!");
        }
?>

Creating Simple Page

First we will create simple page for our application it contains a data table showing the records from the “tbl_products” database table. We’ll include mustache Autoloader it require mustache to render our templates.

 <?php 
include('src/mustache/Autoloader.php');
Mustache_Autoloader::register();
$entry = new Mustache_Engine;
$product_list_template = file_get_contents('template/product_list.mustache');
?>

 <!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/bootstrap.min.css">
   <style>
   footer {
     position: absolute;
     width: 100%;
     bottom: 0;
   }
   </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>
   <main class="py-5">
     <section>
       <div class="container">
         <table class="table">
           <table id='load-data' class='table table-striped table-bordered' style='width:100%'>
             <thead class="thead-dark">
               <tr>
                 <th>#</th>
                 <th>Product Name</th>
                 <th>SKU</th>
                 <th>Amount</th>
                 <th>Quantity</th>
                 <th>Description</th>
                 <th>Availability</th>
               </tr>
             </thead>
             <tbody id="get_product">

             </tbody>
           </table>

           <?php echo $entry->render($product_list_template); ?>
         </table>
       </div>
     </section>
   </main>
   <footer class="text-center py-2 bg-light">
     © 2020 Your Site
   </footer>
   <script src="./assets/jquery/jquery.min.js"></script>
   <script src="./assets/mustache/js/mustache.min.js"></script>
   <script src="./assets/js/render.js"></script>
 </body>

 </html>

This is a shorthand Ajax function

$(document).ready(function () {
  var template = $("#product_list_data").html();
  $(function () {
    $.getJSON('product_list.php',
      function (prodData) {

        $.each(prodData, function (i, product) {
          var html = Mustache.render(template, product);
          $('#get_product').append(html);
        });

      });
  });
})

which is equal to

$(document).ready(function () {
  var template = $("#product_list_data").html();
  $.ajax({
    url: 'product_list.php',
    dataType: "json",
    success: function (prodData) {

      $.each(prodData, function (i, product) {
        var html = Mustache.render(template, product);
        $('#get_product').append(html);
      });

    }
  });
})

Copy and paste the code below and save it as product_list.php. This code will simply retrieve the records from the “tbl_products” table from database and it will return to JSON.

<?php
	require_once 'db_con.php';
	$bacth = array();

	$queryretrieve=mysqli_query($conn,"SELECT * FROM tbl_products");
	$n = mysqli_num_rows($queryretrieve);

	if ($n!=0) {
		while($result = mysqli_fetch_array($queryretrieve)){
			$bacth[] = $result;
	} 
	echo json_encode($bacth);
	}
?>

Download the complete source code. 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!