Below class file handle mysql and mysqli connection & quries. you can select all records as well as can place where clause also saving records , removing records , executing direct query with Mysql OR MySqli as defined in config.php file.
1. Your config.php file will be as below , you can change it and placed values accordingly as your mysql database. resource_type Mysql OR MySqli
$dbconfig['db_server'] = 'localhost'; $dbconfig['db_port'] = ':3306'; $dbconfig['db_username'] = 'root'; $dbconfig['db_password'] = ''; $dbconfig['db_name'] = 'test'; $resource_type='MySqli';
2. Below is MysqlDatabaseMysqli.php class which has functions , you can execute Queries with MySql or MySqli.
require_once 'config.php';
Class MysqlDatabaseMysqli {
protected $connection = NULL;
protected $connection_string = NULL;
protected $associate_type = NULL;
function __construct() {
global $resource_type;
$this->resource_type_string = strtolower($resource_type);
switch ($resource_type) {
case 'MySql':
$this->associate_type = MYSQL_ASSOC;
$this->msl_connect();
break;
case 'MySqli':
$this->associate_type = MYSQLI_ASSOC;
$this->msli_connect();
break;
default:
echo "No resource type define";
exit;
break;
}
}
/*
* msl->mysql
*/
function msl_connect() {
global $dbconfig;
try {
if ($link_identifier = @mysql_connect($dbconfig['db_server'],
$dbconfig['db_username'], $dbconfig['db_password'])) {
$select_db = @mysql_select_db($dbconfig['db_name'], $link_identifier);
if (!$select_db) {
throw new Exception(mysql_errno() . ' ' . mysql_error());
}
$this->connection = $link_identifier;
} else {
throw new Exception(mysql_errno() . ' ' . mysql_error());
}
} catch (Exception $e) {
echo $e->getMessage();
}
}
/*
* msli->mysqli
*/
function msli_connect() {
global $dbconfig;
try {
if ($link_identifier = @mysqli_connect($dbconfig['db_server']
, $dbconfig['db_username'], $dbconfig['db_password'])) {
$select_db = @mysqli_select_db($link_identifier, $dbconfig['db_name']);
if (!$select_db) {
throw new Exception(mysqli_connect_errno() . ' Unable to select db');
}
$this->connection = $link_identifier;
} else {
throw new Exception(mysqli_connect_errno() . ' Unable to connect');
}
} catch (Exception $e) {
echo $e->getMessage();
}
}
/*
* Created By: Boy108zon
* @table_name: string
* @where: Array
*/
function get_records($table_name, $where=NULL) {
$connect = $this->resource_type_string . '_query';
$num_rows = $this->resource_type_string . '_num_rows';
$connect_close = $this->resource_type_string . '_close';
/* if where included */
$qwhere = '';
if (is_array($where)) {
$counter = 0;
foreach ($where as $key => $value) {
if ($counter > 0) {
$qwhere .= ' AND ';
}
$qwhere .= "$key = '$value'";
$counter++;
}
$qwhere = 'WHERE ' . $qwhere;
}
$query = "SELECT * from $table_name $qwhere";
if ($this->resource_type_string == 'mysqli') {
$result_resource = $connect($this->connection, $query);
} else {
$result_resource = $connect($query, $this->connection);
}
$noOfrows = $num_rows($result_resource);
if ($noOfrows > 0) {
return $this->db_to_array($result_resource);
} else {
return NULL;
}
}
/*
* Created By: Boy108zon
* @table_name: string
* @where: Array
*/
function remove_records($table_name, $where=NULL) {
$connect = $this->resource_type_string . '_query';
$affected_rows = $this->resource_type_string . '_affected_rows';
$connect_close = $this->resource_type_string . '_close';
/* if where included */
$qwhere = '';
if (is_array($where)) {
$counter = 0;
foreach ($where as $key => $value) {
if ($counter > 0) {
$qwhere .= ' AND ';
}
$qwhere .= "$key = '$value'";
$counter++;
}
$qwhere = 'WHERE ' . $qwhere;
}
$query = "DELETE from $table_name $qwhere";
if ($this->resource_type_string == 'mysqli') {
$result_resource = $connect($this->connection, $query);
} else {
$result_resource = $connect($query, $this->connection);
}
$idds = $affected_rows($this->connection);
//close connection
@$connect_close($this->connection);
if ($idds > 0) {
return $idds;
} else {
return NULL;
}
}
/*
* Created By: Boy108zon
* @result->mysql OR mysqli
*/
function db_to_array($result) {
$connection_string = $this->resource_type_string . '_fetch_array';
$rows = array();
while ($row = $connection_string($result, $this->associate_type)) {
$rows[] = $row;
}
//close connection
@$connect_close($this->connection);
return $rows;
}
/*
* Created By: Boy108zon
* @table_name: String
* @post_array:Array
* @where:Array
* @mode:'save' OR edit
* Based on mode return type affected rows and latest insert id
*/
function save_records($table_name, $post_array, $where=NULL, $mode='save') {
$connect = $this->resource_type_string . '_query';
$insert_id = $this->resource_type_string . '_insert_id';
$affected_rows = $this->resource_type_string . '_affected_rows';
$connect_close = $this->resource_type_string . '_close';
$count = 0;
$fields = '';
foreach ($post_array as $col => $val) {
if ($count > 0) {
$fields .= ', ';
}
$fields .= "$col = '$val'";
$count++;
}
$qwhere = '';
if (is_array($where)) {
$counter = 0;
foreach ($where as $key => $value) {
if ($counter > 0) {
$qwhere .= ' AND ';
}
$qwhere .= "$key = '$value'";
$counter++;
}
$qwhere = 'WHERE ' . $qwhere;
}
if ($mode == 'edit') {
$query = "UPDATE " . $table_name . " SET $fields $qwhere;";
} else {
$query = "INSERT INTO " . $table_name . " SET $fields;";
}
if ($this->resource_type_string == 'mysqli') {
$result_resource = $connect($this->connection, $query);
} else {
$result_resource = $connect($query, $this->connection);
}
if ($mode == 'edit') {
$idds = $affected_rows($this->connection);
} else {
$idds = $insert_id($this->connection);
}
//close connection
@$connect_close($this->connection);
if ($idds > 0) {
return $idds;
} else {
return NULL;
}
}
/*
* Created By: Boy108zon
* @query: full query including inner , where
* if required we can also use it.
*/
function get_direct_query_records($query) {
$connect = $this->resource_type_string . '_query';
$num_rows = $this->resource_type_string . '_num_rows';
$connect_close = $this->resource_type_string . '_close';
if ($this->resource_type_string == 'mysqli') {
$result_resource = $connect($this->connection, $query);
} else {
$result_resource = $connect($query, $this->connection);
}
$noOfrows = $num_rows($result_resource);
//close connection
@$connect_close($this->connection);
if ($noOfrows > 0) {
return $this->db_to_array($result_resource);
} else {
return NULL;
}
}
}
3. You can use it like below
require_once 'MysqlDatabaseMysqli.php';
$dbfun=new MysqlDatabaseMysqli();
$result=$dbfun->get_records('employee',array('name'=>'amit'));
//For Retrive records from employee table.
$result=$dbfun->get_records('your_table_name');
//edit
$result=$dbfun->save_records('your_table_name',$post_array,$where,$mode='Edit');
//save
$result=$dbfun->save_records('your_table_name',$post_array);
//remove
$result=$dbfun->remove_records('your_table_name',array('id'=>6));
// direct query
$result=$dbfun->get_direct_query_records($query='select * from employee');
4. You can download class from below
https://github.com/boy108zon/SingelMysqlMysqli
