PHP DB Database Connection Class Definition and Usage Example
- 2021-11-29 23:22:04
- OfStack
This article illustrates the definition and usage of the PHP DB database connection class. Share it for your reference, as follows:
Statement:
Recently, I watched a video of PHP message queue. For the code provided by lecturer WiconWang, I share it here, hoping to help my friends who love learning …
<?php
// Database connection class
class DB{
// Private property
private static $dbcon=false;
private $host;
private $port;
private $user;
private $pass;
private $db;
private $charset;
private $link;
// Private constructor
private function __construct(){
$this->host = 'localhost';
$this->port = '3306';
$this->user = 'root';
$this->pass = 'root';
$this->db = 'imooc';
$this->charset= 'utf8';
// Connect to a database
$this->db_connect();
// Select a database
$this->db_usedb();
// Set the character set
$this->db_charset();
}
// Connect to a database
private function db_connect(){
$this->link=mysqli_connect($this->host.':'.$this->port,$this->user,$this->pass);
if(!$this->link){
echo " Database connection failed <br>";
echo " Error coding ".mysqli_errno($this->link)."<br>";
echo " Error message ".mysqli_error($this->link)."<br>";
exit;
}
}
// Set the character set
private function db_charset(){
mysqli_query($this->link,"set names {$this->charset}");
}
// Select a database
private function db_usedb(){
mysqli_query($this->link,"use {$this->db}");
}
// Private cloning
private function __clone(){
die('clone is not allowed');
}
// Common static method
public static function getIntance(){
if(self::$dbcon==false){
self::$dbcon=new self;
}
return self::$dbcon;
}
// Execute sql Method of statement
public function query($sql){
$res=mysqli_query($this->link,$sql);
if(!$res){
echo "sql Statement execution failed <br>";
echo " The error code is ".mysqli_errno($this->link)."<br>";
echo " The error message is ".mysqli_error($this->link)."<br>";
}
return $res;
}
// Get the last 1 A record id
public function getInsertid(){
return mysqli_insert_id($this->link);
}
/**
* Query a field
* @param
* @return string or int
*/
public function getOne($sql){
$query=$this->query($sql);
return mysqli_free_result($query);
}
// Get 1 Row record ,return array 1 Dimensional array
public function getRow($sql,$type="assoc"){
$query=$this->query($sql);
if(!in_array($type,array("assoc",'array',"row"))){
die("mysqli_query error");
}
$funcname="mysqli_fetch_".$type;
return $funcname($query);
}
// Get 1 A record , Preconditions are obtained through resources 1 A record
public function getFormSource($query,$type="assoc"){
if(!in_array($type,array("assoc","array","row")))
{
die("mysqli_query error");
}
$funcname="mysqli_fetch_".$type;
return $funcname($query);
}
// Get multiple pieces of data, 2 Dimensional array
public function getAll($sql){
$query=$this->query($sql);
$list=array();
while ($r=$this->getFormSource($query)) {
$list[]=$r;
}
return $list;
}
public function selectAll($table,$where,$fields='*',$order='',$skip=0,$limit=1000)
{
if(is_array($where)){
foreach ($where as $key => $val) {
if (is_numeric($val)) {
$condition = $key.'='.$val;
}else{
$condition = $key.'=\"'.$val.'\"';
}
}
} else {
$condition = $where;
}
if (!empty($order)) {
$order = " order by ".$order;
}
$sql = "select $fields from $table where $condition $order limit $skip,$limit";
$query = $this->query($sql);
$list = array();
while ($r= $this->getFormSource($query)) {
$list[] = $r;
}
return $list;
}
/**
* Defining Methods for Adding Data
* @param string $table Table name
* @param string orarray $data [ Data ]
* @return int Newly added id
*/
public function insert($table,$data){
// Traverse the array and get every 1 Fields and field values
$key_str='';
$v_str='';
foreach($data as $key=>$v){
// if(empty($v)){
// die("error");
// }
//$key The value of is per 1 Fields s1 The value corresponding to the field
$key_str.=$key.',';
$v_str.="'$v',";
}
$key_str=trim($key_str,',');
$v_str=trim($v_str,',');
// Judge whether the data is empty or not
$sql="insert into $table ($key_str) values ($v_str)";
$this->query($sql);
// Back up 1 Secondary increase operation generation ID Value
return $this->getInsertid();
}
/*
* Delete 1 Bar data method
* @param1 $table, $where=array('id'=>'1') Table name Condition
* @return Number of rows affected
*/
public function deleteOne($table, $where){
if(is_array($where)){
foreach ($where as $key => $val) {
$condition = $key.'='.$val;
}
} else {
$condition = $where;
}
$sql = "delete from $table where $condition";
$this->query($sql);
// Returns the number of rows affected
return mysqli_affected_rows($this->link);
}
/*
* Method for deleting multiple pieces of data
* @param1 $table, $where Table name Condition
* @return Number of rows affected
*/
public function deleteAll($table, $where){
if(is_array($where)){
foreach ($where as $key => $val) {
if(is_array($val)){
$condition = $key.' in ('.implode(',', $val) .')';
} else {
$condition = $key. '=' .$val;
}
}
} else {
$condition = $where;
}
$sql = "delete from $table where $condition";
$this->query($sql);
// Returns the number of rows affected
return mysqli_affected_rows($this->link);
}
/**
* [ Modification operation description]
* @param [type] $table [ Table name ]
* @param [type] $data [ Data ]
* @param [type] $where [ Condition ]
* @return [type]
*/
public function update($table,$data,$where,$limit=0){
// Traverse the array and get every 1 Fields and field values
$str='';
foreach($data as $key=>$v){
$str.="$key='$v',";
}
$str=rtrim($str,',');
if(is_array($where)){
foreach ($where as $key => $val) {
if(is_array($val)){
$condition = $key.' in ('.implode(',', $val) .')';
} else {
$condition = $key. '=' .$val;
}
}
} else {
$condition = $where;
}
if (!empty($limit)) {
$limit = " limit ".$limit;
}else{
$limit='';
}
// Modify SQL Statement
$sql="update $table set $str where $condition $limit";
$this->query($sql);
// Returns the number of rows affected
return mysqli_affected_rows($this->link);
}
}
?>
Usage
In the DB class,__construct()
To modify the configuration information in your own database
include
Introducing DB class
Using the methods in the DB class requires instantiation first, taking inserting data as an example:
$db = DB::getIntance();
$insert_data = ['order_id'=>'10010','order_amount' = '200.00','status'=> 1];
$res = $db->insert('order_info',$insert_data);
For more readers interested in PHP related content, please check the topics on this site: "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Encyclopedia of PHP Array (Array) Operation Skills", "Introduction to PHP Basic Syntax", "Summary of php String (string) Usage", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"
I hope this article is helpful to everyone's PHP programming.