Sunday, September 23, 2018

A Simple CRUD App in PHP

A Simple CRUD App in PHP
The “CRUD” means Create, Read, Update, Delete and is the most frequent functions used in daily operation. Here shows a simple CRUD app in PHP for reference. Let’s follow the methods explained in the article of my blog “Building PHP Web Apps Without Framework” and start to build a product list web site.

Seven Basic Operations

There are seven operations need to identified. They are:
  1. list_products: get and show all products.
  2. show_product: get product data for a specific prod_id.
  3. new_product: get a new form to enter product data.
  4. create_product: create a product and save to database.
  5. edit_product: get and edit product data for a specific prod_id.
  6. update_product update product data of a specific prod_id to database.
  7. delete_product delete product for a specific prod_id.
The operations 3 and 4 are consecutive steps, which mean when a user creates a product by finishing product data entry (operation 3), the system save product data to database at once (operation 4).
Likewise, operation 5 and 6 have the same situation. The system gets and shows the product data on the screen (operation 5), then it saves the data back to database while data has been edited by a user (operation 6).

The Flow Control in “index.php”

Here shows the flow control in “index.php” which variable “$op” decides the program flow.
switch ($op) {
  case 'show_product':
    show_product($prod_id);
    break;
  case 'new_product':
    new_product();
    break;
  case 'create_product':
    $prod_id = create_product();
    header("Location:index.php?op=show_product&prod_id={$prod_id}");
    exit;
    break;
  case 'edit_product':
    edit_product($prod_id);
    break;
  case 'update_product':
    update_product($prod_id);
    header("Location:index.php?op=show_product&prod_id={$prod_id}");
    exit;
    break;
  case 'delete_product':
    delete_products($prod_id);
    header("Location:index.php");
    exit;
    break;
  default:
    list_products($keyword);
    break;
}

The Flow Control in “index.html”

This is the flow control part in “index.html” where variable “$op” decides what partial html file should be included. It also shows the correct form and data needed.
{if $op == 'show_product'}
  {include file = "show_product.html"}
{elseif $op == 'new_product'}
  {include file = "new_product.html" }
{elseif $op == 'edit_product'}
  {include file = "edit_product.html"}
{else}
  {include file = "list_products.html" }
{/if}

The Sample Source Code ( also on github.com )

PHP code:

index.php
<?php
require_once "header.php";

// flow control
//
$op = isset($_REQUEST['op']) ? filter_var($_REQUEST['op'], FILTER_SANITIZE_MAGIC_QUOTES) : '';
$prod_id = isset($_REQUEST['prod_id']) ?  filter_var($_REQUEST['prod_id'], FILTER_SANITIZE_NUMBER_INT) : '';
$keyword = isset($_REQUEST['keyword']) ? filter_var($_REQUEST['keyword'], FILTER_SANITIZE_MAGIC_QUOTES) : '';
switch ($op) {
  case 'show_product':
    show_product($prod_id);
    break;

  case 'new_product':
    new_product();
    break;
  case 'create_product':
    $prod_id = create_product();
    header("Location:index.php?op=show_product&prod_id={$prod_id}");
    exit;
    break;
  case 'edit_product':
    edit_product($prod_id);
    break;
  case 'update_product':
    update_product($prod_id);
    header("Location:index.php?op=show_product&prod_id={$prod_id}");
    exit;
    break;
  case 'delete_product':
    delete_products($prod_id);
    header("Location:index.php");
    exit;
    break;
  default:
    list_products($keyword);
    break;
}

// output
//
require_once "footer.php";

// functions
//
// index: list all products
function list_products($keyword = "") {
  global $smarty, $mysqli;
  include_once "class/PageBar.php";

  $where = !empty($keyword) ? "where prod_name like '%{$keyword}%' or prod_desc like '%{$keyword}%'" : "";
  $sql = "SELECT * FROM products $where ORDER BY prod_start_date DESC";

  // PageBar 分頁工具
  //  _NUMBER_PER_PAGE:  每頁呈現幾筆資料
  //  _PAGE_LIMIT:       分頁工具呈現幾頁

  $PageBar = getPageBar($sql, _NUMBER_PER_PAGE, _PAGE_LIMIT);
  $bar     = $PageBar['bar'];
  $sql     = $PageBar['sql'];
  $total   = $PageBar['total'];

  $result  = $mysqli->query($sql) or die($mysqli->connect_error);
  $products  = $result->fetch_all(MYSQLI_ASSOC);

  $smarty->assign('products', $products);
  $smarty->assign('total', $total);
  $smarty->assign('bar', $bar);
}

// show product
function show_product($prod_id = '') {
  global $mysqli, $smarty;
  $sql = "SELECT * FROM products WHERE prod_id = '{$prod_id}'";
  $result = $mysqli->query($sql) or die($mysqli->connect_error);
  $product = $result->fetch_assoc();

  $smarty->assign('product', $product);
}

// new product
function new_product() {
  global $mysqli, $smarty;

  $product['prod_id']         = "";
  $product['prod_name']       = "";
  $product['prod_desc']       = "";
  $product['prod_price']      = "";
  $product['prod_counter']    = "";
  $product['prod_start_date'] = "";
  $smarty->assign('product', $product);
}


// create product
function create_product(){
  global $mysqli;

  if (empty($_POST['prod_name'])) {
    return;
  }

  $prod_name  = $mysqli->real_escape_string($_POST['prod_name']);
  $prod_desc  = $mysqli->real_escape_string($_POST['prod_desc']);
  $prod_price = $mysqli->real_escape_string($_POST['prod_price']);
  $prod_start_date  = date("Y-m-d H:i:s");

  $sql = "INSERT INTO products (prod_name, prod_desc, prod_price, prod_counter ,prod_start_date)
          VALUES ('{$prod_name}', '{$prod_desc}', '{$prod_price}', '0','{$prod_start_date}')";

  if ($mysqli->query($sql)) {
  } else {
    die($mysqli->connect_error);
  }
    $prod_id = $mysqli->insert_id;
    // save_prod_pic($prod_id);
  return $prod_id;
}

// edit product
function edit_product($prod_id) {
  global $mysqli, $smarty;

  if (empty($prod_id)) {
    return;
  }

  $sql = "SELECT * FROM products WHERE prod_id = {$prod_id}";
  $result = $mysqli->query($sql) or die($mysqli->connect_error);
  $product = $result->fetch_assoc();
  $smarty->assign('product', $product);
}

// update product
function update_product($prod_id) {
  global $mysqli;

  $prod_name  = $mysqli->real_escape_string($_POST['prod_name']);
  $prod_desc  = $mysqli->real_escape_string($_POST['prod_desc']);
  $prod_price = $mysqli->real_escape_string($_POST['prod_price']);
  $prod_start_date = date("Y-m-d H:i:s");

  $sql = "UPDATE products SET
      prod_name       = '{$prod_name}',
      prod_desc       = '{$prod_desc}',
      prod_price      = '{$prod_price}',
      prod_start_date = '{$prod_start_date}'
    WHERE prod_id = {$prod_id}";

  $mysqli->query($sql) or die($mysqli->connect_error);
}

// delete product
function delete_products($prod_id) {
  global $mysqli;
  $sql = "DELETE FROM products WHERE prod_id = {$prod_id}";
  $mysqli->query($sql) or die($mysqli->connect_error);
}

?>
header.php
<?php
  session_start();
  require_once "config.php";
  require_once "smarty/libs/Smarty.class.php";
  $smarty = new Smarty;

  $mysqli = new mysqli( _DBHOST, _DBUSER, _DBPASSWD, _DBNAME);
  if ($mysqli->connect_error){
    die ('DB Error: ('.$mysqli->connect_errno.' )'.$mysqli->connect_error);
  } else {
    $mysqli->set_charset('utf8');
  }
?>
footer.php
<?php
  $smarty->assign('shop_name', _SHOP_NAME);
  $smarty->assign('op', $op);
  $smarty->display('index.html');
?>
config.php
<?php
  define('_SHOP_NAME', 'CRUD-DEMO SHOP');
  define('_DBNAME',    'xxxxx');
  define('_DBUSER',    'xxxx');
  define('_DBPASSWD',  'xxxx');
  define('_DBHOST',    '127.0.0.1');

  // PageBar 分頁工具
  define('_NUMBER_PER_PAGE', 5 );    // 每頁呈現幾筆資料
  define('_PAGE_LIMIT', 10);         // 分頁工具呈現幾頁
?>

HTML files:

index.html
<!DOCTYPE html>
<html lang="zh-Hant">
  <head>
    <title>{$shop_name}</title>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
    <script src="https://cdn.ckeditor.com/ckeditor5/11.0.1/classic/ckeditor.js"></script>
  </head>
  <body>
    <div class="container">
      <nav class="navbar navbar-expand-lg navbar-light" style="background-color: #e3f2fd;" id="shop_head">
        <a class="navbar-brand" href="index.php">CRUD-DEMO</a>
        <div class="collapse navbar-collapse" id="navbarSupportedContent">
          <ul class="navbar-nav mr-auto">
            <li class="nav-item active">
              <a class="nav-link" href="index.php">首頁 <span class="sr-only">(current)</span></a>
            </li>
            <li class="nav-item dropdown">
              <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">功能表
              </a>
              <div class="dropdown-menu" aria-labelledby="navbarDropdown">
                <a class="dropdown-item" href="index.php?op=new_product">新增產品</a>
                <div class="dropdown-divider"></div>
                <a class="dropdown-item disabled" href="#">其他功能</a>
              </div>
            </li>
          </ul>
          <form class="form-inline my-2 my-lg-0" action="index.php" method="GET" role="form">
            <input class="form-control mr-sm-2" type="search" name="keyword" placeholder="請輸入關鍵字" aria-label="Search">
            <button class="btn btn-outline-success my-2 my-sm-0" type="submit">搜尋</button>
          </form>
        </div>
      </nav>

      <br/>
      <div id="shop_main" class="row">
        <div class="col-md-9 col-sm-8">
          <!-- Flow Control -->
          {if $op == 'show_product'}
            {include file = "show_product.html"}
          {elseif $op == 'new_product'}
            {include file = "new_product.html" }
          {elseif $op == 'edit_product'}
            {include file = "edit_product.html"}
          {else}
            {include file = "list_products.html" }
          {/if}
        </div>
      </div>

      <div id="shop_foot" >
        <div>地址：XX市XX區XX路XX巷XX號</div>
        <div>電話：(02)12345678</div>
        <div>版權所有 copyright © 2018 CRUD-DEMO SHOP. All Rights Reserved. </div>
      </div>
    </div>
  </body>
</html>
list_products.html
<h4>產品列表</h4>
  <table class="table table-hover">
    <thead>
      <tr>
        <th>產品名稱</th>
        <th>產品內容</th>
        <th>價格</th>
        <th>人氣</th>
      </tr>
    </thead>
    <tbody>
      {foreach from=$products item=product}
        <tr>
          <td><a href="index.php?op=show_product&prod_id={$product.prod_id}">{$product.prod_name}</a></td>
          <td>{$product.prod_desc}</td>
          <td>{$product.prod_price}</td>
          <td>{$product.prod_counter}</td>
        <tr>
      {/foreach}
    </tbody>
  </table>
<div class="container">
  共有 {$total} 件商品
  {$bar}
</div>
show_product.html
<div class="container">
  <div class="row">
    <div class="col-md-9 offset-md-2">
      <div class="row">
        <h2>{$product.prod_name}</h2>
      </div>
      <hr>
      <div class="row">
        <h4>{$product.prod_desc}</h4>
      </div>
      <div class="row">
        <h5>售價：</h5>
        <h5>新台幣 &nbsp</h5>
        <h4 style="color: red">{$product.prod_price}</h4>
        <h5>&nbsp 元整</h5>
      </div>
      <div class="row">
          <h5>人氣： &nbsp</h5>
          <h5 style="color: red">{$product.prod_counter}</h5>
      </div>
      <br/>
      <div class="row">
        <a href="index.php?op=edit_product&prod_id={$product.prod_id}" class="btn btn-warning">編輯產品</a>
        &nbsp;
        <a href="index.php?op=delete_product&prod_id={$product.prod_id}" class="btn btn-danger" >刪除產品</a>
      </div>
    </div>
  </div>
</div>
new_product.html
<div class="container">
  <h4>新增產品</h4>
</div>
<form class="form" action="index.php" method="post" class="form-horizontal" role="form" enctype="multipart/form-data">
  {include file = "product_form.html"}
  <div class="form-group">
    <div class="col-md-offset-2 col-md-10">
      <input type="hidden" name="op" value="create_product">
      <button type="submit" class="btn btn-primary">儲存產品資訊</button>
    </div>
  </div>
</form>
edit_product.html
<div class="container">
  <h4>編輯產品</h4>
</div>
<form class="form" action="index.php" method="post" class="form-horizontal" role="form" enctype="multipart/form-data">
  {include file = "product_form.html"}
  <div class="form-group">
    <div class="col-md-offset-2 col-md-10">
      <input type="hidden" name="op" value="update_product">
      <input type="hidden" name="prod_id" value="{$product.prod_id}">
      <button type="submit" class="btn btn-primary">儲存產品資訊</button>
    </div>
  </div>
</form>
product_form.html
<div class="form-group">
  <label class="col-md-2 control-label">產品名稱</label>
  <div class="col-md-10">
    <input type="text" class="form-control" name="prod_name" id="prod_name"
            placeholder="請輸入產品名稱" value="{$product.prod_name}" required>
  </div>
</div>
<div class="form-group">
  <label class="col-md-2 control-label">產品內容</label>
  <div class="col-md-10">
    <textarea class="form-control" name="prod_desc" id="prod_desc"
            placeholder="請輸入產品內容">{$product.prod_desc}</textarea>
  </div>
  <!--textarea using CKEditor 5 -->
  <script>
    ClassicEditor
      .create( document.querySelector( '#prod_desc' ) )
      .then( editor => {
        console.log( prod_desc );
      } )
      .catch( error => {
        console.error( prod_desc );
      } );
  </script>
</div>
<div class="form-group">
  <label class="col-md-2 control-label">產品價格</label>
  <div class="col-md-4">
    <input type="text" class="form-control" name="prod_price" id="prod_price"
            placeholder="請輸入產品價格" value="{$product.prod_price}" required>
  </div>
</div>
張貼者： 於
標籤：

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)