Introduction
Hello, today we will talk about how to use the server side in the Datatables using PHP and MYSQL in simple steps.
YouTube Video
Files Structure
libraries: A folder containing the important libraries used .
ssp.class.php: library file for datatables server side.
validator.class.php: library for validation.
add.php: add form design.
update.php: update form design.
crud.php: File containing CRUD code using PDO.
index.php: The crud show homepage.
server_side: It contains the main codes for creating datatables server side processing using the ssp library.
Steps:
Step 1: import my data to database or create database
datatables.sql:
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Aug 16, 2023 at 01:46 PM
-- Server version: 8.0.30
-- PHP Version: 8.1.10
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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: `crud`
--
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int NOT NULL,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `username`, `email`) VALUES
(62, 'admin', 'admin@gmail.com'),
(64, 'blog', 'blog@gmail.com'),
(65, 'manager', 'manager@gmail.com'),
(66, 'user', 'user@gmail.com');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;
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 */;
Step 2: copy paste importent libraries to folder libraries.
ssp.class.php:
<?php
/*
* Helper functions for building a DataTables server-side processing SQL query
*
* The static functions in this class are just helper functions to help build
* the SQL used in the DataTables demo server-side processing scripts. These
* functions obviously do not represent all that can be done with server-side
* processing, they are intentionally simple to show how it works. More complex
* server-side processing operations will likely require a custom script.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
// Please Remove below 4 lines as this is use in Datatatables test environment for your local or live environment please remove it or else it will not work
$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/pdo.php';
if ( is_file( $file ) ) {
include( $file );
}
class SSP {
/**
* Create the data output array for the DataTables rows
*
* @param array $columns Column information array
* @param array $data Data from the SQL get
* @return array Formatted data in a row based format
*/
static function data_output ( $columns, $data )
{
$out = array();
for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
$row = array();
for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
$column = $columns[$j];
// Is there a formatter?
if ( isset( $column['formatter'] ) ) {
if(empty($column['db'])){
$row[ $column['dt'] ] = $column['formatter']( $data[$i] );
}
else{
$row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
}
}
else {
if(!empty($column['db'])){
$row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
}
else{
$row[ $column['dt'] ] = "";
}
}
}
$out[] = $row;
}
return $out;
}
/**
* Database connection
*
* Obtain an PHP PDO connection from a connection details array
*
* @param array $conn SQL connection details. The array should have
* the following properties
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource PDO connection
*/
static function db ( $conn )
{
if ( is_array( $conn ) ) {
return self::sql_connect( $conn );
}
return $conn;
}
/**
* Paging
*
* Construct the LIMIT clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL limit clause
*/
static function limit ( $request, $columns )
{
$limit = '';
if ( isset($request['start']) && $request['length'] != -1 ) {
$limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
}
return $limit;
}
/**
* Ordering
*
* Construct the ORDER BY clause for server-side processing SQL query
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @return string SQL order by clause
*/
static function order ( $request, $columns )
{
$order = '';
if ( isset($request['order']) && count($request['order']) ) {
$orderBy = array();
$dtColumns = self::pluck( $columns, 'dt' );
for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
// Convert the column index into the column data property
$columnIdx = intval($request['order'][$i]['column']);
$requestColumn = $request['columns'][$columnIdx];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['orderable'] == 'true' ) {
$dir = $request['order'][$i]['dir'] === 'asc' ?
'ASC' :
'DESC';
$orderBy[] = '`'.$column['db'].'` '.$dir;
}
}
if ( count( $orderBy ) ) {
$order = 'ORDER BY '.implode(', ', $orderBy);
}
}
return $order;
}
/**
* Searching / Filtering
*
* Construct the WHERE clause for server-side processing SQL query.
*
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here performance on large
* databases would be very poor
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @param array $bindings Array of values for PDO bindings, used in the
* sql_exec() function
* @return string SQL where clause
*/
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$str = $request['search']['value'];
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
if ( $requestColumn['searchable'] == 'true' ) {
if(!empty($column['db'])){
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
}
// Individual column filtering
if ( isset( $request['columns'] ) ) {
for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search( $requestColumn['data'], $dtColumns );
$column = $columns[ $columnIdx ];
$str = $requestColumn['search']['value'];
if ( $requestColumn['searchable'] == 'true' &&
$str != '' ) {
if(!empty($column['db'])){
$binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
$columnSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
}
// Combine the filters into a single string
$where = '';
if ( count( $globalSearch ) ) {
$where = '('.implode(' OR ', $globalSearch).')';
}
if ( count( $columnSearch ) ) {
$where = $where === '' ?
implode(' AND ', $columnSearch) :
$where .' AND '. implode(' AND ', $columnSearch);
}
if ( $where !== '' ) {
$where = 'WHERE '.$where;
}
return $where;
}
/**
* Perform the SQL queries needed for an server-side processing requested,
* utilising the helper functions of this class, limit(), order() and
* filter() among others. The returned array is ready to be encoded as JSON
* in response to an SSP request, or can be modified if needed before
* sending back to the client.
*
* @param array $request Data sent to server by DataTables
* @param array|PDO $conn PDO connection resource or connection parameters array
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @return array Server-side processing response array
*/
static function simple ( $request, $conn, $table, $primaryKey, $columns )
{
$bindings = array();
$db = self::db( $conn );
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`"
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
/**
* The difference between this method and the `simple` one, is that you can
* apply additional `where` conditions to the SQL queries. These can be in
* one of two forms:
*
* * 'Result condition' - This is applied to the result set, but not the
* overall paging information query - i.e. it will not effect the number
* of records that a user sees they can have access to. This should be
* used when you want apply a filtering condition that the user has sent.
* * 'All condition' - This is applied to all queries that are made and
* reduces the number of records that the user can access. This should be
* used in conditions where you don't want the user to ever have access to
* particular records (for example, restricting by a login id).
*
* @param array $request Data sent to server by DataTables
* @param array|PDO $conn PDO connection resource or connection parameters array
* @param string $table SQL table to query
* @param string $primaryKey Primary key of the table
* @param array $columns Column information array
* @param string $whereResult WHERE condition to apply to the result set
* @param string $whereAll WHERE condition to apply to all queries
* @return array Server-side processing response array
*/
static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
{
$bindings = array();
$db = self::db( $conn );
$localWhereResult = array();
$localWhereAll = array();
$whereAllSql = '';
// Build the SQL query string from the request
$limit = self::limit( $request, $columns );
$order = self::order( $request, $columns );
$where = self::filter( $request, $columns, $bindings );
$whereResult = self::_flatten( $whereResult );
$whereAll = self::_flatten( $whereAll );
if ( $whereResult ) {
$where = $where ?
$where .' AND '.$whereResult :
'WHERE '.$whereResult;
}
if ( $whereAll ) {
$where = $where ?
$where .' AND '.$whereAll :
'WHERE '.$whereAll;
$whereAllSql = 'WHERE '.$whereAll;
}
// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
"SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
FROM `$table`
$where
$order
$limit"
);
// Data set length after filtering
$resFilterLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table`
$where"
);
$recordsFiltered = $resFilterLength[0][0];
// Total data set length
$resTotalLength = self::sql_exec( $db, $bindings,
"SELECT COUNT(`{$primaryKey}`)
FROM `$table` ".
$whereAllSql
);
$recordsTotal = $resTotalLength[0][0];
/*
* Output
*/
return array(
"draw" => isset ( $request['draw'] ) ?
intval( $request['draw'] ) :
0,
"recordsTotal" => intval( $recordsTotal ),
"recordsFiltered" => intval( $recordsFiltered ),
"data" => self::data_output( $columns, $data )
);
}
/**
* Connect to the database
*
* @param array $sql_details SQL server connection details array, with the
* properties:
* * host - host name
* * db - database name
* * user - user name
* * pass - user password
* @return resource Database connection handle
*/
static function sql_connect ( $sql_details )
{
try {
$db = @new PDO(
"mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
$sql_details['user'],
$sql_details['pass'],
array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
);
}
catch (PDOException $e) {
self::fatal(
"An error occurred while connecting to the database. ".
"The error reported by the server was: ".$e->getMessage()
);
}
return $db;
}
/**
* Execute an SQL query on the database
*
* @param resource $db Database handler
* @param array $bindings Array of PDO binding values from bind() to be
* used for safely escaping strings. Note that this can be given as the
* SQL query string if no bindings are required.
* @param string $sql SQL query to execute.
* @return array Result from the query (all rows)
*/
static function sql_exec ( $db, $bindings, $sql=null )
{
// Argument shifting
if ( $sql === null ) {
$sql = $bindings;
}
$stmt = $db->prepare( $sql );
//echo $sql;
// Bind parameters
if ( is_array( $bindings ) ) {
for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
$binding = $bindings[$i];
$stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
}
}
// Execute
try {
$stmt->execute();
}
catch (PDOException $e) {
self::fatal( "An SQL error occurred: ".$e->getMessage() );
}
// Return all
return $stmt->fetchAll( PDO::FETCH_BOTH );
}
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Internal methods
*/
/**
* Throw a fatal error.
*
* This writes out an error message in a JSON string which DataTables will
* see and show to the user in the browser.
*
* @param string $msg Message to send to the client
*/
static function fatal ( $msg )
{
echo json_encode( array(
"error" => $msg
) );
exit(0);
}
/**
* Create a PDO binding key which can be used for escaping variables safely
* when executing a query with sql_exec()
*
* @param array &$a Array of bindings
* @param * $val Value to bind
* @param int $type PDO field type
* @return string Bound key to be used in the SQL where this parameter
* would be used.
*/
static function bind ( &$a, $val, $type )
{
$key = ':binding_'.count( $a );
$a[] = array(
'key' => $key,
'val' => $val,
'type' => $type
);
return $key;
}
/**
* Pull a particular property from each assoc. array in a numeric array,
* returning and array of the property values from each item.
*
* @param array $a Array to get data from
* @param string $prop Property to read
* @return array Array of property values
*/
static function pluck ( $a, $prop )
{
$out = array();
for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
if(empty($a[$i][$prop])){
continue;
}
//removing the $out array index confuses the filter method in doing proper binding,
//adding it ensures that the array data are mapped correctly
$out[$i] = $a[$i][$prop];
}
return $out;
}
/**
* Return a string from an array or a string
*
* @param array|string $a Array to join
* @param string $join Glue for the concatenation
* @return string Joined string
*/
static function _flatten ( $a, $join = ' AND ' )
{
if ( ! $a ) {
return '';
}
else if ( $a && is_array($a) ) {
return implode( $join, $a );
}
return $a;
}
}
Step 3: validator.class.php library
validator.class.php:
<?php
/* ####
class by skmcoder
*/
class validator
{
/* find_errors prop */
private $errors = [];
/* find_errors prop */
/* validation prop */
private $input_names = array();
/* validation prop */
/* check all validation */
private $check = array();
/* check all validation */
/* masgs */
private $masgs = array();
public $Messages = [
"Date-error" => "Please enter a valid date in the field",
"Time-error" => "Please type a valid time representing 24 hours in the field",
"Number-word-max-error" => "The number of words or numbers must be in a field",
"Number-word-min-error" => "You must be the number of words or numbers in a field",
"Number-error" => "Please enter numbers only in the field",
"url-error" => "Please enter a valid link in the field",
"eng-error" => "Please type English only in the field",
"email-error" => "Please type a valid email in the field"
];
/* masgs */
/* flag all validation */
// public $ok;
/* custom name */
private $custom_name = array();
/* custom name */
private $custom_name2 = array();
/* flag all validation */
public function test_validate()
{
return $this->find_errors($this->check);
}
public function __construct($valid_array)
{
foreach ($valid_array as $key => $value) {
array_push($this->input_names, strip_tags(htmlentities(trim($_POST[$key]))));
foreach ($value as $option) {
/* start validation part */
switch ($option) {
case substr($option, 0, 3) == "max":
$this->max(substr($option, 4), $key);
break;
case substr($option, 0, 3) == "min":
$this->min(substr($option, 4), $key);
break;
case substr($option, 0, 4) == "name":
$this->custom_name[] = $key . ':' . substr($option, 5);
break;
case "require":
$this->require($key);
break;
case "url":
$this->url($key);
break;
case "number":
$this->number($key);
break;
case "email":
$this->email($key);
break;
case "eng":
$this->eng($key);
break;
case "date":
$this->date($key);
break;
case "time24":
$this->time24($key);
break;
};
}
}
/*get the name inputs */
// foreach($this->input_names as $inputs){
// echo $inputs .'<br>';
// }
/*get the name inputs */
}
private function date($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
if (!preg_match("/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$/", $input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' ' . $this->Messages['Date-error'] . $custom_name_arabic);
} else {
array_push($this->masgs, ' ' . $this->Messages['Date-error'] . $input_name);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function time24($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
if (!preg_match("#^([01]?[0-9]|2[0-3]):[0-5][0-9](:[0-5][0-9])?$#", $input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' ' . $this->Messages['Time-error'] . $custom_name_arabic);
} else {
array_push($this->masgs, ' ' . $this->Messages['Time-error'] . $input_name);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function max($max_number, $input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim(@$_POST[$input_name])));
}
// echo strlen($input_value) .'<Br>';
if (strlen("$input_value") > $max_number) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' ' . ' ' . ' ' . $this->Messages['Number-word-max-error'] . ' ' . $custom_name_arabic . "don't go beyond
" . ' ' . $max_number);
} else {
array_push($this->masgs, $max_number . ' ' . "don't go beyond
" . ' ' . $input_name . ' ' . $this->Messages['Number-word-max-error']);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
};
}
private function min($min_number, $input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim($_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
// echo strlen($input_value) .'<Br>';
if (strlen("$input_value") < $min_number) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, $min_number . ' ' . ' ' . ' ' . $this->Messages["Number-word-min-error"] . ' ' . $custom_name_arabic . ' at least
');
} else {
array_push($this->masgs, ' ' . ' at least
' . ' ' . $input_name . ' ' . $this->Messages["Number-word-min-error"] . $min_number);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
};
}
private function number($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim(@$_POST[$input_name])));
}
if (!preg_match("/^[0-9]+$/", $input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' ' . $this->Messages['Number-error'] . $custom_name_arabic);
} else {
array_push($this->masgs, $input_name . ' ' . $this->Messages['Number-error']);
};
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function require($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim(@$_POST[$input_name])));
}
if (empty($input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' Do not leave a field
' . $custom_name_arabic . ' ' . 'empty');
} else {
array_push($this->masgs, 'empty' . ' ' . $input_name . ' ' . ' Do not leave a field');
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function url($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
if (!filter_var($input_value, FILTER_VALIDATE_URL)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, ' ' . $this->Messages['url-error'] . $custom_name_arabic);
} else {
array_push($this->masgs, $input_name . ' ' . $this->Messages['url-error']);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function eng($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
$regex = '/^[a-zA-Z0-9$@$!%*?&#^-_. +]+$/';
if (!preg_match($regex, $input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, $this->Messages['eng-error'] . ' ' . $custom_name_arabic);
} else {
array_push($this->masgs, $input_name . ' ' . $this->Messages['eng-error']);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
private function email($input_name = null, $custom_name_arabic = null)
{
foreach ($this->custom_name as $value) {
$pos = strpos($value, ":");
/*custom name */
$name = substr($value, 0, $pos);
/* origanl name */
$custom_name = substr($value, $pos + 1);
if ($name == $input_name) {
$custom_name_arabic = $custom_name;
}
}
if (in_array(strip_tags(htmlentities(trim(@$_POST[$input_name]))), $this->input_names)) {
$input_value = strip_tags(htmlentities(trim($_POST[$input_name])));
}
$regex = '/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/';
if (!preg_match($regex, $input_value)) {
if (isset($custom_name_arabic)) {
array_push($this->masgs, $this->Messages['email-error'] . ' ' . $custom_name_arabic);
} else {
array_push($this->masgs, $input_name . ' ' . $this->Messages['email-error']);
}
array_push($this->check, "false");
} else {
array_push($this->check, "true");
}
}
public function errors_validate()
{
return $this->masgs;
}
public function find_errors($errors_array)
{
$error = $this->errors[] = $errors_array;
$flag_false = array();
$flag_true = array();
$errors_array = array();
for ($i = 0; $i < count($error); $i++) {
if ($error[$i] == "false") {
array_push($flag_false, "false");
} else {
array_push($flag_true, "true");
}
}
if ($flag_false != null) {
return false;
} else if ($flag_true != null) {
return true;
}
// return $this->chack_bool;
}
};
Step 4: design with bootstrap and add and update.php and index.php with some code php for validation or showing data from database like in update page .. etc.
index.php:
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta name="description" content="">
<!-- datatables css -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
<!-- Bootstrap core CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body class="vsc-initialized">
<br>
<h3 align="center" style="font-weight: revert; margin-top:3px;color:color(prophoto-rgb 0.47 0.52 0.53);">PHP CRUD OOP using Datatables server side ,PDO and Bootstrap 5 </h3>
<div id="gtx-trans" style="position: absolute; left: 847px; top: 35px;">
<div class="gtx-trans-icon"></div>
</div>
<h4><a href="add.php" type="button" class="btn btn-info" style="
margin-left: 31px;margin-bottom: 4px;
">
Add User
</a></h4>
<div class="container">
<?php
//for show add message
session_start();
if (isset($_SESSION['add_message'])) {
echo $_SESSION['add_message'];
$_SESSION['add_message'] = '';
};
if (isset($_SESSION['delete_message'])) {
echo $_SESSION['delete_message'];
$_SESSION['delete_message'] = '';
};
?>
<table id="example" class="table " style="width:100%">
<thead class="table-info">
<tr>
<th>username </th>
<th>email</th>
<th> </th>
</tr>
</thead>
<tbody>
<tfoot class="table-info">
<tr>
<th>username </th>
<th>email</th>
<th> </th>
</tr>
</tfoot>
</tbody>
</table>
</div>
<!-- datatables js -->
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#example').DataTable({
"searching": true,
"processing": true,
"serverSide": true,
"ajax": "server_side.php"
});
});
</script>
<!-- sweetalert2 -->
</body>
</html>
Step 5:
add.php:
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-md-12">
<div class="card text-info mb-3">
<div class="card-header" style="
COLOR: aliceblue;
BACKGROUND-COLOR: #4da5a5;
">
<a href="index.php" class="btn btn-light float-right ">Back <i class="fa fa-undo"></i> </a>
</a>
Add New User
</div>
<div class="card-body">
<?php
// validation message errors
session_start();
if (isset($_SESSION['error_validation_messages']) && $_SESSION['error_validation_messages'] != '') {
foreach ($_SESSION['error_validation_messages'] as $errors) {
echo ' <div class="alert alert-dismissible alert-warning text-right direction-rtl" role="alert">
<button type="button" class="close" data-dismiss="alert">×</button>
<strong>' . $errors . '</strong>.
</div>';
}
$_SESSION['error_validation_messages'] = '';
};
?>
<form action="crud.php?action=add" method="post">
<div class="form-group">
<label for="username">username:</label>
<input type="text" name="username" class="form-control" id="username">
</div>
<div class="form-group">
<label for="email">Email:</label>
<input type="email" name="email" class="form-control" id="email">
</div>
<br>
<button type="submit" class="btn btn-dark">Add</button>
</form>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Step 6:
update.php:
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0-alpha1/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-md-12">
<div class="card text-info mb-3">
<div class="card-header" style="
BACKGROUND-COLOR: #ffc107;
">
<?php
if (isset($_GET['id']) && $_GET['id'] != '') {
include 'crud.php';
$id = intval($_GET['id']);
$crud = new crud();
$row = $crud->SelectById($id);
if ($row != '') {
?>
<a href="index.php" class="btn btn-light float-right ">Back <i class="fa fa-undo"></i> </a>
</a>
<h6 style="color:black;">Update User</h6>
</div>
<div class="card-body">
<?php
// validation message errors
if (isset($_SESSION['error_validation_messages']) && $_SESSION['error_validation_messages'] != '') {
foreach ($_SESSION['error_validation_messages'] as $errors) {
echo ' <div class="alert alert-dismissible alert-warning text-right direction-rtl" role="alert">
<button type="button" class="close" data-dismiss="alert">×</button>
<strong>' . $errors . '</strong>.
</div>';
}
$_SESSION['error_validation_messages'] = '';
};
?>
<form action="crud.php?action=update&&id=<?php echo $row['id']; ?>" method="post">
<div class="form-group">
<label for="username">username:</label>
<input type="text" name="username" class="form-control" value="<?php echo $row['username']; ?>" id="username">
</div>
<div class="form-group">
<label for="email">Email:</label>
<input type="email" name="email" value="<?php echo $row['email']; ?>" class="form-control" id="email">
</div>
<br>
<button type="submit" class="btn btn-info">Update</button>
</form>
<?php }
} ?>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
Step 7: code for datatables server side.
server_side.php:
<?php
/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simple to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
// DB table to use
$table = 'users';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'username', 'dt' => 0 ),
array( 'db' => 'email', 'dt' => 1 ),
/** for showing edit and delete buttons */
array(
'db' => 'id',
'dt' => 2,
'formatter' => function($d, $row) {
return '<a class="btn btn-light fa fa-edit " href="update.php?id=' . $d . '"> </a> <a class="btn btn-light fa fa-trash " href="crud.php?action=delete&&id=' . $d . '"> </a> ';
}
),
);
// SQL server connection information
$sql_details = array(
'user' => 'root',
'pass' => '',
'db' => 'crud',
'host' => 'localhost'
);
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'libraries/ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
?>
Step 8: the main code of crud using oop and pdo.
crud.php:
<?php
include 'libraries/validator.class.php';
session_start();
class Crud
{
private $conn;
public function __construct($host = "localhost", $dbname = "crud", $username = "root", $password = "")
{
//connect database
try {
$this->conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
//select action
if (isset($_GET['action'])) {
//add action
if ($_GET['action'] === 'add') {
$username = htmlentities($_POST['username']);
$email = htmlentities($_POST['email']);
//validation
$validator = new validator(
[
"email" => ["require", "email"],
"username" => ["require"],
],
);
if ($validator->test_validate() == true) {
//if not find errors validation
$add = $this->Add($username, $email);
if ($add == true) {
//for show add message
$add_message = '<div class="alert alert-success" role="alert">';
$add_message .= 'Record added successfully!';
$add_message .= '</div>';
$_SESSION['add_message'] = $add_message;
header("Location:index.php");
}
} else {
$_SESSION['error_validation_messages'] = $validator->errors_validate();
header("Location:add.php ");
}
}
//update action
if ($_GET['action'] === 'update' && isset($_GET['id'])) {
$id = $_GET['id'];
$username = htmlentities($_POST['username']);
$email = htmlentities($_POST['email']);
//validation
$validator = new validator(
[
"email" => ["require", "email"],
"username" => ["require"],
],
);
if ($validator->test_validate() == true) {
//if not find errors validation
$add = $this->update($id, $username, $email);
if ($add == true) {
//for show add message
$add_message = '<div class="alert alert-success" role="alert">';
$add_message .= 'Record updated successfully!';
$add_message .= '</div>';
$_SESSION['add_message'] = $add_message;
header("Location:index.php");
}
} else {
$_SESSION['error_validation_messages'] = $validator->errors_validate();
header("Location:update.php?id=$id ");
}
}
//delete action
if ($_GET['action'] === 'delete' && isset($_GET['id'])) {
$id = intval($_GET['id']);
$delete = $this->delete($id);
if ($delete == true) {
//for show add message
$add_message = '<div class="alert alert-success" role="alert">';
$add_message .= 'Record deleted successfully!';
$add_message .= '</div>';
$_SESSION['delete_message'] = $add_message;
header("Location:index.php");
}
}
}
}
public function Add($username, $email)
{
try {
$stmt = $this->conn->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->execute();
return true;
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
return false;
}
}
public function update($id, $username, $email)
{
try {
$stmt = $this->conn->prepare("UPDATE users SET username = :username, email = :email WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_STR);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);
$stmt->execute();
return true;
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
return false;
}
}
public function delete($id)
{
try {
$stmt = $this->conn->prepare("DELETE FROM users WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
return true;
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
return false;
}
}
public function SelectById($id)
{
try {
$stmt = $this->conn->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row;
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
}
$crud = new Crud();
