PHP operation mysqli (sample code)_PHP tutorial-PHP Tutorial-php.cn

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(/*mysqli_result*/ $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;

}

/* MySQLi class
Object-oriented interface Procedural-oriented interface Alias Description
Attributes
$mysqli->affected_row s mysqli_affected_rows() N/A Get the previous Mysql Number of rows affected by the operation
$mysqli->client_info mysqli_get_client_info() N/A Returns the Mysql client version information in string type
$mysqli->client_version mysqli_get_client_version() N/A Returns integer type Mysql client version information
$mysqli->connect_errno mysqli_connect_errno() N/A Returns the error code of the last connection call
$mysqli->connect_error mysqli_connect_error() N/A Returns the last error code described in a string The error code of a connection call
$mysqli->errno mysqli_errno() N/A Returns the error code generated by the most recent function call
$mysqli->error mysqli_error() N/A Returns the string description The error code generated by the latest function call
$mysqli->field_count mysqli_field_count() N/A Returns the number of columns obtained by the latest query
$mysqli->host_info mysqli_get_host_info() N/A Returns one A string that can represent the connection type used
$mysqli->protocol_version mysqli_get_proto_info() N/A Returns the version information of the Mysql protocol used
$mysqli->server_info mysqli_get_server_info() N/A Returns the Mysql service Server version information
$mysqli->server_version mysqli_get_server_version() N/A Returns integer Mysql server version information
$mysqli->info mysqli_info() N/A Retrieval of the most recently executed query Information
$mysqli->insert_id mysqli_insert_id() N/A Returns the id automatically generated and used in the last query
$mysqli->sqlstate mysqli_sqlstate() N/A Returns the SQLSTATE error of the previous Mysql operation
                                                                                                                                                                                $mysqli-> Submit function
mysqli->change_user() mysqli_change_user() N/A Change the user of the specified database connection
mysqli->character_set_name(), mysqli->client_encoding mysqli_character_set_name() mysqli_client_encoding( ) Returns the database connection Default character set
mysqli->close() mysqli_close() N/A Close the previously opened database connection
mysqli->commit() mysqli_commit() N/A Commit the current transaction
mysqli:: __construct() mysqli_connect() N/A Open a new connection to the Mysql server [Note: static method]
mysqli->debug() mysqli_debug() N/A Perform debugging operations
mysqli-> dump_debug_info() mysqli_dump_debug_info() N/A Dump debugging information into the log
mysqli->get_charset() mysqli_get_charset() N/A Return the character set of the object
mysqli->get_connection_stats( ) mysqli_get_connection_stats( ) N/A Returns client connection statistics.Available only with mysqlnd.
mysqli->get_client_info() mysqli_get_client_info() N/A Returns the Mysql client version described by a string
mysqli->get_client_stats() mysqli_get_client_stats() N/A Returns the version of each client process Statistics . Available only with mysqlnd.
mysqli->get_cache_stats() mysqli_get_cache_stats() N/A Returns the client’s zval cache statistics. Available only with mysqlnd.
mysqli->get_server_info() mysqli_get_server_info() N/A No documentation
mysqli->get_warnings() mysqli_get_warnings() N/A No documentation
mysqli ::init() mysqli_init() N/ A initializes mysqli and returns a resource type used by mysqli_real_connect. [Not on the object, but the $mysqli object it returns]
mysqli->kill() mysqli_kill() N/A Request the server to kill a Mysql thread
mysqli->more_results() mysqli_more_results() N/A Check if a multi-statement query has other query result sets
mysqli->multi_query() mysqli_multi_query() N/A Execute a multi-statement query on the database
mysqli->next_result() mysqli_next_result () N/A Prepare the next result set from multi_query
connection, or try to reconnect if that connection is broken
mysqli->prepare() mysqli_prepare() N/A Prepare a SQL statement for execution
mysqli->query() mysqli_query() N/A A Execute a query on the database
mysqli->real_connect() mysqli_real_connect() N/A Open a connection to the Mysql server
mysqli->real_escape_string(), mysqli->escape_string() mysqli_real_escape_string( ) mysqli_escape_string() Escapes special characters in a string used in SQL statements. This conversion will take into account the current character set of the connection.
        mysqli->real_query() mysqli_real_query() N/A 执行一个SQL查询
        mysqli->rollback()   mysqli_rollback()   N/A 回滚当前事务
        mysqli->select_db() mysqli_select_db() N/A 为数据库查询选择默认数据库
        mysqli->set_charset()    mysqli_set_charset()    N/A 设置默认的客户端字符集
        mysqli->set_local_infile_default()   mysqli_set_local_infile_default()   N/A 清除用户为load data local infile命令定义的处理程序
        mysqli->set_local_infile_handler()   mysqli_set_local_infile_handler()   N/A 设置LOAD DATA LOCAL INFILE命令执行的回调函数
        mysqli->ssl_set()    mysqli_ssl_set()    N/A 使用SSL建立安装连接
        mysqli->stat()   mysqli_stat()   N/A 获取当前系统状态
        mysqli->stmt_init() mysqli_stmt_init() N/A 初始化一个语句并且返回一个mysqli_stmt_prepare使用的对象
        mysqli->store_result()   mysqli_store_result()   N/A 传输最后一个查询的结果集
        mysqli->thread_id() mysqli_thread_id() N/A 返回当前连接的线程ID
        mysqli->thread_safe()    mysqli_thread_safe()    N/A 返回是否设定了线程安全
        mysqli->use_result() mysqli_use_result() N/A 初始化一个结果集的取回
    */

    /*
            MySQL_STMT
        面向对象接口过程化接口   别名（不要使用）    描述
        属性
        $mysqli_stmt->affected_rows mysqli_stmt_affected_rows() N/A 返回最后一条倍执行的语句改变，删除或插入的总行数
        $mysqli_stmt->errno mysqli_stmt_errno() N/A 返回最近一次语句调用的错误代码
        $mysqli_stmt->error mysqli_stmt_error() N/A 返回最后一条语句错误的字符串描述
        $mysqli_stmt->field_count    mysqli_stmt_field_count()   N/A 返回给定语句得到的字段数量
        $mysqli_stmt->insert_id mysqli_stmt_insert_id() N/A 获取前一个INSERT操作生成的ID
        $mysqli_stmt->num_rows   mysqli_stmt_num_rows() N/A 返回语句结果集中的行数
        $mysqli_stmt->param_count    mysqli_stmt_param_count()   mysqli_param_count()    返回给定语句中参数数量
        $mysqli_stmt->sqlstate   mysqli_stmt_sqlstate() N/A 返回前一个语句操作的SQLSTATE错误代码
        方法
        mysqli_stmt->attr_get() mysqli_stmt_attr_get() N/A 用于获取语句属性的当前值
        mysqli_stmt->attr_set() mysqli_stmt_attr_set() N/A 用于修改prepared语句的行为
        mysqli_stmt->bind_param()    mysqli_stmt_bind_param()    mysqli_bind_param() 将一个变量作为参数绑定到prepared语句上
        mysqli_stmt->bind_result()   mysqli_stmt_bind_result()   mysqli_bind_result()    将一个变量绑定到一个prepared语句上用于结果存储
        mysqli_stmt->close() mysqli_stmt_close() N/A 关闭一个prepared语句
        mysqli_stmt->data_seek() mysqli_stmt_data_seek() N/A 查看语句结果集中的任意行
mysqli_stmt->execute() mysqli_stmt_execute() mysqli_execute() Execute a prepared query
mysqli_stmt->fetch() mysqli_stmt_fetch() mysqli_fetch() Fetch the results from a prepared statement into a qualified variable
mysqli_stmt- >free_result() mysqli_stmt_free_result() N/A Releases the memory occupied by the result set stored by the given statement processing
$mysqli_stmt->get_result() mysqli_stmt_get_result N/A No documentation available only for mysqlnd.
mysqli_stmt->get_warnings() N/A No documentation qlnd.
$mysqli_stmt->next_result() mysqli_stmt_next_result() N/A No documentation. Available only for mysqlnd.
mysqli_stmt->num_rows() mysqli_stmt_num_rows() N/A View attribute $mysqli_stmt->num_rows
mysqli_stmt->prepare() mysqli_stmt_prepare() N /A Prepare a SQL statement for execution
mysqli_stmt->reset() mysqli_stmt_reset() N/A Reset a prepared statement
mysqli_stmt->result_metadata() mysqli_stmt_result_metadata() mysqli_get_metadata() Return the result set element from a prepared statement Data
mysqli_stmt-> send_long_data() mysqli_stmt_send_long_data() mysqli_send_long_data() Send data block
mysqli_stmt->store_result() mysqli_stmt_store_result() N/A Transfer a result set from a prepared statement

*/

/*
MySQLi_RESULT
Object-oriented interface Procedural interface Alias (do not use) Description
Attributes
$mysqli_result->current_field mysqli_field_tell( ) N/A Get the current field in the result set pointer Starting position
$mysqli_result->field_count mysqli_num_fields() N/A Get the number of fields in the result
$mysqli_result->lengths mysqli_fetch_lengths() N/A Return the value length of each column in the current row in the result set, return Array
$mysqli_result->num_rows mysqli_num_rows() N/A Get the number of rows in the result
Method
mysqli_result->data_seek() mysqli_data_seek() N/A Adjust the result pointer in the result to any Row
mysqli_result->fetch_all() mysqli_fetch_all() N/A Fetch all result rows and return the result set as associative data, numerically indexed array, or both. Available only with mysqlnd.
mysqli_result->fetch_array() mysqli_fetch_array() N/A Fetch a row of results as an associative array, a numerically indexed array, or both
mysqli_result->fetch_assoc() mysqli_fetch_assoc() N /A Fetch a row of results in an associative array
mysqli_result->fetch_field_direct() mysqli_fetch_field_direct() N/A Fetch a single field of metadata
mysqli_result->fetch_field() mysqli_fetch_field() N / A Returns the next field in the result set N/A with Return the current row in a result set as an object
mysqli_result->fetch_row() mysqli_fetch_row() N/A Return a row of results as an enumeration array
mysqli_result->field_seek() mysqli_field_seek() N /A Set the result pointer to a specific field start position
mysqli_result->free(), mysqli_result->close, mysqli_result->free_result mysqli_free_result() N/A Release the memory associated with a result set
*/
/*Note the difference between MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT
In fact, the difference between these two parameters is still very big.
(1) The difference lies in retrieving the rows of the result set from the server.
(2) MYSQLI_USE_RESULT starts the query, but does not actually fetch any rows
(3) MYSQLI_STORE_RESULT retrieves all rows immediately
(4) MYSQLI_STORE_RESULT The rows are extracted when the result set is retrieved from the server, And allocate memory for it, store it in the client, and then call
mysqli_fetch_array() will never return an error, because it only separates the rows from the data structure that already retains the result set, mysqli_fetch_array() always returns NULL Indicates that the end of the result set has been reached.
(5) MYSQLI_USE_RESULT itself does not retrieve any rows, but only starts a row-by-row retrieval, which means that you must call
mysqli_fetch_array() for each row to complete it yourself. In this case, although under normal circumstances, mysqli_fetch_array() returning NULL still indicates that the end of the result set has been reached, it may also indicate that an error occurred while communicating with the server.

If it is MYSQLI_USE_RESULT, after the query obtains the mysqli_result object, an error will occur when executing data_seek, because
mysqli_result::data_seek() [mysqli-result.data-seek]: Function cannot be used with MYSQL_USE_RESULT

Compared with MYSQLI_USE_RESULT, MYSQLI_STORE_RESULT has higher memory and processing requirements. Because the entire result set is maintained on the client, the cost of memory allocation and creation of data structures is very huge. If you want to retrieve it all at once For multiple rows, MYSQLI_USE_RESULT can be used.
MYSQLI_USE_RESULT has lower memory requirements because only enough space is allocated for a single row processed each time. This is faster because there is no need to create complex data structures for the result set.
On the other hand, MYSQLI_USE_RESULT places a greater load on the server, which must retain rows in the result set until the client seems suitable to retrieve all rows.
    */
}

$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);

/* fetch values */
while ($stmt->fetch()) {
    printf("%s %s"."
", $col1, $col3);
}

echo "
";
$title="%%";
$cateid=4;
$stmt->execute();
$stmt->bind_result($col1, $col2,$col3);

/* fetch values */
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);

/* fetch values */
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 {
        /* store first result set */
        //下面两种方法有什么区别?
        // 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();
        }


        /* print divider */
        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查询信息

//
?>