PHP Operation mysqli (sample code) _php instance

<?php


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)


{


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


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;


}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 "<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 ()


{








$this->db=null;





}








/* Mysqli class


Object-oriented interface for process interface alias description


Property


$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


Method


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


*/





/*


Mysql_stmt


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


Property


$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


Method


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





*/





/*


Mysqli_result


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


Property


$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


Method


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 () [Mysqli-result.data-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);








/* FETCH VALUES * *


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








/* FETCH VALUES * *


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








/* FETCH VALUES * *


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


Exit


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





}else


{


$dbHelper->db->rollback ();


echo "Failed";





}


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








Var_dump ($dbHelper->insert_id ());


Var_dump ($mysql _debug_query); Print SQL query Information








//


?>