常用的mysql_data.php备忘记录

<?php
// 针对MySQL数据库中的数据进行添加、修改、删除等操作
require_once "../common/db.mysql.class.php";//加载连接封装类
require_once "../common/db.conf.php";//加载配置

//=======================添加记录===========================
//统一添加数据操作
function insertInfo($tablename, $add_arr, $isshow=null)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);
	
	$addid = insertData($mbdb, $tablename, $add_arr, $isshow);

	$mbdb->free(); 

	return $addid;
}

//添加数据，传入数据库对象
function insertData($mbdb, $tablename, $add_arr, $isshow=null)
{
	$addid = 0;
	if(!empty($add_arr)) {
		$sqlfield = "";
		$sqlvalue = "";
		$i = 0;
		foreach($add_arr as $akey => $aval) 
		{
			$sqlfield .= $akey;
			$sqlvalue .= $aval;
			if((++$i) < count($add_arr)) {
				$sqlfield .= ", ";
				$sqlvalue .= ", ";
			}
		}

		$sql = "INSERT IGNORE INTO ".$tablename."(".$sqlfield.") VALUES(".$sqlvalue.")";
		if(empty($isshow)) echo "MySQL InsertSQL : ".$sql.PHP_EOL;
		$result = $mbdb->query($sql) or die($mbdb->error());
		//添加完成，直接获取新记录ID
		$addid = $mbdb->insert_id();	
	}

	return $addid;
}

//从一张表查询导入数据至另一张表中
function insertIntoTable($totablename, $fromtablename, $add_arr, $where, $isshow=null)
{
	//global $mbdb;
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);
	
	$addid = insertIntoData($mbdb, $totablename, $fromtablename, $add_arr, $where, $isshow);

	$mbdb->free(); 

	return $addid;
}

//导入数据，传入数据库对象
function insertIntoData($mbdb, $totablename, $fromtablename, $add_arr, $where, $isshow=null)
{
	$addid = 0;
	if(!empty($add_arr)) {
		$sqlfield = "";
		$sqlvalue = "";
		$sqlext = "";

		$i = 0;
		foreach($add_arr as $akey => $aval) 
		{
			$sqlfield .= $akey;
			$sqlvalue .= $aval;
			if((++$i) < count($add_arr)) {
				$sqlfield .= ", ";
				$sqlvalue .= ", ";
			}
		}
		
		if(!empty($where)) {
			$i = 0;		
			foreach($where as $wval) {
				$sqlext .= $wval;
				if((++$i) < count($where)) $sqlext .= " AND ";
			}
			$sqlext = " WHERE ".$sqlext;
		}

		$sql = "INSERT IGNORE INTO ".$totablename."(".$sqlfield.") SELECT ".$sqlvalue." FROM ".$fromtablename." ".$sqlext;
		if(empty($isshow)) echo "MySQL InsertIntoTable SQL : ".$sql.PHP_EOL;
		$result = $mbdb->query($sql) or die($mbdb->error());
		//导入完成，直接获取新记录ID
		$addid = $mbdb->insert_id();	
	}

	return $addid;
}

//=======================更新记录===========================
//统一更新数据操作
function updateInfo($tablename, $upt_arr, $where=null, $isshow=null)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);
	
	$db_affected_rows = updateData($mbdb, $tablename, $upt_arr, $where, $isshow);
    
	$mbdb->free(); 

	return $db_affected_rows;
}

//更新数据，传入数据库对象
function updateData($mbdb, $tablename, $upt_arr, $where=null, $isshow=null)
{
	$sqlext = "";
	$sqlupt = "";
	if(!empty($where)) {		
		$i = 0;			
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;
	}
	if(!empty($upt_arr)) {
		$j = 0;
		foreach($upt_arr as $uptval) {
			$sqlupt .= $uptval;
			if((++$j) < count($upt_arr)) $sqlupt .= " , ";
		}
		$sqlupt = " SET ".$sqlupt;
	}

	$sql = "UPDATE IGNORE ".$tablename." ".$sqlupt.$sqlext;
	if(empty($isshow)) echo "MySQL UpdateSQL : ".$sql.PHP_EOL;
	$result = $mbdb->query($sql) or die($mbdb->error());

	return $mbdb->db_affected_rows();
}

//=======================删除记录===========================
//统一删除数据操作
function delInfo($tablename, $where, $isshow=null)
{
	if(!empty($where)) {
		$sqlext = "";
		$sqlupt = "";
		$i = 0;		
		$j = 0;
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;

		//global $mbdb;
		global $db_guesssound_conf;
		$mbdb = new MBMySQL($db_guesssound_conf);
		$sql = "DELETE FROM ".$tablename.$sqlext;
		if(empty($isshow)) echo "MySQL DelSQL : ".$sql.PHP_EOL;

		$result = $mbdb->query($sql) or die($mbdb->error());
		
		$mbdb->free();
	}
}

//=======================查询记录===========================
//获取记录条数
function getsqltotal($tablename, $where=null)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);
    $sqlext = "";
	if(!empty($where)) {
		$i = 0;		
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;
	}

	//$sql = "SELECT * FROM ".$tablename.$sqlext;
	$sql = "SELECT COUNT(*) as countnum FROM ".$tablename.$sqlext;
	$result = $mbdb->query($sql);
	//return $mbdb->db_num_rows();

	$qryinfo = array();
	while($row = $mbdb->fetch_array($result)) {
		$qryinfo[] = array(
			"countnum" => $row['countnum']
		);
	}

	$mbdb->free();

	$countnum = 0;
	if(!empty($qryinfo)) {
		$countnum = $qryinfo[0]['countnum'];
	}
	
	return $countnum;
}

//查询记录是否存在
function qrysql_exists($tablename, $where)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);

	$sqlext = "";
	if(!empty($where)) {
		$i = 0;		
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;
	}

	$sql = "SELECT * FROM ".$tablename.$sqlext;

	$result = $mbdb->query($sql);

	return $mbdb->db_num_rows();	
}

//获取表中的最大ID值
function qrysql_maxid($tablename, $idval, $where=null)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);

	$sqlext = "";
	if(!empty($where)) {
		$i = 0;		
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;
	}

	$sql = "SELECT MAX(".$idval.") as ".$idval." FROM ".$tablename.$sqlext;
	
	$result = $mbdb->query($sql);
	
	$qryinfo = array();
	while($row = $mbdb->fetch_array($result)) {
		$qryinfo[] = array(
			(string)$idval => (int)$row[$idval]
		);
	}

	$mbdb->free();

	return $qryinfo;
}

//表中的唯一索引field：查询数据库
function qrysql_table_field($tablename, $idval, $where=null)
{
	global $db_guesssound_conf;
	$mbdb = new MBMySQL($db_guesssound_conf);

	$sqlext = "";
	if(!empty($where)) {
		$i = 0;		
		foreach($where as $wval) {
			$sqlext .= $wval;
			if((++$i) < count($where)) $sqlext .= " AND ";
		}
		$sqlext = " WHERE ".$sqlext;
	}

	$sql = "SELECT $idval FROM ".$tablename.$sqlext." GROUP BY id";
	
	$result = $mbdb->query($sql);
	
	$qryinfo = array();
	while($row = $mbdb->fetch_array($result)) {
		$qryinfo[] = array(
			(string)$idval => (int)$row[$idval]
		);
	}

	$mbdb->free();

	return $qryinfo;
}