有时可能会遇到用原生php写一些小脚本的场景,可以简单封装下sql语句写起来更方便
class mysql
{
private $host = 'localhost';
private $name = 'root';
private $pass = 'root';
private $database = 'test';
private $port = 3306;
private $mysql;
/**------------------------------------------------
* 构造函数 使用PHP内置的mysqli类对数据库进行连接
*------------------------------------------------*/
public function __construct()
{
$this->mysql = @new mysqli($this->host, $this->name, $this->pass, $this->database, $this->port);
if($this->mysql->connect_errno){
exit("数据库连接错误 {$this->mysql->connect_error}");
}
}
/**------------------------------------------------
* 根据表名, 字段名, 值 对数据表进行插入数据
* 成功返回插入记录的ID 否则返回0
*------------------------------------------------*/
public function insert($table, $data)
{
//解析传递过来的字段 转为字串
$field = '';
$worth = '';
$i = 1;
$length = count($data);
foreach ($data as $key => $value) {
if($length != $i){
$field .= $key . ',';
if(gettype($value) == 'string'){
$worth .= "'$value',";
}else{
$worth .= $value;
}
}else{
$field .= $key;
if(gettype($value) == 'string'){
$worth .= "'$value'";
}else{
$worth .= $value;
}
}
$i++;
}
$sql = "insert into {$table}({$field})values($worth)";
$result = $this->mysql->query($sql);
//获取返回的ID
return $this->mysql->insert_id;
}
/**------------------------------------------------
* 根据表名 where条件的键值 对数据记录进行删除
* 成功返回 true 否则返回 false
*------------------------------------------------*/
public function delete($table, $key, $value)
{
if(gettype($value) == 'string'){
$value = "'{$value}'";
}
$sql = "delete from {$table} where {$key} = {$value}";
return $this->mysql->query($sql);
}
/**------------------------------------------------
* 根据表名 where条件的键值 对数据记录进行查询
* 成功返回数组 否则返回空数组
*------------------------------------------------*/
public function get($table, $condition)
{
$where = '';
foreach ($condition as $key => $value) {
if(gettype($value) == 'string'){
$value = "'{$value}'";
}
$where .= " $key = $value and";
}
$where = "where " . mb_substr($where, 0, mb_strlen($where) - 3);
$sql = "select * from {$table} {$where}";
$sql = trim($sql);
$result = $this->mysql->query($sql);
$data = [];
while ($res_data = $result->fetch_assoc()) {
var_dump($res_data);
$data[] = $res_data;
}
return $data;
}
/**------------------------------------------------
* 根据表名 字段及值 条件 修改记录
* 成功返回true 否则返回false
*------------------------------------------------*/
public function update($table, $data, $where)
{
$field = '';
foreach ($data as $key => $value) {
if(gettype($value) == 'string'){
$value = "'{$value}'";
}
$field .= "{$key} = {$value}, ";
}
$field = mb_substr($field, 0, mb_strlen($field) -2);
//条件
$worth = '';
foreach ($where as $key => $value) {
if(gettype($value) == 'string'){
$value = "'{$value}'";
}
$worth .= "{$key} = {$value} and ";
}
$worth = 'where ' . mb_substr($worth, 0, mb_strlen($worth) - 5);
$sql = "update {$table} set {$field} {$worth}";
return $this->mysql->query($sql);
}
/**------------------------------------------------
* 将传递过来的值转义并返回
*------------------------------------------------*/
public function escape($string)
{
return $this->mysql->escape_string($string);
}
/**------------------------------------------------
* 析构函数 关闭数据库连接
*------------------------------------------------*/
public function __destruct()
{
$this->mysql->close();
}
}
调用
//引入 MySql 类
include_once('mysql.php');
$mysql = new mysql();
//对数据记录进行删除操作
$res = $mysql->delete('表名', '条件字段名', '值');
//对数据表进行插入数据操作
$res = $mysql->insert('表名', [条件字段及值 如 'name' => 'root'...]);
//对数据表进行查询操作
$res = $mysql->get('表名', [条件字段及值 如 'name' => 'root'...]);
//对数据记录进行修改更新
$res = $mysql->update('表名', [条件字段及值 如 'name' => 'root'...]);