php采用绑定变量形式访问mysql数据库封装类

<?
/**
* 采用绑定变量形式进行对数据库添加,删除,修改,查询操作封装,目前至少9个一下绑定变量,如果需要更多可以进行扩展
* 数据库编码utf8,网页,文件编码utf8,不会出现乱码
*/

class DB
{
var $mysqli;

function DB()
{
$this->mysqli = new mysqli(“127.0.0.1”, “root”, “123456”, “message”);
$this->mysqli->set_charset(‘utf8’);

}
//绑定参数
function bindParam($stmt,$paramtype,$paramvalue)
{
if(is_array($paramvalue))
{
$size = count($paramvalue);
switch($size)
{
case 1:$stmt->bind_param($paramtype,$paramvalue[0]);
break;
case 2: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1]);
break;
case 3: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] );
break;
case 4: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3] );
break;
case 5: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3],$paramvalue[4] );
break;
case 6: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3],$paramvalue[4] ,$paramvalue[5]);
break;
case 7: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3],$paramvalue[4] ,$paramvalue[5] ,$paramvalue[6]);
break;
case 8: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3],$paramvalue[4] ,$paramvalue[5] ,$paramvalue[6],$paramvalue[7]);
break;
case 9: $stmt->bind_param($paramtype,$paramvalue[0],$paramvalue[1],$paramvalue[2] ,$paramvalue[3],$paramvalue[4] ,$paramvalue[5] ,$paramvalue[6],$paramvalue[7],$paramvalue[8]);
break;
}
}
else
{
$stmt->bind_param($paramtype,$paramvalue);
}
}
//目前只支持9个参数以内,更新删除修改
function saveOrUpdateOrDelete($sql,$paramtype,$paramvalue)
{

$stmt = $this->mysqli->prepare($sql);
if($stmt)
{
$this->bindParam($stmt,$paramtype,$paramvalue);
$flag = $stmt->execute();
$stmt->close();
$this->closeConn();
return $flag;
}
else
{
echo ‘数据库连接出错或者sql出错:请检查sql: ‘.$sql;
return 0;
}

}
/**
* 添加数据
*/
function add($table,$field,$paramtype,$paramvalue)
{
$sql = “insert into “.$table.'(‘.$field.’) values(‘;
$fs = explode(‘,’,$field);
$fscount = count($fs);
$i=0;
$bindparam = ”;
while($i<$fscount)
{
$bindparam = $bindparam.’?’;
if($i<$fscount-1)
{
$bindparam = $bindparam.’,’;
}
$i++;
}
$sql = $sql.$bindparam.’)’;
return $this->saveOrUpdateOrDelete($sql,$paramtype,$paramvalue);
}
/**
* 修改数据
*/
function update($table,$field,$where,$paramtype,$paramvalue)
{
$sql = “update “.$table.’ set ‘;
$fs = explode(‘,’,’username’);
$fs = Array(‘username’);
$size = count($fs);
for($i=0;$i<$size;$i++)
{
echo count($fs);
$sql = $sql.$fs[$i].’=?,’;
}

$sql = substr($sql,0,strlen($sql)-1).’ where ‘;

$whereParam = explode(‘,’,$where);
$size = count($whereParam);
for($i=0;$i<$size;$i++)
{

$sql = $sql.$whereParam[$i].’=? and ‘;
}
$sql = $sql .’ 0=0 ‘;
return $this->saveOrUpdateOrDelete($sql,$paramtype,$paramvalue);
}
/**
* 删除数据
*/
function del($table,$where,$paramtype,$paramvalue)
{
$sql = “delete from “.$table.’ where ‘;
$whereParam = explode(‘,’,$where);
$size = count($whereParam);
for($i=0;$i<$size;$i++)
{

$sql = $sql.$whereParam[$i].’=? and ‘;
}
$sql = $sql .’ 0=0 ‘;
return $this->saveOrUpdateOrDelete($sql,$paramtype,$paramvalue);
}

/**
* 获取结果集合
*/
function getResult($table,$field,$where,$paramtype,$paramvalue)
{
$sql = “select “.$field.’ from ‘.$table.’ where ‘;
$whereParam = explode(‘,’,$where);
$size = count($whereParam);
for($i=0;$i<$size;$i++)
{

$sql = $sql.$whereParam[$i].’=? and ‘;
}
$sql = $sql .’ 0=0 ‘;
$stmt = $this->mysqli->prepare($sql);
$list = Array();
if($stmt)
{

$this->bindParam($stmt,$paramtype,$paramvalue);
$stmt->execute();
$rs = $stmt->result_metadata();
$cloumn = Array();
$cloumnName = Array();
$i=0;
while($field = $rs->fetch_field())
{
$cloumn[$i] = $field->name;
$cloumnName[$i] = $field->name;
$i++;
}
$size = count($cloumn);
switch($size)
{
case 1:
$stmt->bind_result($cloumn[0]);
break;
case 2:
$stmt->bind_result($cloumn[0],$cloumn[1]);
break;
case 3:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] );
break;
case 4:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3] );
break;
case 5:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3],$cloumn[4] );
break;
case 6:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3],$cloumn[4] ,$cloumn[5]);
break;
case 7:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3],$cloumn[4] ,$cloumn[5] ,$cloumn[6]);
break;
case 8:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3],$cloumn[4] ,$cloumn[5] ,$cloumn[6],$cloumn[7]);
break;
case 9:
$stmt->bind_result($cloumn[0],$cloumn[1],$cloumn[2] ,$cloumn[3],$cloumn[4] ,$cloumn[5] ,$cloumn[6],$cloumn[7],$cloumn[8]);
break;
}

$j=0;
while($stmt->fetch())
{
$row =Array();
$n = 0;
while($n<$size)
{

$row[$cloumnName[$n]]=$cloumn[$n];
$n++;
}
$list[$j] =$row;
$j++;
}
$stmt->close();
$this->closeConn();
}
else
{
$list[0]=’数据库连接出错或者sql出错:请检查sql: ‘.$sql;

}

return $list;
}
/**
* 关闭数据库连接
*/
function closeConn()
{
$this->mysqli->close();
}

}
$db = new DB();
$table = ‘users’;
$field = ‘*’;
$where = ‘userid’;
$paramValue = Array(3);//对应userid值
//获取列表
$list = $db->getResult($table,$field,$where,’d’,$paramValue);//d 表示整数
print_r($list);

//添加数据
$db = new DB();
$table = ‘users’;
$field = ‘username,password’;
$paramValue = Array(‘admin’,’admin’);//对应username,userid值
$flag = $db->add($table,$field,’ss’,$paramValue);//ss 表示两个字符串
echo $flag.'<br>’;
//修改数据
$db = new DB();
$table = ‘users’;
$field = ‘username’;
$where = ‘userid’;
$paramValue = Array(‘aaffaaaf’,2);//对应username,userid值
$flag = $db->update($table,$field,$where,’sd’,$paramValue);//sd 表示一个字符串,一个整数
echo $flag.'<br>’;
//删除数据
$db = new DB();
$table = ‘users’;
$where = ‘userid’;
$paramValue = Array(1);//对应userid值
$flag = $db->del( $table,$where,’d’,$paramValue);//d 表示一个整数
echo $flag.'<br>’;
?>

 

 

Tagged on: , ,

One thought on “php采用绑定变量形式访问mysql数据库封装类

发表评论

电子邮件地址不会被公开。 必填项已用*标注


*