实例
<?php
error_reporting(E_ALL || ~E_NOTICE);
//连接函数
if (!function_exists('myconnect')) {
function myconnect($dbType,$host,$dbname,$user,$pw,$port=0,$charset='utf8') {
if ($dbType == 'mysql') {
//端口号设置
$port = ($port == 0) ? 3306 : $port;
//dsn 建立
$dsn = "{$dbType}:host={$host};dbname={$dbname};charset={$charset};port={$port}";
// echo $dsn;
}
if ($dbType == 'sqlsrv') {
$port = ($port == 0) ? 1433 : $port;
$dsn = "{$dbType}:server=tcp:{$host},{$port};database={$dbname};";
// echo $dsn;
}
try{
$pdo = new PDO($dsn,$user,$pw);
// echo "<h3>数据库连接成功！</h3>";
}catch (PDOException $e){
die('连接失败。'.$e->getMessage());
}
return $pdo;
}
}
//新增函数
if (!function_exists('myinsert')) {
function myinsert($pdo,$table,$data=[]) {
$sql = "INSERT INTO {$table} (";
// Field 部分
foreach (array_keys($data) as $field) {
$sql .= $field.',';
}
//去掉尾部 , 并加上）
$sql = rtrim(trim($sql),',').') ';
//VALUE 部分
$sql .= ' VALUES (';
foreach (array_keys($data) as $value) {
$sql .= ':'.$value.',';
}
$sql = rtrim(trim($sql),',').') ';
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '新增记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>新增记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//更新函数
if (!function_exists('myupdate')) {
function myupdate($pdo,$table,$data=[],$where) {
$sql = "UPDATE {$table} SET ";
// Field 部分
foreach (array_keys($data) as $field) {
$sql .= $field.'=' .':'.$field.',';
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}else{
exit('条件不能为空');
}
echo($sql);
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '修改记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>修改记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//删除函数
if (!function_exists('mydelete')) {
function mydelete($pdo,$table,$where) {
$sql = "DELETE FROM {$table} ";
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}else{
exit('条件不能为空');
}
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($data as $field => $value) {
$stmt->bindValue(":{$field}",$value);
}
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '删除记录成功！';
return true;
} else {
return false;
}
} else {
// echo '<h3>修改记录失败</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
//查询单条函数
if (!function_exists('myselect_one')) {
function myselect_one($pdo,$table,$fields,$where,$order) {
$sql = "SELECT TOP 1 ";
if (is_array($fields)) {
foreach ($fields as $field) {
$sql .= $field.', ';
}
} else {
$sql .= $fields;
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//FROM 部分
$sql .= ' FROM '.$table ;
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}
//ORDER BY 部分
if (!empty($order)) {
$sql .= ' ORDER BY ' .$order;
}
// echo $sql;
//创建pdo预处理对象
// mssql rowCount()无法返回行数
// mysql rowCount()可返回受影响的行数
// rowCount() 用 prepare()方法时，仅支持CURSOR_SCROLL
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
if ($stmt->execute()) {
// var_dump($stmt->rowCount());
if ($stmt->rowCount() > 0) {
echo '查询记录成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetch(PDO::FETCH_ASSOC);
} else {
echo '没有匹配的记录！';
return false;
}
} else {
// print_r($stmt->errorInfo());
return false;
}
}
}
//查询符合条件的全部记录函数
if (!function_exists('myselect_all')) {
function myselect_all($pdo,$table,$fields,$where,$order) {
$sql = "SELECT ";
if (is_array($fields)) {
foreach ($fields as $field) {
$sql .= $field.', ';
}
} else {
$sql .= $fields;
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
//FROM 部分
$sql .= ' FROM '.$table ;
//where 部分
if(!empty($where)) {
$sql .= ' WHERE '. $where;
}
//ORDER BY 部分
if (!empty($order)) {
$sql .= ' ORDER BY ' .$order;
}
//创建pdo预处理对象
$stmt = $pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
// var_dump($pdo->prepare($sql));
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
echo '查询记录成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
echo '没有匹配的记录！';
return false;
}
} else {
// print_r($stmt->errorInfo());
return false;
}
}
}
//调用存储过程函数
if (!function_exists('myexecute')) {
function myexecute($pdo,$proc,$params=[]) {
$sql = "EXEC {$proc} ";
// 参数 部分
foreach (array_keys($params) as $param) {
$sql .= ':'.$param.',';
}
//去掉尾部 ,
$sql = rtrim(trim($sql),',');
// exit($sql);
//创建pdo预处理对象
$stmt = $pdo->prepare($sql);
foreach ($params as $param => $value) {
$stmt->bindValue(":{$param}",$value);
}
if ($stmt->execute()) {
echo '调用存储过程成功！';
// $stmt->setFetchMode(PDO::FETCH_ASSOC);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
echo '<h3>调用存储过程失败！</h3>';
// print_r($stmt->errorInfo());
return false;
}
}
}
?>运行实例 »
点击 "运行实例" 按钮查看在线实例
实例
<?php require 'lib/myfun_pdo.php'; // 连接测试 mssql $dbType = 'sqlsrv'; $host = '127.0.0.1'; $dbname = 'WsErp'; $user = 'sa'; $pw = '13650158099'; $pdo = myconnect($dbType,$host,$dbname,$user,$pw); // 连接测试 mysql // $dbType = 'mysql'; // $host = '127.0.0.1'; // $dbname = 'MyTest'; // $user = 'root'; // $pw = '13650158099'; // $pdo = myconnect($dbType,$host,$dbname,$user,$pw); //新增测试 echo '<br>'; $table = 'bs_supplier'; $data = ['keyid'=>'test2', 'name'=>'test2_update']; myinsert($pdo,$table,$data); //修改测试 echo '<br>'; $table = 'bs_supplier'; $data = ['jname'=>'pdo_u', 'name'=>'test2_update']; $where = "keyid='test2'"; myupdate($pdo,$table,$data,$where); //删除测试 echo '<br>'; $table = 'bs_supplier'; $where = "keyid='test2'"; mydelete($pdo,$table,$where); //单条查询 echo '<br>'; $table = 'bs_supplier'; $fields = ['keyid','name','jname']; // $where = "keyid like 'xx%'"; $where = "keyid like '%'"; $order = "keyid asc"; echo '<pre>' .print_r(myselect_one($pdo,$table,$fields,$where,$order),true).'</pre>'; //符合条件全部记录查询 echo '<br>'; $table = 'bs_supplier'; $fields = ['keyid','name','jname']; $where = "keyid like 'F%'"; $order = "keyid asc"; echo '<pre>' .print_r(myselect_all($pdo,$table,$fields,$where,$order),true).'</pre>'; //调用存储过程 echo '<br>'; $proc = 'mp_stat_supplier'; $params = ['para1'=>'2017-01-01', 'para2'=>'2017-01-05','para3'=>'FC02']; echo '<pre>' .print_r(myexecute($pdo,$proc,$params),true).'</pre>'; ?>
运行实例 »
点击 "运行实例" 按钮查看在线实例
