code-boxx/0-PHP-GROCERY.MD

## 0-PHP-GROCERY.MD

      
    Raw
  

              0-PHP-GROCERY.MD
            
          
    PHP MYSQL GROCERY LIST

https://code-boxx.com/grocery-list-php-mysql/
NOTES


Create a dummy database and import 1-items.sql.
Change the database settings in 2-lib-items.php to your own.
Launch 4a-grocery-list.html in the browser.

LICENSE

Copyright by Code Boxx
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  
## 1-items.sql
CREATE TABLE `items` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `qty` int(11) NOT NULL,
  `got` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `items`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `items`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

## 2-lib-items.php
<?php
class Items {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET ALL ITEMS
  function get () {
    $this->query("SELECT * FROM `items`");
    return $this->stmt->fetchAll();
  }

  // (E) ADD NEW ITEM
  function add ($name, $qty) {
    $this->query("INSERT INTO `items` (`name`, `qty`) VALUES (?,?)", [$name, $qty]);
    return true;
  }

  // (F) UPDATE ITEM STATUS
  function update ($got, $id) {
    $this->query("UPDATE `items` SET `got`=? WHERE `id`=?", [$got, $id]);
    return true;
  }

  // (G) DELETE ITEM
  function delete ($id) {
    $this->query("DELETE FROM `items` WHERE `id`=?", [$id]);
    return true;
  }
}

// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (I) NEW ITEMS OBJECT
$_ITEMS = new Items();

## 3-ajax-items.php
<?php
if ($_POST["req"]) {
  require "2-lib-items.php";
  switch ($_POST["req"]) {
  // (A) GET ALL ITEMS
  case "get":
    echo json_encode($_ITEMS->get());
    break;

  // (B) ADD ITEM
  case "add":
    $_ITEMS->add($_POST["name"], $_POST["qty"]);
    echo "OK";
    break;

  // (C) UPDATE ITEM STATUS
  case "update":
    $_ITEMS->update($_POST["got"], $_POST["id"]);
    echo "OK";
    break;

  // (D) DELETE ITEM
  case "delete":
    $_ITEMS->delete($_POST["id"]);
    echo "OK";
    break;
}}

## 4a-grocery-list.html
<!DOCTYPE html>
<html>
  <head>
    <title>Grocery List</title>
    <meta charset="utf-8">

    <!-- MATERIAL ICONS -->
    <!-- https://fonts.google.com/icons?icon.query=delete&icon.style=Filled&icon.set=Material+Icons -->
    <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    <script src="4b-grocery-list.js"></script>
    <link href="4c-grocery-list.css" rel="stylesheet">
  </head>
  <body>
    <div id="gro-wrap">
      <!-- (A) ADD ITEM -->
      <form id="gro-form">
        <input type="number" id="gro-qty" min="1" value="1" placeholder="Quantity" required disabled>
        <input type="text" id="gro-item" placeholder="Item Name" required disabled>
        <button id="gro-add" class="material-icons" disabled>add</button>
      </form>

      <!-- (B) ITEM LIST -->
      <div id="gro-list"></div>
    </div>
  </body>
</html>

## 4b-grocery-list.js
var glist = {
  // (A) SUPPORT FUNCTION - AJAX FETCH
  ajax : (data, onload) => {
    // (A1) FORM DATA
    var form = new FormData;
    for (let [k,v] of Object.entries(data)) { form.append(k, v); }

    // (A2) AJAX FETCH
    fetch("3-ajax-items.php", { method:"post", body:form })
    .then(res => res.text())
    .then(txt => {
      if (txt == "OK") { onload(); }
      else {
        try {
          var json = JSON.parse(txt);
          onload(json);
        } catch (err) {
          alert("AJAX fetch error");
          console.error(err, txt);
        }
      }
    })
    .catch(err => {
      alert("AJAX fetch error");
      console.error(err);
    });
  },

  // (B) INITIALIZE GROCERY LIST
  items : [], // current grocery list
  hqty : null, // html add quantity field
  hitem : null, // html add item field
  hlist : null, // html <div> grocery list
  init : () => {
    // (B1) GET HTML ELEMENTS
    glist.hqty = document.getElementById("gro-qty");
    glist.hitem = document.getElementById("gro-item");
    glist.hlist = document.getElementById("gro-list");

    // (B2) ENABLE FORM
    glist.hqty.disabled = false;
    glist.hitem.disabled = false;
    document.getElementById("gro-add").disabled = false;
    document.getElementById("gro-form").onsubmit = glist.add;

    // (B3) DRAW HTML GROCERY LIST
    glist.draw();
  },

  // (C) DRAW GROCERY LIST
  draw : () => glist.ajax({ req : "get" }, items => {
    // (C1) NO ITEMS
    if (items.length == 0) {
      glist.hlist.innerHTML = "<div class='item-row item-name'>No items found.</div>";
    }

    // (C2) DRAW ITEMS
    else {
      glist.hlist.innerHTML = "";
      for (let i of items) {
        // (C2-1) ITEM ROW
        let row = document.createElement("div");
        row.className = i.got=="1" ? "item-row yes" : "item-row no";
        glist.hlist.appendChild(row);

        // (C2-2) DELETE BUTTON
        let del = document.createElement("div");
        del.className = "item-del material-icons";
        del.innerHTML = "delete_forever";
        del.onclick = () => glist.delete(i.id);
        row.appendChild(del);

        // (C2-3) ITEM QUANTITY & NAME
        let name = document.createElement("div");
        name.innerHTML = `${i.qty} X ${i.name}`;
        name.className = "item-name";
        if (i.got=="1") { name.classList.add("item-got"); }
        row.appendChild(name);

        // (C2-4) ITEM ACCQUIRED
        let ok = document.createElement("button");
        ok.className = "item-ok material-icons";
        ok.innerHTML = i.got=="1" ? "done" : "clear";
        ok.onclick = () => glist.toggle(i.got=="1" ? 0 : 1, i.id);
        row.appendChild(ok);
      }
    }
  }),

  // (C) ADD NEW ITEM TO THE LIST
  add : () => {
    glist.ajax({
      req : "add",
      name : glist.hitem.value,
      qty : glist.hqty.value
    }, () => {
      glist.hqty.value = 1;
      glist.hitem.value = "";
      glist.draw();
    });
    return false;
  },

  // (D) TOGGLE ITEM ACCQUIRED STATUS
  toggle : (got, id) => glist.ajax({
    req : "update",
    got: got,
    id : id
  }, glist.draw),

  // (E) DELETE SELECTED ITEM
  delete : id => { if (confirm("Remove this item?")) {
    glist.ajax({
      req : "delete",
      id : id
    }, glist.draw);
  }}
};
window.addEventListener("load", glist.init);

## 4c-grocery-list.css
/* (A) ENTIRE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
#gro-wrap {
  padding: 10px;
  max-width: 500px;
  margin: 0 auto;
}

/* (B) ADD ITEM */
#gro-form, input, button {
  font-size: 16px;
  padding: 10px;
}
#gro-form {
  display: flex;
  border: 1px solid #e3e3e3;
  background: #f2f2f2;
}
button, input { border: 0; }
input[type=number], input[type=text] { margin-right: 5px; }
input[type=number] { width: 80px; }
input[type=text] { flex-grow: 1; }
button, input[type=button], input[type=submit] {
  cursor: pointer;
  color: #fff;
  background: #df4949;
}

/* (C) ITEMS LIST */
#gro-list { margin-top: 10px; }
.item-row {
  margin: 10px 0;
  display: flex;
  align-items: center;
}
.item-row.yes {
  border: 1px solid #68ff6d;
  background: #e2ffe0;
}
.item-row.no {
  border: 1px solid #ffc4c4;
  background: #fff2f2;
}
.item-ok { background: none; }
.item-row.yes .item-ok { color: #1fba1f; }
.item-row.no .item-ok { color: #ca1717; }
.item-del {
  padding: 10px;
  color: #a7a7a7;
  cursor: pointer;
}
.item-name {
  flex-grow: 1;
  padding: 0 10px;
}
	CREATE TABLE `items` (
	`id` bigint(20) NOT NULL,
	`name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
	`qty` int(11) NOT NULL,
	`got` tinyint(1) NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

	ALTER TABLE `items`
	ADD PRIMARY KEY (`id`);

	ALTER TABLE `items`
	MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
	<?php
	class Items {
	// (A) CONSTRUCTOR - CONNECT TO DATABASE
	private $pdo = null;
	private $stmt = null;
	public $error = null;
	function __construct () {
	$this->pdo = new PDO(
	"mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
	DB_USER, DB_PASSWORD, [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
	]);
	}

	// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
	function __destruct () {
	if ($this->stmt !== null) { $this->stmt = null; }
	if ($this->pdo !== null) { $this->pdo = null; }
	}

	// (C) HELPER FUNCTION - RUN SQL QUERY
	function query ($sql, $data=null) : void {
	$this->stmt = $this->pdo->prepare($sql);
	$this->stmt->execute($data);
	}

	// (D) GET ALL ITEMS
	function get () {
	$this->query("SELECT * FROM `items`");
	return $this->stmt->fetchAll();
	}

	// (E) ADD NEW ITEM
	function add ($name, $qty) {
	$this->query("INSERT INTO `items` (`name`, `qty`) VALUES (?,?)", [$name, $qty]);
	return true;
	}

	// (F) UPDATE ITEM STATUS
	function update ($got, $id) {
	$this->query("UPDATE `items` SET `got`=? WHERE `id`=?", [$got, $id]);
	return true;
	}

	// (G) DELETE ITEM
	function delete ($id) {
	$this->query("DELETE FROM `items` WHERE `id`=?", [$id]);
	return true;
	}
	}

	// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
	define("DB_HOST", "localhost");
	define("DB_NAME", "test");
	define("DB_CHARSET", "utf8mb4");
	define("DB_USER", "root");
	define("DB_PASSWORD", "");

	// (I) NEW ITEMS OBJECT
	$_ITEMS = new Items();
	<?php
	if ($_POST["req"]) {
	require "2-lib-items.php";
	switch ($_POST["req"]) {
	// (A) GET ALL ITEMS
	case "get":
	echo json_encode($_ITEMS->get());
	break;

	// (B) ADD ITEM
	case "add":
	$_ITEMS->add($_POST["name"], $_POST["qty"]);
	echo "OK";
	break;

	// (C) UPDATE ITEM STATUS
	case "update":
	$_ITEMS->update($_POST["got"], $_POST["id"]);
	echo "OK";
	break;

	// (D) DELETE ITEM
	case "delete":
	$_ITEMS->delete($_POST["id"]);
	echo "OK";
	break;
	}}
	<!DOCTYPE html>
	<html>
	<head>
	<title>Grocery List</title>
	<meta charset="utf-8">

	<!-- MATERIAL ICONS -->
	<!-- https://fonts.google.com/icons?icon.query=delete&icon.style=Filled&icon.set=Material+Icons -->
	<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
	<script src="4b-grocery-list.js"></script>
	<link href="4c-grocery-list.css" rel="stylesheet">
	</head>
	<body>
	<div id="gro-wrap">
	<!-- (A) ADD ITEM -->
	<form id="gro-form">
	<input type="number" id="gro-qty" min="1" value="1" placeholder="Quantity" required disabled>
	<input type="text" id="gro-item" placeholder="Item Name" required disabled>
	<button id="gro-add" class="material-icons" disabled>add</button>
	</form>

	<!-- (B) ITEM LIST -->
	<div id="gro-list"></div>
	</div>
	</body>
	</html>
	var glist = {
	// (A) SUPPORT FUNCTION - AJAX FETCH
	ajax : (data, onload) => {
	// (A1) FORM DATA
	var form = new FormData;
	for (let [k,v] of Object.entries(data)) { form.append(k, v); }

	// (A2) AJAX FETCH
	fetch("3-ajax-items.php", { method:"post", body:form })
	.then(res => res.text())
	.then(txt => {
	if (txt == "OK") { onload(); }
	else {
	try {
	var json = JSON.parse(txt);
	onload(json);
	} catch (err) {
	alert("AJAX fetch error");
	console.error(err, txt);
	}
	}
	})
	.catch(err => {
	alert("AJAX fetch error");
	console.error(err);
	});
	},

	// (B) INITIALIZE GROCERY LIST
	items : [], // current grocery list
	hqty : null, // html add quantity field
	hitem : null, // html add item field
	hlist : null, // html <div> grocery list
	init : () => {
	// (B1) GET HTML ELEMENTS
	glist.hqty = document.getElementById("gro-qty");
	glist.hitem = document.getElementById("gro-item");
	glist.hlist = document.getElementById("gro-list");

	// (B2) ENABLE FORM
	glist.hqty.disabled = false;
	glist.hitem.disabled = false;
	document.getElementById("gro-add").disabled = false;
	document.getElementById("gro-form").onsubmit = glist.add;

	// (B3) DRAW HTML GROCERY LIST
	glist.draw();
	},

	// (C) DRAW GROCERY LIST
	draw : () => glist.ajax({ req : "get" }, items => {
	// (C1) NO ITEMS
	if (items.length == 0) {
	glist.hlist.innerHTML = "<div class='item-row item-name'>No items found.</div>";
	}

	// (C2) DRAW ITEMS
	else {
	glist.hlist.innerHTML = "";
	for (let i of items) {
	// (C2-1) ITEM ROW
	let row = document.createElement("div");
	row.className = i.got=="1" ? "item-row yes" : "item-row no";
	glist.hlist.appendChild(row);

	// (C2-2) DELETE BUTTON
	let del = document.createElement("div");
	del.className = "item-del material-icons";
	del.innerHTML = "delete_forever";
	del.onclick = () => glist.delete(i.id);
	row.appendChild(del);

	// (C2-3) ITEM QUANTITY & NAME
	let name = document.createElement("div");
	name.innerHTML = `${i.qty} X ${i.name}`;
	name.className = "item-name";
	if (i.got=="1") { name.classList.add("item-got"); }
	row.appendChild(name);

	// (C2-4) ITEM ACCQUIRED
	let ok = document.createElement("button");
	ok.className = "item-ok material-icons";
	ok.innerHTML = i.got=="1" ? "done" : "clear";
	ok.onclick = () => glist.toggle(i.got=="1" ? 0 : 1, i.id);
	row.appendChild(ok);
	}
	}
	}),

	// (C) ADD NEW ITEM TO THE LIST
	add : () => {
	glist.ajax({
	req : "add",
	name : glist.hitem.value,
	qty : glist.hqty.value
	}, () => {
	glist.hqty.value = 1;
	glist.hitem.value = "";
	glist.draw();
	});
	return false;
	},

	// (D) TOGGLE ITEM ACCQUIRED STATUS
	toggle : (got, id) => glist.ajax({
	req : "update",
	got: got,
	id : id
	}, glist.draw),

	// (E) DELETE SELECTED ITEM
	delete : id => { if (confirm("Remove this item?")) {
	glist.ajax({
	req : "delete",
	id : id
	}, glist.draw);
	}}
	};
	window.addEventListener("load", glist.init);
	/* (A) ENTIRE PAGE */
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	#gro-wrap {
	padding: 10px;
	max-width: 500px;
	margin: 0 auto;
	}

	/* (B) ADD ITEM */
	#gro-form, input, button {
	font-size: 16px;
	padding: 10px;
	}
	#gro-form {
	display: flex;
	border: 1px solid #e3e3e3;
	background: #f2f2f2;
	}
	button, input { border: 0; }
	input[type=number], input[type=text] { margin-right: 5px; }
	input[type=number] { width: 80px; }
	input[type=text] { flex-grow: 1; }
	button, input[type=button], input[type=submit] {
	cursor: pointer;
	color: #fff;
	background: #df4949;
	}

	/* (C) ITEMS LIST */
	#gro-list { margin-top: 10px; }
	.item-row {
	margin: 10px 0;
	display: flex;
	align-items: center;
	}
	.item-row.yes {
	border: 1px solid #68ff6d;
	background: #e2ffe0;
	}
	.item-row.no {
	border: 1px solid #ffc4c4;
	background: #fff2f2;
	}
	.item-ok { background: none; }
	.item-row.yes .item-ok { color: #1fba1f; }
	.item-row.no .item-ok { color: #ca1717; }
	.item-del {
	padding: 10px;
	color: #a7a7a7;
	cursor: pointer;
	}
	.item-name {
	flex-grow: 1;
	padding: 0 10px;
	}