[PDO]②③--添加记录
/**
* 添加记录的操作
* array(
*'username'=>'imooc',
*'password'=>'imooc',
*'email'=>'email'
* )
*
* INSERT user(username,password,email) VALUES('aa','aa','aa@aa.com');
*/
public static function add($data, $table)
{
$keys = array_keys($data);
array_walk($keys, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = join(',', $keys);
$values = "'" . join("','", array_values($data)) . "'";
$sql = "INSERT {$table}({$fieldsStr}) VALUES({$values});
";
//echo $sql;
return self::execute($sql);
}
PdoMYSQL.class.php
DB_HOST,
'username' => DB_USER,
'password' => DB_PWD,
'database' => DB_NAME,
'hostport' => DB_PORT,
'dsn' => DB_TYPE . ":host=" . DB_HOST . ";
dbname=" . DB_NAME);
}
if (empty($dbConfig['hostname']))
self::throw_exception('没有定义数据库配置，请先定义');
self::$config = $dbConfig;
if (empty(self::$config['params']))
self::$config['params'] = array();
if (!isset(self::$link)) {
$configs = self::$config;
if (self::$pconnect) {
//开启长连接，添加到配置数组中
$configs['params'][constant("PDO::ATTR_PERSISTENT")] = true;
}
try {
self::$link = new PDO($configs['dsn'], $configs['username'], $configs['password'], $configs['params']);
} catch (PDOException $e) {
self::throw_exception($e->getMessage());
}
if (!self::$link) {
self::throw_exception('PDO连接错误');
return false;
}
self::$link->exec('SET NAMES ' . DB_CHARSET);
self::$dbVersion = self::$link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
self::$connected = true;
unset($configs);
}
}/**得到所有记录
* @param null $sql
* @return mixed
*/
public static function getAll($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$PDOStatement->fetchAll(constant("PDO::FETCH_ASSOC"));
return $result;
}/**
* 得到结果集的一条记录
* @param null $sql
* @return mixed
*/
public static function getRow($sql = null)
{
if ($sql != null) {
self::query($sql);
}
$result = self::$PDOStatement->fetch(constant("PDO::FETCH_ASSOC"));
return $result;
}/** 根据主键查找记录
* @param $tabName
* @param $priId
* @param string $fields
* @return mixed
*/
public static function findById($tabName, $priId, $fields = '*')
{
$sql = 'SELECT %s FROM %s WHERE id=%d;
';
return self::getRow(sprintf($sql, self::parseFields($fields), $tabName, $priId));
}/**
* 执行普通查询
* @param $tables
* @param null $where
* @param string $fields
* @param null $group
* @param null $having
* @param null $order
* @param null $limit
* @return mixed
*/
public static function find($tables, $where = null, $fields = '*', $group = null, $having = null, $order = null, $limit = null)
{
$sql = 'SELECT ' . self::parseFields($fields) . ' FROM ' . $tables . ' '
. self::parseWhere($where)
. self::parseGroup($group)
. self::parseHaving($having)
. self::parseOrder($order)
. self::parseLimit($limit);
$dataAll = self::getAll($sql);
return count($dataAll) == 1 ? $dataAll[0] : $dataAll;
}/**
* 解析where条件
* @param $where
* @return string
*/
public static function parseWhere($where)
{
$whereStr = '';
if (is_string($where) && !empty($where)) {
$whereStr = $where;
}
return empty($whereStr) ? '' : ' WHERE ' . $whereStr;
}/**
* 添加记录的操作
* array(
*'username'=>'imooc',
*'password'=>'imooc',
*'email'=>'email'
* )
*
* INSERT user(username,password,email) VALUES('aa','aa','aa@aa.com');
*/
public static function add($data, $table)
{
$keys = array_keys($data);
array_walk($keys, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = join(',', $keys);
$values = "'" . join("','", array_values($data)) . "'";
$sql = "INSERT {$table}({$fieldsStr}) VALUES({$values});
";
//echo $sql;
return self::execute($sql);
}/**
* 解析group by
* @param $group
* @return string
*/
public static function parseGroup($group)
{
$groupStr = '';
if (is_array($group)) {
$groupStr .= ' GROUP BY' . implode(',', $group);
} else if (is_string($group) && !empty($group)) {
$groupStr .= ' GROUP BY ' . $group;
}
return empty($groupStr) ? '' : $groupStr;
}/**
* 对分组结果通过Having子句进行二次删选
* @param $having
* @return string
*/
public static function parseHaving($having)
{
$havingStr = '';
if (is_string($having) && !empty($having)) {
$havingStr .= ' HAVING ' . $having;
}
return $havingStr;
}/**
* 解析order by
* @param $order
* @return mixed
*/
public static function parseOrder($order)
{
$orderStr = '';
if (is_array($order)) {
$orderStr = ' ORDER BY ' . join(',', $order);
} elseif (is_string($order) && !empty($order)) {
$orderStr .= ' ORDER BY ' . $order;
}
return $orderStr;
}/**
* 解析显示显示条件
* limit 3
* limit 0,3
* @param $limit
* @return string
*/
public static function parseLimit($limit)
{
$limitStr = '';
if (is_array($limit)) {
if (count($limit) > 1) {
$limitStr .= ' LIMIT ' . $limit[0] . ',' . $limit[1];
} else {
$limitStr .= ' LIMIT ' . $limit[0];
}
} elseif (is_string($limit) && !empty($limit)) {
$limitStr .= ' LIMIT ' . $limit;
}
return $limitStr;
}/**
* 解析字段
* @param $fields
* @return string
*/
public static function parseFields($fields)
{
if (is_array($fields)) {
array_walk($fields, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = implode(',', $fields);
} else if (is_string($fields) && !empty($fields)) {
if (strpos($fields, '^') === false) {
//0==false 0:position
$fields = explode(',', $fields);
array_walk($fields, array('PdoMySQL', 'addSpecilChar'));
$fieldsStr = implode(',', $fields);
} else {
$fieldsStr = $fields;
}
} else {
$fieldsStr = '*';
}
return $fieldsStr;
}/**
* 通过反引号引用字段
* @param $value
*/
public static function addSpecilChar(&$value)
{
if ($value =https://www.it610.com/article/='*' || strpos($value, '.') !== false || strpos($value, '^') !== false) {
//不用做处理} elseif (strpos($value, '^') === false) {
//$valuehttps://www.it610.com/article/= 'https://www.it610.com/article/^' . trim($value) . '^';
$valuehttps://www.it610.com/article/= '' . trim($value) . '';
}}/**执行增删改操作，返回受影响的记录的条数
* @param null $sql
* @return bool|int
*/
public static function execute($sql = null)
{
$link = self::$link;
if (!$link) return false;
self::$queryStr = $sql;
if (!empty(self::$PDOStatement)) self::free();
$result = $link->exec(self::$queryStr);
self::haveErrorThrowException();
if ($result) {
self::$lastInsertId = $link->lastInsertId();
self::$numRows = $result;
return self::$numRows;
} else {
return false;
}
}/**
* 释放结果集
*/
public static function free()
{
self::$PDOStatement = null;
}public static function query($sql = '')
{
$link = self::$link;
if (!$link) return false;
//判断之前是否有结果集，如果有，释放结果集
if (!empty(self::$PDOStatement)) self::free();
self::$queryStr = $sql;
self::$PDOStatement = $link->prepare(self::$queryStr);
$res = self::$PDOStatement->execute();
self::haveErrorThrowException();
return $res;
}public static function haveErrorThrowException()
{
$obj = empty(self::$PDOStatement) ? self::$link : self::$PDOStatement;
$arrError = $obj->errorInfo();
/**
* Array
* (
* [0] => 42S02
* [1] => 1146
* [2] => Table 'test.user1' doesn't exist
* )
*/
//print_r($arrError);
if ($arrError[0] != '00000') {
self::$error = 'SQLSTATE ' . $arrError[0] . 'SQL Error: ' . $arrError[2] . '
Error SQL: ' . self::$queryStr;
self::throw_exception(self::$error);
return false;
}
if (self::$queryStr == '') {
self::throw_exception('没有执行SQL语句');
return false;
}
}/**
* 自定义错误处理
* @param $errMsg
*/
public
static function throw_exception($errMsg)
{
echo '
' . $errMsg . '';
}}require_once 'config.php';
$PdoMySQL = new PdoMYSQL();
//var_dump($PdoMySQL);
//object(PdoMYSQL)[1]
//$sql = 'select * from user';
//print_r($PdoMySQL::getAll($sql));
//$sql = 'SELECT * FROM user WHERE id>4';
//print_r($PdoMySQL->getRow($sql));
//Array ( [id] => 13 [username] => king4 [password] => king1 [email] => imoooc1@qq.com )//$sql = 'INSERT user(username,password,email) ';
//$sql .= "VALUES('imooc111','imooc122','imooc@imooc.com')";
//var_dump($PdoMySQL::execute($sql));
//int 1
//echo $PdoMySQL::$lastInsertId;
//65//$sql = 'DELETE FROM user WHERE id>15';
//var_dump($PdoMySQL::execute($sql));
//int 22//$sql = 'UPDATE user SET username="king1234" WHERE id=14';
//var_dump($PdoMySQL::execute($sql));
//int 1//$tabName = 'user';
//$priId = '3';
//print_r($PdoMySQL::findById($tabName, $priId));
//$tabName = 'user';
//$priId = '14';
////$fields = 'username,email';
//$fields = array('username', 'email');
//print_r($PdoMySQL::findById($tabName, $priId, $fields));
$tables = 'user';
//print_r($PdoMySQL::find($tables));
//print_r($PdoMySQL::find($tables,'id>=13'));
//print_r($PdoMySQL::find($tables,'id>=1','username,email'));
//print_r($PdoMySQL::find($tables, 'id>=1', '*,count(*) as total', 'email', 'count(*)>=2'));
//print_r($PdoMySQL::find($tables, 'id>5', '*', null, null, 'username DESC,id DESC'));
//print_r($PdoMySQL::find($tables, null, '*', null, null, null, '3,3'));
//print_r($PdoMySQL::find($tables, null, '*', null, null, null, array(2, 5)));
$data = https://www.it610.com/article/array('username' => 'imooct2g',
'password' => 'imooc',
'email' => 'imooc@imooc.com'
);
$PdoMySQL::add($data, 'user');
![[PDO]②③--添加记录](https://img.it610.com/image/info10/79426c55c900442dbab3253b0dcc6ae5.png)
文章图片
Paste_Image.png
推荐阅读
- 宠物情缘②
- 和小郑同学的故事②
- 道德经（七）>|道德经（七）> 不上贤
- 咬文嚼字689《盖②（盖小楼》）
- Linux文件管理②
- 2018.05.08
- 冬末光亦暗③
- 外贸B2C产品选择技巧②（google趋势分析法）
- 闲聊②|闲聊② | 焦虑
- P2P技术小结