PHP Operation mysqli (sample code) _php instance

Source: Internet
Author: User
Tags commit data structures explode mysql client mysql query prepare rollback stmt

Define ("Mysql_open_logs", true);

Class Mysqlihelp


Private $db;

Public Function __construct ()


What to do if you want to query log logs


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)




$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

The return value returns TRUE if successful, and FALSE if it fails.

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.

Return value Returns an array of strings which corresponds to the fetched row or NULL if there are no further 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 ();//associative array


function Fetch_row ($query)


return $query->fetch_row ()//index array, number 0, 1. eg..


function Fetch_fields ($query)


return $query->fetch_fields ();


$query string

$resultmode int

The return value returns TRUE if successful, and FALSE if it fails. For SELECT, show, DESCRIBE or EXPLAIN mysqli_query () would 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];


Real Query

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


return value Mysqli_stmt Object

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 No

function Num_rows ($query)


return $query->num_rows;


Returns the value int the number of fields from a result set.

You can also return Mysqlihelp->db->field_count in a different way.

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;



$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 = "". Rawurlencode ($dberrno). &dberror= ". Rawurlencode ($dberror);

echo "<div style=\" Position:absolute;font-size:11px;font-family:verdana,arial;background: #EBEBEB;p adding:0. 5em;\ ">

<b>mysql error</b><br>

<b>message</b&gt: $message <br>

<b>sql</b&gt: $sql <br>

<b>error</b&gt: $dberror <br>

<b>errno.</b&gt: $dberrno <br>

<a href=\ "$help _link\" target=\ "_blank\" >click

</div> ";

Exit ();


function __destruct ()




/* Mysqli class

Object-oriented interface for process interface alias description


$mysqli->affected_rows mysqli_affected_rows () n/A Get the number of affected rows of the previous MySQL operation

$mysqli->client_info Mysqli_get_client_info () n/A returns the MySQL client version information of the string type

$mysqli->client_version mysqli_get_client_version () n/A returns the MySQL client version information of the integral type

$mysqli->connect_errno Mysqli_connect_errno () n/A returns the error code for the last connection call

$mysqli->connect_error Mysqli_connect_error () n/A returns the error code for the last connection call described by a string

$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 error code generated by the most recent function call described by the string

$mysqli->field_count Mysqli_field_count () n/A returns the number of columns that were fetched by the most recent query

$mysqli->host_info Mysqli_get_host_info () n/A returns a string that can represent the type of connection 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 returns information about the MySQL server version

$mysqli->server_version mysqli_get_server_version () n/A returns the MySQL server-side version of the integer information

$mysqli->info Mysqli_info () n/A retrieve information for the last query executed

$mysqli->insert_id mysqli_insert_id () n/A returns the ID that was automatically generated and used by the last query

$mysqli->sqlstate mysqli_sqlstate () n/A returns the SQLSTATE error of the previous MySQL operation

$mysqli->warning_count Mysqli_warning_count () n/A returns the number of warnings for the last query for a given link


Mysqli->autocommit () Mysqli_autocommit () n/a turn on or off the automatic submission of the database

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 default character set for a database connection

Mysqli->close () Mysqli_close () n/A closes a previously opened database connection

Mysqli->commit () Mysqli_commit () n/A commit the current transaction

Mysqli::__construct () Mysqli_connect () n/A to 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 dumps debug information to the log

Mysqli->get_charset () Mysqli_get_charset ()/n returns the character set of the object

Mysqli->get_connection_stats () mysqli_get_connection_stats () n/A returns statistics on the client connection. Available only for MYSQLND.

Mysqli->get_client_info () Mysqli_get_client_info ()/n returns the MySQL client version of the string description

Mysqli->get_client_stats () mysqli_get_client_stats () n/A returns statistics for each client process. Available only for MYSQLND.

Mysqli->get_cache_stats () mysqli_get_cache_stats () n/A returns the client's Zval cache statistics. Available only for 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 the mysqli and returns a resource type used by Mysqli_real_connect. [Is not on the object, it is the $mysqli object it returns]

Mysqli->kill () Mysqli_kill () n/A requests the server to kill a MySQL thread

Mysqli->more_results () mysqli_more_results () n/A check whether a multiple-statement query has other query result sets

Mysqli->multi_query () Mysqli_multi_query () n/A executes a multiple-statement query on the database

Mysqli->next_result () Mysqli_next_result () n/A prepare the next result set from the Multi_query

Mysqli->options () mysqli_options () mysqli_set_opt () Setting options

Mysqli->ping () mysqli_ping () n/a ping a server connection, or if the connection is broken try to reconnect

Mysqli->prepare () Mysqli_prepare () n/a prepare a SQL statement for execution

Mysqli->query () Mysqli_query () n/a query executed on the database

Mysqli->real_connect () Mysqli_real_connect () n/A to open a connection to the MySQL server

Mysqli->real_escape_string (), mysqli->escape_string () mysqli_real_escape_string () mysqli_escape_string () The special character used in the escape string for the SQL statement, which considers the current character set of the connection.

Mysqli->real_query () Mysqli_real_query () n/a execute an SQL query

Mysqli->rollback () Mysqli_rollback () n/a rollback of current transaction

mysqli->select_db () mysqli_select_db () n/A Select the default database for database queries

Mysqli->set_charset () Mysqli_set_charset () n/A set the default client character set

Mysqli->set_local_infile_default () Mysqli_set_local_infile_default ()/n clears the handler defined by the user for the load data local infile command

Mysqli->set_local_infile_handler () Mysqli_set_local_infile_handler () n/a callback function to set the load DATA local infile command execution

Mysqli->ssl_set () Mysqli_ssl_set () n/a use SSL to establish an installation connection

Mysqli->stat () Mysqli_stat () n/A Get current system state

Mysqli->stmt_init () Mysqli_stmt_init () n/A Initializes a statement and returns the object used by a Mysqli_stmt_prepare

Mysqli->store_result () Mysqli_store_result ()/n transmits the result set of the last query

MYSQLI->THREAD_ID () mysqli_thread_id () n/a return the thread ID of the current connection

Mysqli->thread_safe () Mysqli_thread_safe () n/A returns whether thread safety is set

Mysqli->use_result () Mysqli_use_result () n/A to initialize the retrieval of a result set




Object-oriented Interface process interface alias (do not use) description


$mysqli _stmt->affected_rows mysqli_stmt_affected_rows () n/A returns the last multiple of the execution of the statement change, the total number of rows deleted or inserted

$mysqli _stmt->errno Mysqli_stmt_errno () n/A returns the error code of the last statement call

$mysqli _stmt->error Mysqli_stmt_error () n/A A string description that returns the last statement error

$mysqli _stmt->field_count Mysqli_stmt_field_count () n/A returns the number of fields obtained from the given statement

$mysqli _stmt->insert_id mysqli_stmt_insert_id ()/n Gets the ID generated by the previous insert operation

$mysqli _stmt->num_rows mysqli_stmt_num_rows () n/A returns the number of rows in the result set of the statement

$mysqli _stmt->param_count Mysqli_stmt_param_count () Mysqli_param_count () returns the number of parameters in the given statement

$mysqli _stmt->sqlstate mysqli_stmt_sqlstate () n/A returns the SQLSTATE error code of the previous statement operation


Mysqli_stmt->attr_get () Mysqli_stmt_attr_get () n/A is used to get the current value of the statement property

Mysqli_stmt->attr_set () Mysqli_stmt_attr_set () n/A is used to modify the behavior of the prepared statement

Mysqli_stmt->bind_param () Mysqli_stmt_bind_param () Mysqli_bind_param () binds a variable as an argument to the prepared statement

Mysqli_stmt->bind_result () Mysqli_stmt_bind_result () Mysqli_bind_result () binds a variable to a prepared statement for the result store

Mysqli_stmt->close () Mysqli_stmt_close () n/A closes a prepared statement

Mysqli_stmt->data_seek () Mysqli_stmt_data_seek () n/A view any row in the statement result set

Mysqli_stmt->execute () Mysqli_stmt_execute () Mysqli_execute () executes a prepared query

Mysqli_stmt->fetch () Mysqli_stmt_fetch () Mysqli_fetch () grabs the result from a prepared statement into a qualified variable

Mysqli_stmt->free_result () Mysqli_stmt_free_result () n/A releasing a given statement to handle the memory of a stored result set

$mysqli _stmt->get_result () mysqli_stmt_get_result N/A No documentation is available for MYSQLND only.

Mysqli_stmt->get_warnings () mysqli_stmt_get_warnings () n/a No documentation

$mysqli _stmt->more_results () mysqli_stmt_more_results () n/a No documentation is available for MYSQLND only.

$mysqli _stmt->next_result () Mysqli_stmt_next_result () n/a No documentation is available for MYSQLND only.

Mysqli_stmt->num_rows () mysqli_stmt_num_rows () n/a Lookup attribute $mysqli_stmt->num_rows

Mysqli_stmt->prepare () Mysqli_stmt_prepare () n/A prepares an SQL statement for executing

Mysqli_stmt->reset () Mysqli_stmt_reset () n/A to reset a prepared statement

Mysqli_stmt->result_metadata () Mysqli_stmt_result_metadata () Mysqli_get_metadata () returns the result set metadata from a prepared statement

Mysqli_stmt->send_long_data () Mysqli_stmt_send_long_data () Mysqli_send_long_data () send a block of data

Mysqli_stmt->store_result () Mysqli_stmt_store_result () n/a The transmission of a result set from a prepared statement




Object-oriented Interface process interface alias (do not use) description


$mysqli _result->current_field Mysqli_field_tell () n/A Get the position of the current field at the beginning of the result set pointer

$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 returns the length of each column of the current row in the result set, returning the array

$mysqli _result->num_rows mysqli_num_rows () n/A to get the number of rows in the result


Mysqli_result->data_seek () Mysqli_data_seek () n/A to adjust the result pointer in the result to any row

Mysqli_result->fetch_all () Mysqli_fetch_all () n/A crawls all the result rows and returns the result set in the form of associative data, an array of numeric indices, or both. Available only for MYSQLND.

Mysqli_result->fetch_array () Mysqli_fetch_array () n/A an associative array, a numeric index array, or both, to grab a row of results

MYSQLI_RESULT->FETCH_ASSOC () Mysqli_fetch_assoc () n/A to crawl a row of results in an associative array

Mysqli_result->fetch_field_direct () Mysqli_fetch_field_direct () n/a captures the metadata of a single field

Mysqli_result->fetch_field () Mysqli_fetch_field () n/A returns the next field in the result set

Mysqli_result->fetch_fields () Mysqli_fetch_fields () n/A returns an array of objects representing the result set field

Mysqli_result->fetch_object () Mysqli_fetch_object () n/A returns the line of a result set in the form of an object

Mysqli_result->fetch_row () Mysqli_fetch_row () n/A returns a row of results in an enumerated array

Mysqli_result->field_seek () Mysqli_field_seek () n/A sets the result pointer to the specific field start position

Mysqli_result->free (), Mysqli_result->close, Mysqli_result->free_result Mysqli_free_result () n/a release with a result set phase Off the memory


* Note the difference between Mysqli_store_result and Mysqli_use_result

In fact, the difference between the two parameters is still very large.

(1) The difference is in retrieving the rows of the result set from the server.

(2) Mysqli_use_result started the query, but actually did not get any rows

(3) Mysqli_store_result retrieves all rows immediately

(4) Mysqli_store_result Retrieves a result set from the server, extracts the row, allocates the memory to it, stores it in the client, and then calls

Mysqli_fetch_array () will never return an error because it simply leaves the row out of the data structure that has the result set preserved, Mysqli_fetch_array () returns null always indicates that the end of the result set has been reached.

(5) The Mysqli_use_result itself does not retrieve any rows, but simply initiates a line-by-row retrieval, which means that each row must be called

Mysqli_fetch_array () to finish it by himself. This being the case, although Mysqli_fetch_array () returns NULL, it still indicates that the end of the result set is reached at this point, but it may also indicate an error in communicating with the server.

If the Mysqli_result object is mysqli_use_result,query later, the execution data_seek will be wrong because

Mysqli_result::d ata_seek () []: Function cannot is used with Mysql_use_result

Compared to Mysqli_use_result, Mysqli_store_result has a higher memory and processing requirements because it maintains the entire result set on the client, so the cost of allocating memory and creating data structures is huge, and if you want to retrieve multiple rows at once, you can Mysqli_use_result.

Mysqli_use_result has a lower memory requirement because it only needs to allocate enough space for each processing line. This is faster because there is no need to create a complex data structure for the result set.

Mysqli_use_result, on the other hand, adds a larger load to the server, and it must keep the rows in the result set until the client looks appropriate to retrieve all the rows.



$dbHelper =new Mysqlihelp;

$dbHelper->connect (' localhost ', ' root ', ', ', ' TT ');

$dbHelper->db->select_db ("tt");

$dbHelper->db->set_charset ("UTF8");

If this is Mysqli_use_result, the following $dbhelper->data_seek ($query, 10); There will be an error.

$query = $dbHelper->query ("Select Id,cateid,title from Product Limit", 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, ' Here is demo ', ' Test ', Unix_timestamp ()) ");

echo $query->num_rows. " Total ";

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:%s\n", $val->name);

printf ("Table:%s\n", $val->table);

printf ("Max. Len:%d\n ", $val->max_length);

printf ("Flags:%d\n", $val->flags);

printf ("Type:%d\n\n", $val->type);

// }

If the query operation affected_rows the number of rows 1,num_rows to the query result row number, Num_fields is the number of fields

If the update or delete operation Affected_rows as the number of rows affected, Num_rows is Null,num_fields null


$dbHelper->data_seek ($query, 10);

$row = $dbHelper->fetch_row ($query);

Var_dump ($row);

echo "affects number of rows:", "<br/>";

Var_dump ($dbHelper->affected_rows ());

Var_dump ($dbHelper->db->affected_rows);

echo "Query line number", "<br/>";

Var_dump ($dbHelper->num_rows ($query));

echo "Number of columns:", "<br/>";

Var_dump ($dbHelper->num_fields ($query));

1th Kind

$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 = "% percent";

$cateid = 10;

$stmt->execute ();

$stmt->bind_result ($col 1, $col 2, $col 3);


while ($stmt->fetch ()) {

printf ('%s%s '. ' <br/> ", $col 1, $col 3);


echo "<br/>";

$title = "% percent";

$cateid = 4;

$stmt->execute ();

$stmt->bind_result ($col 1, $col 2, $col 3);


while ($stmt->fetch ()) {

printf ('%s%s '%s '. ' <br/> ", $col 1, $col 2, $col 3);


$stmt->close ();

2nd Kind

$stmt = $dbHelper->db->stmt_init ();

$stmt->prepare ($sql);

$stmt->bind_param (' is ', $cateid, $title);

$title = "% percent";

$cateid = 10;

$stmt->execute ();

$stmt->bind_result ($col 1, $col 2, $col 3);


while ($stmt->fetch ()) {

printf ('%s%s '. ' <br/> ", $col 1, $col 3);


echo "<br/> start multi_query:<br/>";

Multi_query instance

The return value of the Multi_query () method, and the//mysqli property errno, error, info, and so on, are only relevant to the first SQL command, and it is not possible to determine whether the second and subsequent commands were incorrectly executed. Therefore, when the return value of the//multi_query () method is true, it does not mean that the subsequent command does not make an error while executing.

$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-A-set */

What is the difference between the following two methods?

if ($result = $dbHelper->db->store_result ()) {

while ($row = $result->fetch_row ()) {

$data []= $row;

// }

$result->free ();

// }

if ($result = $dbHelper->db->use_result ()) {//return Mysqli_result type

$result->data_seek (0);//return 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);


Executing transaction instances

Table with InnoDB type to ensure table of action

If it is MyISAM, logic fails, all actions are performed, not rolled back

$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 "Success";



$dbHelper->db->rollback ();

echo "Failed";


$dbHelper->db->autocommit (1);

Var_dump ($dbHelper->insert_id ());

Var_dump ($mysql _debug_query); Print SQL query Information



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.