Writing PHP is considerably harder than ASP.Net or Microsoft .Net. One reason I kind of hate it is that I am a lazy programmer. So here is what I use for a PHP DEV at the moment. A Database class that make it easy to deal with the DB.
This is a copied class. The author is there in the class file as a comment. I will discuss how to use it.
The next post is about how it use it.
This is a copied class. The author is there in the class file as a comment. I will discuss how to use it.
<?php
/**
* MySQLi Database Class
* @category Database Access
* @package Database
* @author AashikP
* @copyright Copyright (c) 2018 AashikP
* @license https://opensource.org/licenses/MIT The MIT License
* @version 0.1
*/
class MySQLiDB
{
// Mysqli instance.
private $mySqli;
// Save Prefix if defined.
private $prefix = '';
// Generate an array from given $data values for bindParam
private $bind_arr = array(''); // Create the empty 0 index
// Set type to use in bindPar function
private $type;
// Set table with prefix if exists
private $table;
// array to generate bind_results
private $result_arr = array('');
// array to catch multiple rows of results
private $multi_result_arr = array();
// array to fetch values
private $fetch = array();
public function __construct()
{
// Create a connection
$this->connect();
// Check if a database prefix is defined. If defined, set prefix value
defined('DB_PREFIX') ? $this->setPrefix(DB_PREFIX) : null;
}
// Connect using a mysqli instance
private function connect()
{
$this->mySqli = new \mysqli('localhost', 'root', '', 'prasanna2019april');
// Is there an issue connecting to the database?
if ($this->mySqli->connect_errno) {
echo '<br/>', 'Error: Unable to connect to Database.' , '<br>';
echo "Debugging errno: " . $this->mySqli->connect_errno , '<br>';
echo "Debugging error: " . $this->mySqli->connect_error , '<br>';
unset($this->mySqli);
exit;
}
}
// Set prefix for the table name if there's a prefix setup in the config file
private function setPrefix($value = '')
{
$this->prefix = $value;
}
// Function to insert data into table
public function insert($args)
{
// set type
$this->type = 'insert';
// set table and configure prefix, if available
$this->setTable($args['table']);
// generate insert query
$query = $this->genQuery($args);
// prepare query statement
$stmt = $this->mySqli->prepare($query);
if ($this->mySqli->errno) {
die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
// generate the bind_arr to be used to bind_param
$this->bindPar($args);
// bind parameters for statement execution
call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
// execute the statement (return error if execution failed)
if (!$stmt->execute()) {
die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
}
// close statement
$stmt->close();
$this->reset();
}
// Function to update data
public function update($args)
{
// set type for use in query generator
$this->type = 'update';
// set table and configure prefix, if available
$this->setTable($args['table']);
// generate update query
$query = $this->genQuery($args);
// prepare query statement
$stmt = $this->mySqli->prepare($query);
if ($this->mySqli->errno) {
die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
// generate the bind_arr to be used to bind_param
$this->bindPar($args);
// bind parameters for statement execution
call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
// execute the statement (return error if execution failed)
if (!$stmt->execute()) {
die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
}
// close statement
$stmt->close();
$this->reset();
}
// Function to select data from the table
public function select($args)
{
// set type for use in query generator
$this->type = 'select';
// set table and configure prefix, if available
$this->setTable($args['table']);
// generate select query
$query = $this->genQuery($args);
// prepare query statement
$stmt = $this->mySqli->prepare($query);
if ($this->mySqli->errno) {
die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
// generate the bind_arr to be used to bind_param
$this->bindPar($args);
// bind parameters for statement execution if bind_arr is not empty
// bind_arr will be empty if you're trying to retrieve all the values in a row
if (!empty($this->bind_arr)) {
call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
}
// execute the statement (return error if execution failed)
if (!$stmt->execute()) {
die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
}
// if you've manually defined the data that you need to retrieve, generate result set
if (is_array($args['data'])) {
// generate the result set as an array to be
$this->genResultArr($args);
call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
if ($this->mySqli->errno) {
die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
$this->fetch = array(); // making sure the array is empty
$i=0;
while ($stmt->fetch()) {
$this->multi_result_arr = array_combine($args['data'], $this->result_arr);
// Get the values and append it to fetch array $i denotes the row number
foreach ($this->multi_result_arr as $arr => $val) {
$this->fetch[$i][$arr] = $val;
}
$i++;
}
// if there's just one row of results retrieved, just reset the array
// so that you can directly call the value by $fetch['column_name']
if (count($this->fetch) == 1) {
$this->fetch = $this->fetch[0];
}
} elseif ($args['data'] == '*') {
// Generate a result metadata variable to be used to fetch column names in the array
$res = $stmt->result_metadata();
// Copy the column tables as an array into the fields variable to generate bind_result later
$fields = $res->fetch_fields();
// Field count for iteration
$count = $res->field_count;
// row count to chose type of array (multidimensional if more than one row found)
$row = $res->num_rows;
for ($i = 0; $i < $count; $i++) {
$this->multi_result_arr[$i] = $this->result_arr[$i] = $fields[$i]->name;
}
call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
if ($this->mySqli->errno) {
die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
$this->fetch = array(); // making sure the array is empty
$i=0;
// create a fetch array that combines the required db column names with the retrieved results
while ($stmt->fetch()) {
$this->fetch[$i] = array_combine($this->multi_result_arr, $this->result_arr);
$i++;
}
// if there's just one row of results retrieved, just reset the array
// so that you can directly call the value by $fetch['column_name']
if (count($this->fetch) == 1) {
$this->fetch = $this->fetch[0];
}
}
$stmt->close();
// reset values for next query
$this->reset();
return $this->fetch;
}
// Function to delete values from a Database
public function delete($args)
{
// delete function must not be used to truncate tables
if (!isset($args['where'])) {
echo 'If you really want to delete all the contents, use truncate() method.';
return;
} elseif (isset($args['data'])) { // if you're just deleting fields, use update statement instead
echo 'If you want to delete certain column in a row, use the update statement instead';
}
// set type for use in query generator
$this->type = 'delete';
// set table and configure prefix, if available
$this->setTable($args['table']);
// generate delete query
$query = $this->genQuery($args);
// prepare query statement
$stmt = $this->mySqli->prepare($query);
if ($this->mySqli->errno) {
die('Unable to delete data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
}
// generate the bind_arr to be used to bind_param
$this->bindPar($args);
// bind parameters for statement execution
call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
// execute the statement (return error if execution failed)
if (!$stmt->execute()) {
die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
}
// close statement
$stmt->close();
$this->reset();
}
// Deletes all the data and resets the table. Please use with caution
public function truncate($table)
{
// set table and configure prefix, if available
$this->setTable($table);
// query to truncate the entire table
// NOTE: This is irreversible
$query = 'TRUNCATE ' . $this->table;
// prepare query statement
$stmt = $this->mySqli->prepare($query);
// execute the statement (return error if execution failed)
if (!$stmt->execute()) {
die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
}
// close statement
$stmt->close();
$this->reset();
}
// prefix table name if db prefix is setup
private function setTable($table)
{
$this->table = $this->prefix . $table;
}
// Generates the mysqli query statement
private function genQuery($args)
{
switch ($this->type) {
case 'insert':
$query = "INSERT INTO `" . $this->table .'` ';
$query .= $this->genInsert($args['data']);
$query .= " VALUES " . $this->genInsval($args['data']);
break;
case 'select':
$query = "SELECT " . $this->genSelect($args) . " FROM " . $this->table;
if (isset($args['where'])) {
$query .= $this->genWhere($args);
}
if (isset($args['order'])) {
$query .= $this->genOrder($args);
}
if (isset($args['group'])) {
$query .= $this->genGroup($args);
}
if (isset($args['limit'])) {
$query .= " LIMIT " . $args['limit'];
}
break;
case 'update':
$query = "UPDATE `" . $this->table . "` SET";
$query .= $this->genUpdate($args['data']);
if (isset($args['where'])) {
$query .= $this->genWhere($args);
}
break;
case 'delete':
$query ="DELETE FROM `" . $this->table . '` ';
if (isset($args['where'])) {
$query .= $this->genWhere($args);
}
break;
default:
$query ='';
break;
}
return $query;
}
// Generate insert query
private function genInsert($data)
{
$ins_query = '( ';
foreach ($data as $key => $value) {
if ($data[$key] == end($data)) {
$ins_query .= ' ' . $key . ' ';
continue;
}
$ins_query .= ' ' . $key . ', ';
}
$ins_query .= ')';
return $ins_query;
}
// generate the value part of the insert query to be used as a prepared statement
// Eg (? , ?, ?)
private function genInsVal($data)
{
$ins_value = '(';
foreach ($data as $k => $v) {
if ($data[$k] == end($data)) {
$ins_value .= '?';
continue;
}
$ins_value .= '?, ';
}
$ins_value .=')';
return $ins_value;
}
// generate update query
private function genUpdate($data)
{
$update_query = '';
foreach ($data as $key => $value) {
$update_query .= ' ' .$key .' =?,' ;
}
$update_query = rtrim($update_query, ',');
return $update_query;
}
// Generate select query
private function genSelect($sel_array)
{
$sel_string = '';
if (is_array($sel_array['data'])) {
foreach ($sel_array['data'] as $value) {
$sel_string .= $value . ', ';
}
$sel_string = rtrim($sel_string, ', ');
} elseif ($sel_array['data'] == '*') {
$sel_string = '*';
}
return $sel_string;
}
// Generate where condition for query generator (genQuery)
private function genWhere($where_arr)
{
$where_query = ' WHERE';
if (isset($where_arr['whereOp'])) {
$opr = $where_arr['whereOp'];
} else {
$opr = '=';
}
// Check if the given array is associative
if ($this->isAssoc($where_arr)) {
foreach ($where_arr['where'] as $key => $value) {
$where_query .= ' ' . $key . $opr . '? ';
}
} else {
foreach ($where_arr['where'] as $value) {
$where_query .= ' ' . $value . $opr . '? ';
}
}
if (isset($where_arr['and']) && !empty($where_arr['and'])) {
$where_query .= $this->andWhere($where_arr);
}
if (isset($where_arr['or']) && !empty($where_arr['or'])) {
$where_query .= $this->orWhere($where_arr);
}
return $where_query;
}
// Generate and condition for query generator (genQuery)
private function andWhere($and_arr)
{
$and_query = ' AND';
if (isset($where_arr['andOP'])) {
$opr = $where_arr['andOP'];
} else {
$opr = '=';
}
foreach ($and_arr['and'] as $key => $value) {
$and_query .= ' ' . $key . $opr . '? ';
}
return $and_query;
}
// Generate OR condition for query generator (genQuery)
private function orWhere($or_arr)
{
$or_query = ' OR';
if (isset($or_arr['orOP'])) {
$opr = $or_arr['orOp'];
} else {
$opr = '=';
}
foreach ($or_arr['and'] as $key => $value) {
$or_query .= ' ' . $key . $opr . '? ';
}
return $or_query;
}
// Generate order by condition
private function genOrder($args)
{
$order_query = ' ORDER BY ' . $args['order'] .' ';
if (isset($args['oType']) && (($args['oType'] == 'ASC') || ($args['oType'] == 'DESC'))) {
$order_query .= $args['oType'];
}
return $order_query;
}
// Generate group by conditions
private function genGroup()
{
$grp_query = ' GROUP BY ' . $args['group'] .' ';
if (isset($args['gType']) && (($args['gType'] == 'ASC') || ($args['gType'] == 'DESC'))) {
$grp_query .= $args['gType'];
}
return $grp_query;
}
// Check the input array and forward it to bindParam for further processing
private function bindPar($args)
{
if (isset($args['data']) && $this->type != 'select') {
$this->bindParam($args['data']);
}
if (isset($args['where'])) {
$this->bindParam($args['where']);
}
if (isset($args['and'])) {
$this->bindParam($args['and']);
}
if (isset($args['or'])) {
$this->bindParam($args['or']);
}
if ($this->type == 'select' && !isset($args['where']) && !isset($args['and']) && !isset($args['or'])) {
unset($this->bind_arr);
}
}
// Organize generation of bind_arr in the below method based on $data
private function bindParam($data)
{
if (is_array($data)) {
if ($this->isAssoc($data)) {
foreach ($data as $key => $value) {
$this->bindValues($value);
}
} else {
foreach ($data as $value) {
$this->bindValues($value);
}
}
} else {
$this->bindValues($data);
}
}
// Detect type and push values inside the bind_arr to be submitted as bind parameters
private function bindValues($value)
{
$this->bind_arr[0] .= $this->detectType($value);
array_push($this->bind_arr, $value);
}
// Detect value type to generate bind parameter
protected function detectType($value)
{
switch (gettype($value)) {
case 'string':
return 's';
break;
case 'integer':
return 'i';
break;
case 'blob':
return 'b';
break;
case 'double':
return 'd';
break;
}
return '';
}
protected function returnRef(array &$arr)
{
//Referenced data array is required by mysqli since PHP 5.3+
if (strnatcmp(phpversion(), '5.3') >= 0) {
$refs = array();
foreach ($arr as $key => $value) {
$refs[$key] = & $arr[$key];
}
return $refs;
}
return $arr;
}
// Generate a result array with selected values from database for given data
private function genResultArr($args)
{
$this->result_arr = array();
foreach ($args['data'] as $value) {
array_push($this->result_arr, $value);
}
}
// Check if an array is associative
private function isAssoc(array $array)
{
$keys = array_keys($array);
return array_keys($keys) !== $keys;
}
// Reset to default values after an operation
private function reset()
{
$this->type = null;
$this->table = '';
$this->bind_arr = array('');
$this->result_arr = array();
$this->multi_result_arr = array();
}
// Disconnects the active connection
private function disconnect()
{
if (isset($this->mySqli)) {
$this->mySqli->close();
unset($this->mySqli);
}
}
// Making sure we don't have open connections
public function __destruct()
{
if (isset($this->mySqli)) {
// if there's an active connection, close it
if ($this->mySqli->ping()) {
$this->disconnect();
}
}
}
}
The next post is about how it use it.
No comments:
Post a Comment