define("MYSQL_OPEN_LOGS",true);
class mysqliHelp
{
private $db;
public function __construct()
{
//如果要查询日志log的话，怎么办
}
public function __get($name )
{
//echo "__GET:",$name;
if(in_array($name,array("db"),true))//或者isset($this->$name)
return $this->$name;
return null;
}
public function connect($host,$user,$pass,$db,$charSet='utf8',$force=false)
{
if($this->db && ($this->db instanceof mysqli) && !$force)
{
return ;
}
$this->db=new mysqli($host,$user,$pass,$db);
if (mysqli_connect_error()) {
die('Connect Error (' . mysqli_connect_errno() . ') '
. mysqli_connect_error());
}
$this->db->set_charset($charSet);
}
//$dbname string
//返回值 如果成功则返回 TRUE，失败则返回 FALSE。
function select_db ($dbname )
{
return $this->db->select_db($dbname);
}
//$query mysqli_result
//$resulttype int MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. Defaults to MYSQLI_BOTH.
//返回值 Returns an array of strings that corresponds to the fetched row or NULL if there are no more rows in resultset.
function fetch_array( $query, $resulttype = MYSQLI_ASSOC)
{
//var_dump(!null);
if(!$query || !($query instanceof mysqli_result))
return NULL;
return $query->fetch_array($resulttype);//
}
function data_seek($result,$offset)
{
return $result->data_seek($offset);
}
function fetch_assoc($query)
{
return $query->fetch_assoc();// 关联数组
}
function fetch_row($query)
{
return $query->fetch_row();// 索引数组，数字0,1。eg。。。
}
function fetch_fields($query)
{
return $query->fetch_fields();
}
//$query string
//$resultmode int
//返回值 如果成功则返回 TRUE，失败则返回 FALSE。 For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.
public function query($sql ,$resultmode=MYSQLI_STORE_RESULT )
{
if(MYSQL_OPEN_LOGS) {
$sqlstarttime = $sqlendttime = 0;
$mtime = explode(' ', microtime());
$sqlstarttime = $mtime[1]+ $mtime[0] ;
}
//真正查询
$query=$this->db->query($sql,$resultmode);
if(MYSQL_OPEN_LOGS) {
// sleep(1);
$mtime = explode(' ', microtime());
$sqlendttime = $mtime[1] + $mtime[0] ;
$sqlQueryTime = number_format($sqlendttime - $sqlstarttime,6);
//dblogs($sql, $sqlQueryTime,1);
$explain = array();
$info = $this->db->info;
if($query && preg_match("/^(select )/i", $sql)) {
$explain = $this->fetch_array($this->db->query('EXPLAIN '.$sql), MYSQLI_ASSOC );
}
$GLOBALS['mysql_debug_query'][] = array('sql'=>$sql, 'time'=>$sqlQueryTime, 'info'=>$info, 'explain'=>$explain);
}
if(!$query)
{
$this->halt('MySQL Query Error', $sql);
}
return $query;
}
//返回值 mysqli_stmt对象
function prepare($sql)
{
return $this->db->prepare($sql);
}
function affected_rows() {
return $this->db->affected_rows;
}
function error()
{
return $this->db->error;
}
function errno()
{
return $this->db->errno;
}
//result 没有
function num_rows($query)
{
return $query->num_rows;
}
//返回值 int The number of fields from a result set.
//也可以用另外一种方式 mysqliHelp->db->field_count返回。
function num_fields($query)
{
return $query->field_count;
}
function free_result($query)
{
//all methods are equivalent;
$query->free();
//$query->free_result();
//$query->close();
}
function insert_id()
{
if(($id = $this->db->insert_id)>= 0)
{
return $id;
}else
{
$idArr=$this->fetch_array($this->query("SELECt last_insert_id() as id"));
return intval($idArr[0]);
}
//return ($idArr=$this->fetch_array($this->query("SELECT last_insert_id() as id")))[0]
//return ($id = $this->db-insert_id)>= 0 ? $id : 0 ;
}
function close() {
return $this->db->close();
}
function halt($message,$sql)
{
$dberror = $this->error();
$dberrno = $this->errno();
$help_link = "http://faq.comsenz.com/?type=mysql&dberrno=".rawurlencode($dberrno)."&dberror=".rawurlencode($dberror);
echo "
MySQL Error
Message: $message
SQL: $sql
Error: $dberror
Errno.: $dberrno
Click here to seek help.
";
exit();
}
function __destruct()
{
$this->db=null;
}
}
$dbHelper=new mysqliHelp;
$dbHelper->connect('localhost', 'root', '', 'tt');
//$dbHelper->db->select_db("tt");
//$dbHelper->db->set_charset("utf8");
//这里如果是MYSQLI_USE_RESULT，下面的$dbHelper->data_seek($query,10);就会出错
$query=$dbHelper->query("select id,cateid,title from product limit 22",MYSQLI_STORE_RESULT );
//$query=$dbHelper->query("update product set createtime=UNIX_TIMESTAMP() limit 22");
//$query=$dbHelper->query("insert into `product`(`cateid`,`title`,`text`,`createtime`) values (2,'test','content',1284822691)");
//$query=$dbHelper->query("delete from `product` where id=1");
//$query=$dbHelper->query("replace into product(id,cateid,title,text,createtime) values(1,2,'this is demo','test',UNIX_TIMESTAMP())");
echo $query->num_rows."总数";
//var_dump($query);
//$row=$dbHelper->fetch_array($query);
//var_dump($row);
//finfo = $dbHelper->fetch_fields($query);
//var_dump($finfo);
// foreach ($finfo as $val) {
// printf("Name: %sn", $val->name);
// printf("Table: %sn", $val->table);
// printf("max. Len: %dn", $val->max_length);
// printf("Flags: %dn", $val->flags);
// printf("Type: %dnn", $val->type);
// }
//如果是查询操作affected_rows行数为1，num_rows为查询结果行数，num_fields为字段数目
//如果是更新或者删除操作affected_rows为受影响的行数，num_rows为null,num_fields为null
//
$dbHelper->data_seek($query,10);
$row=$dbHelper->fetch_row($query);
var_dump($row);
echo "影响行数:", "
";
var_dump($dbHelper->affected_rows());
//var_dump($dbHelper->db->affected_rows );
echo "查询行数","
";
var_dump($dbHelper->num_rows($query));
echo "列数：","
";
var_dump($dbHelper->num_fields($query));
//第1种
$sql="select id,cateid,title from product where cateid=? and title like ? and createtime<".time();
$stmt=$dbHelper->prepare($sql);
var_dump($stmt);
$stmt->bind_param('is',$cateid,$title);
$title="%%";
$cateid=10;
$stmt->execute();
$stmt->bind_result($col1, $col2,$col3);
while ($stmt->fetch()) {
printf("%s %s"."
", $col1, $col3);
}
echo "
";
$title="%%";
$cateid=4;
$stmt->execute();
$stmt->bind_result($col1, $col2,$col3);
while ($stmt->fetch()) {
printf("%s %s %s"."
", $col1, $col2,$col3);
}
$stmt->close();
//第2种
$stmt=$dbHelper->db->stmt_init();
$stmt->prepare($sql);
$stmt->bind_param('is',$cateid,$title);
$title="%%";
$cateid=10;
$stmt->execute();
$stmt->bind_result($col1, $col2,$col3);
while ($stmt->fetch()) {
//printf("%s %s"."
", $col1, $col3);
}
echo "
开始multi_query:
";
//multi_query实例
//multi_query()方法的返回值，以及 //mysqli的属性errno、error、info等只与第一条SQL命令有关，无法判断第二条及以后的命令是否在执行时发生了错误。所以在执行 //multi_query()方法的返回值是TRUE时，并不意味着后续命令在执行时没有出错。
$sql="select id,cateid,title from product where cateid=4;";
$sql.="select id,cateid,title from product where cateid=10";
$query=$dbHelper->db->multi_query($sql);
if($query)
{
do {
//下面两种方法有什么区别?
// if ($result = $dbHelper->db->store_result()) {
// while ($row = $result->fetch_row()) {
// $data[]=$row;
// }
// $result->free();
// }
if ($result = $dbHelper->db->use_result()) {//返回mysqli_result类型
//$result->data_seek(0);//返回bool
while ($row = $result->fetch_row()) {
$data[]=$row;
}
//$result->close();
}
if ($dbHelper->db->more_results()) {
printf("-----------------n");
$data[]="_______________________________";
}
} while ($dbHelper->db->next_result());
}
var_dump($data);
//exit;
//执行事务 实例
//确保操作的表时innodb类型的表
//如果是MyISAM，逻辑出错的话， 会执行所有操作，不回滚
$price=1;
$success=true;
$dbHelper->db->autocommit(0);
$result=$dbHelper->query("update product set cateid=cateid-$price where id=1000" );
if(!$result || $dbHelper->affected_rows()!=1)
{
$success=false;
}
$result=$dbHelper->query("update product set cateid=cateid+$price where id=2");
if(!$result || $dbHelper->affected_rows()!=1)
{
$success=false;
}
if($success)
{
$dbHelper->db->commit();
echo "成功";
}else
{
$dbHelper->db->rollback();
echo "失败";
}
$dbHelper->db->autocommit(1);
//var_dump( $dbHelper->insert_id());
var_dump($mysql_debug_query); //打印sql查询信息
//
?>
