☰ ☰

PHP - Table class

1. Table class

A table class is implemented to keep track of the contents of the fields of a record in a database table.
The contents of the fields are temporarily stored in the properties. For convenience, the property names are given the same name as the field names in the table.
The constructor method chooses, depending on the number of arguments, whether an array of values or individual values are passed.
A method is provided for the standard actions: add, change, remove.
The other methods can be used to request a list, search for a record or to count the number of records.

<?php
// category.php
class category {
	public $id;
	public $name;
	public $active;

	public function __construct($id="",$name="",$active="") {
		$numargs = func_num_args();
		if ($numargs == 1) {
			$rec = func_get_arg(0);
			$this->id = $rec['id'];
			$this->name = $rec['name'];
			$this->active = $rec['active'];
		} else {
			$this->id = $id;
			$this->name = $name;
			$this->active = $active;
		}
	}	
	public function add($db) {
		try {
			$sql = "INSERT INTO category";
			$sql .= " (id,name,active)";
			$sql .= " VALUES (:id,:name,:active);";
			$values = array(':id'=>null,':name'=>$this->name,':active'=>$this->active);
			$result = $db->query_prepared($sql,$values);
			return $result;
		}
		catch(PDOException $e)
		{
			return "error";
		}
	}	
	public function change($db) {
		try {
			$sql = "UPDATE category";
			$sql .= " SET id=:id,name=:name,active=:active";
			$sql .= " WHERE id=:id;";
			$values = array(':id'=>$this->id,':name'=>$this->name,':active'=>$this->active);
			$result = $db->query_prepared($sql,$values);
			return $result;
		}
		catch(PDOException $e)
		{
			return "error";
		}
	}	
	public function remove($db) {
		try {
			$sql = "DELETE FROM category";
			$sql .= " WHERE id=:id;";
			$values = array(':id'=>$this->id);
			$result = $db->query_prepared($sql,$values);
			return $result;
		}
		catch(PDOException $e)
		{
			return "error";
		}
	}
    public function count_records($db) {
		$sql = "SELECT COUNT(id) FROM category";
		$result = $db->select_query($sql);
		$rs = $result->fetch();
		return $rs[0];		  
    }	
	public function all_records($db) {
		$list = [];
		$sql = "SELECT * FROM category order by name";
		$result = $db->select_query($sql);
		foreach($result->fetchAll() as $rec) {
			$list[] = new category($rec);
		}
		return $list;
    }
	public function all_active_records($db) {
		$list = [];
		$sql = "SELECT * FROM category WHERE active = 1 order by name";
		$result = $db->select_query($sql);
		foreach($result->fetchAll() as $rec) {
			$list[] = new category($rec);
		}
		return $list;
    }
	public function all_records_sorted_and_paging($db,$sort,$start_record,$records_per_page) {
		$list = [];
		$sql = "SELECT * FROM category ORDER BY :sort LIMIT :start_record,:records_per_page";
		$values = array(':sort'=>$sort,':start_record'=>$start_record,':records_per_page'=>$records_per_page);
		$result = $db->query_prepared($sql,$values);
		foreach($result->fetchAll() as $rec) {
			$list[] = new category($rec);
		}
		return $list;
	}
	public function search($id,$db) {
		$sql = "SELECT * FROM category WHERE id = :id";
		$id = intval($id);
		$values = array(':id'=>$id);
		$result = $db->query_prepared($sql,$values);
		$rec = $result->fetch();
		return new category($rec);	  
    }
}

2. Create a list

In this first example, the table class is used together with the database class to display a list of active records from the 'category' table.

// make a list using the category table class and the database class
require("database.php");
if (!isset($db)) $db = new database();
require "category.php";
$catobj = new category();
$categories = $catobj->all_active_records($db);
foreach($categories as $objcat) {
	// category_form.php will use the record id for changing or removing the record
	echo "<a href='category_form.php?id=".$objcat->id."&action=change'>change</a> ";
	echo "<a href='category_form.php?id=".$objcat->id."&action=remove'>remove</a> ";
	echo $objcat->id . " " . $objcat->name . " " . $objcat->active . "<br>";	
}

3. Fill in the form

This second example shows how to search for a record (using an 'id' field from the url).
The result of the search contains an object of the table class (e.g. $category is an object of the category class).
In the form, the properties of the 'category' object are entered in the entry fields if a 'category' object exists.

<!doctype html>
<html>
<head>
<meta name='viewport' content='width=device-width,initial-scale=1,maximum-scale=1'>
<link rel='stylesheet' type='text/css' href='w3.css'>
</head>
<body>
<?php
require("database.php");
if (!isset($db)) $db = new database();
require "category.php";
$catobj = new category();
$action = "add";
$id = "";
if (isset($_GET['id']) && isset($_GET['action'])) {
	$category = $catobj->search(htmlspecialchars($_GET['id']),$db);
	$action = htmlspecialchars($_GET['action']);
	$id = htmlspecialchars($_GET['id']);
}
?>
<table><tr><td style="vertical-align:top;">
<form class='w3-container' action='category_process.php' method='POST'>
<div class='w3-container' style='' id=''>
<h3>Admin category</h3>
<label class='w3-label w3-text-teal'><b>name *</b></label>
<input class='w3-input w3-border w3-light-grey' type='text' name='name' 
		value='<?php if (isset($category)) echo $category->name; ?>' required /><br>
<label class='w3-label w3-text-teal'><b>active *</b></label>
<input class='w3-input w3-border w3-light-grey' type='text' name='active' 
		value='<?php if (isset($category)) echo $category->active; ?>' required />
<label class='w3-left w3-text-teal'><b>* = mandatory.</b></label>
<label class='w3-label'><b>&nbsp;</b></label>
<div class='w3-center' style='' id=''>
<button class='w3-btn w3-blue-grey w3-round w3-center' type='submit'><?php echo $action; ?></button>
<button class='w3-btn w3-blue-grey w3-round w3-right' type='reset'>Reset</button>
</div>
</div>
<input class='' type='hidden' name='action' value='<?php echo $action; ?>' />
<input class=''

 type='hidden' name='id' value='<?php echo $id; ?>' />
</form>
</td>
</tr>
</table>

4. Counting number of records

In this example, the number of records is counted from the 'category' table.

// count the records in the category table
require("database.php");
if (!isset($db)) $db = new database();
require "category.php";
$catobj = new category();
$num_records = $catobj->count_records($db);
echo "Number of records in the category table = " . $num_records . "<br>";

5. Add a record

This example uses the 'add' method of the 'category' object to add a record.

// add a record using the table class and the database class
require("database.php");
if (!isset($db)) $db = new database();
require "category.php";
$catobj = new category();
$id = null;
$name = "Python";
$active = 0;
$category = new category($id,$name,$active);
$result	= $category->add($db);
if ($result->rowCount() > 0) {
	echo "Record with name = " . $name . " added.<br>";
} else {
	echo "error.<br>";
}

Pages

Paragraphs