实例
<?php
//数据库连接mysqlcon
function mysqlcon(){
require dirname(__FILE__).'/../config/config.php';
try {
$dbh = new PDO($sys_db_dsn,$sys_db_user,$sys_db_pws);
return $dbh;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
}
//查询mysql数据表是否存在
function table_exist($dbname,$tablename){
$sql = 'select t.table_name from information_schema.TABLES t where t.TABLE_SCHEMA ="'.$dbname.'" and t.TABLE_NAME ="'.$tablename.'"';
$con = mysqlcon();
$rs = $con->query($sql);
$col = $rs->fetchColumn();
if($col){
return 1;
}else{
return 0;
}
}
//取得sql结果集的总数
function record_total($sql){
$total=0;
$rs = mysqlcon()->query($sql);
if($rs){
return $total = $rs->fetchColumn();
}else{
echo 'record_total结果集总数查询失败，请检查SQL语句!';
}
}
//取得结果集getArray()
function getArray($sql,$setMode='PDO::FETCH_NAMED'){
if(!empty($sql)){
$rs = mysqlcon()->prepare($sql);
$rs-> execute();
if($rs->rowCount()){
$rs->setFetchMode(PDO::FETCH_NAMED);
return $rs->fetchAll();
}else{
return false;
}
}else{
return false;
}
}
//执行数据表操作doexecute()
function doExecute($sql){
if(!empty($sql)){
$rs = mysqlcon()->prepare($sql);
$rs-> execute();
if($rs->rowCount()){
return true;
}else{
return false;
}
}else{
return false;
}
}
// $sql = "SELECT * FROM staff ";
// $b = mysqlcon()->prepare($sql);
// $b->execute();
// $b->setFetchMode(PDO::FETCH_NUM);
// //$b->setFetchMode(PDO::FETCH_NAMED);
// $rs=$b->fetchAll();
/**************************************************************************
函数名称: TableAction
功能描述: 按指定的表名、字段名、与指定的字段名相等的字段值和条件进行表的操作
被访问表: none
被修改表: none
输入参数: $Tablename ---------------------------- 数据表表名
$Actiontype --------------------------- 对表所执行的操作
$Colarray ----------------------------- 字段名的数组
$Clovalue ----------------------------- 与字段名相对应的字段值数组通常为表的字段值(表单的字段名为表的列名)
$Where -------------------------------- 条件/query的sql语句
输出参数: $retun_value---------------------输出执行操作表的结果
函数返回: string
其他说明: 偶然
作者是：杨明志 创建日期：2019-3-22
**************************************************************************/
function funTableAction($Tablename,$Actiontype,$Colarray,$Clovalue,$Where,$debug=false){
require_once dirname(__FILE__).'/inc_SqlQueryBuilder.class.php';
$query=null;$sqlstr='';$MsSql='';$retun_value='';
$query= new SqlQueryBuilder($Actiontype);
$query->setTable($Tablename);
// $sqlstr=$query->BuildQuery();
$Actiontype=strtoupper($Actiontype);
switch ($Actiontype)
{
case 'SELECT':
if (!empty($Colarray))
{
$k="";$v="";
foreach($Colarray as $k=>$v)
{
$query->addColumn("$v");
}
$retun_value='';
$query->setWhere($Where);
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value = getArray($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
case 'INSERT':
if (!empty($Colarray) and !empty($Clovalue))
{
$k="";$v="";
foreach($Colarray as $k=>$v)
{
$k1="";$v1="";
foreach($Clovalue as $k1=>$v1)//与字段名对应的值
{
if (trim($v)==trim($k1))
{
$query->addValue("$v1");
$query->addColumn("$v");
}
}
}
$retun_value='';
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value=doExecute($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
case 'UPDATE':
if (!empty($Colarray) and !empty($Clovalue) and !empty($Where))
{
$k="";$v="";
foreach($Colarray as $k=>$v)
{
$k1="";$v1="";
foreach($Clovalue as $k1=>$v1)
{
if (trim($v)==trim($k1))//与字段名对应的值
{
$query->addValue("$v1");
$query->addColumn("$v");
}
}
}
$retun_value='';
$query->setWhere($Where);
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value=doExecute($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
case 'DELETE':
if (!empty($Where))
{
$retun_value='';
$query->setWhere($Where);
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value=doExecute($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
case 'COUNT':
if (!empty($Where))
{
$retun_value='';
$query->setWhere($Where);
$query->addColumn("count * ");
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value=doExecute($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
case 'QUERY':
if (!empty($Where))
{
$retun_value='';
$query->setQuery($Where);
$sqlstr=$query->BuildQuery();
if($debug) echo $sqlstr;
//$MsSql= new MsSql("China");
$retun_value=getArray($sqlstr);
}else{
echo "请检查您输入的参数有错误！";
}
break;
}
if (!empty($retun_value))
{
return $retun_value;
}else{
return null;
}
}//===TableActionNew==end
require_once dirname(__FILE__).'/inc_SqlQueryBuilder.class.php';
$Tablename='staff';$Actiontype='update';
$query=null;$sqlstr='';$MsSql='';$retun_value='';
$query= new SqlQueryBuilder($Actiontype);
$query->setTable($Tablename);
$Colarray=array('name','age');
$Clovalue=array('name'=>'朱大侠','age'=>28);
$if=' id = 3 ';
$query->setWhere($if);
foreach ($Colarray as $k1 => $v1) {
foreach ($Clovalue as $k2 => $v2) {
if(trim($v1)==trim($k2)){
$query->addColumn(trim($v1));
$query->addValue(trim($v2));
}
}
}
$sqlstr=$query->BuildQuery();
//var_dump($sqlstr);
//查询数据集:年龄小于等于40的人
$Tablename='staff';$Actiontype='select';
$Colarray=array('name','age');//查询的字段名
$if = 'age <= 40 ';//查询的条件
$rs=funTableAction($Tablename,$Actiontype,$Colarray,'',$if);//调用方法
print_r($rs);
//更新数据：id是3的人，姓名修改为：朱大侠，年龄修改为38
$Actiontype='update';
$Colarray=array('name','age');//修改对应原字段
$Clovalue=array('name'=>'朱大侠','age'=>38);//修改对应字段的值
$if = 'id = 3 ';//查询的条件
$rs=funTableAction($Tablename,$Actiontype,$Colarray,$Clovalue,$if);//调用方法
var_dump($rs);
//插入数据
$Actiontype='insert';
$Colarray=array('name','age');//修改对应原字段
$Clovalue=array('name'=>'杨先生','age'=>38);//修改对应字段的值
$if = '';//查询的条件
$rs=funTableAction($Tablename,$Actiontype,$Colarray,$Clovalue,$if);//调用方法
var_dump($rs);
//删除刚刚插入的数据
$Actiontype='delete';
//$Colarray=array('name','age');//修改对应原字段
//$Clovalue=array('name'=>'杨先生','age'=>38);//修改对应字段的值
$if = " name = '杨先生' and age = 38";//查询的条件
$rs=funTableAction($Tablename,$Actiontype,'','',$if);//调用方法
var_dump($rs);
//var_dump(table_exist('ymz','user'));
// $sql = "update staff set name='赵瑞龙',age='18' where id =3";
// $b = mysqlcon()->prepare($sql);
// var_dump($b->execute());
// echo $b->rowCount();
// $b->setFetchMode(PDO::FETCH_NUM);
// //$b->setFetchMode(PDO::FETCH_NAMED);
// $rs=$b->fetchAll();
//print_r($rs);
//$ret = $a->query($sql);
//echo record_total('select count(*) from staff where age <= 40');
//var_dump($ret);
// foreach($ret as $row){
// print_r($row);
// }
?>运行实例 »
点击 "运行实例" 按钮查看在线实例
