PHP implementation method of using MYSQLI to operate MySQL database

This article mainly introduces the PHP implementation using the MYSQLI operation MySQL database method, the interested friend's reference, hoped to have the help to everybody.

PHP's mysqli extension provides all the features of its antecedent version, and since MySQL is already a full-featured database server, this adds some new features to PHP. Mysqli also supports these new features.

A Establish and Disconnect connections

When interacting with the MySQL database, you first establish a connection and finally disconnect, which includes connecting to the server and selecting a database, and finally closing the connection. Like almost all features of mysqli, this can be done using an object-oriented approach, or it can be done in a process-based manner.

1. Create an Mysqli object

$_mysqli = Newmysqli ();

2. Connect to MySQL host, user, password, database

$_mysqli->connect (' localhost ', ' root ', ' Yangfan ', ' guest ');

3. Create a Mysqli object with connection parameters

$_mysqli = newmysqli (' localhost ', ' root ', ' Yangfan ', ' guest ');

4. Select the database separately

$_mysqli->select_db (' testguest ');

5. Disconnect MySQL

$_mysqli->close ();

Two Handling Connection Errors

If the MySQL database cannot be connected, it is unlikely that this page will continue to work as expected. Therefore, be sure to monitor the connection errors and react accordingly. The mysqli extension contains a number of features that can be used to catch error messages, such as the Mysqli_connect_errno () and Mysqli_connect_error () methods.

The Mysqli_connect_errno () function returns the error number returned by the connection database.

The Mysqli_connect_error () function returns the error code returned by the connection database.

if (Mysqli_connect_errno ()) {echo ' Database connection error, error message: '. Mysqli_connect_error (); exit ();}

The Errno property returns the error number of the database operation.

The error property returns the fault code for the database operation.

An error occurred in the IF ($_mysqli->errno) {echo ' database operation, the error code is: '. $_mysqli->error;}

Three Interacting with a database

The vast majority of queries are related to create (Creation), get (retrieval), update, and delete (deletion) tasks, collectively referred to as CRUD.

1. Get Data

Most of the work of Web-page programs is to obtain and format the requested data. To do this, send a select query to the database, and then iterate over the results, outputting the rows to the browser and outputting them according to their requirements.

Set the encoding Utf8$_mysqli->set_charset ("UTF8");//Create an SQL statement $_sql = "select* from t G_user";//Execute SQL statement to assign the result set to $_result $_result = $_mysqli->query ($_sql);//outputs the first row of the result set Print_r ($_result->fetch_row ());//Free query memory (destroy) $_result->free ();

2. Parse Query Results

Once the query is executed and the result set is ready, the resulting rows can be parsed. You can use more than one method to get the fields in each row, depending on which one you prefer, because only the method that refers to the field is different.

Putting the result set in an object

Because you might use Mysqli's object-oriented syntax, you can manage the result set in an object-oriented manner. You can do this by using the Fetch_object () method.

Wrapping a result set as an object

$_row = $_reslut->fetch_object ();

A field (property) in the Output object

Echo $_row->tg_username;

Traverse All user Names

while (!! $_row =$_reslut->fetch_object ()) {

Echo$_row->tg_username. ' <br/> ';

}

Using indexed arrays and associative arrays

Wrapping a result set as an array (Index + association)

$_row = $_reslut->fetch_array ();

Output subscript is a field (attribute) of 3

Echo $_row [3];

Wrapping the result set as an indexed array

$_row = $_reslut->fetch_row ();

Echo $_row [3];

Wrapping the result set as an associative array

$_row = $_reslut->fetch_assoc ();

echo $_row [' tg_username '];

3. Determine the selected row and the affected row

You typically want to be able to determine the number of rows returned by a select query, or the number of rows affected by an INSERT, UPDATE, or delet query. We can use Num_rows and affected_rows two properties

When you use a query, you want to know how many rows the select queries, and you can use Num_rows.

Echo $_reslut->num_rows;

When you use a query, you want to understand the number of rows affected by Select, INSERT, UPDATE, DELETE query, you can use affected_rows; Note that it is a property under the $_mysqli

Echo $_mysqli->affected_rows;

4. Move pointer operations and get fields

When you don't want to start with the first piece of data, or you don't want to get from the first field, you can use the data pointer to move or move the field pointer to the right place. Of course, you can also get the name of the field and its associated properties.

Calculates how many fields echo $_reslut->field_count;//gets the name of the field $_field = $_reslut->fetch_field (); Echo $_field->name;// Traverse the field while (!! $_field =$_reslut->fetch_field ()) {Echo$_field->name. ' <br/> ';} Get the field array Print_r ($_reslut->fetch_fields ()) at once,//move the data pointer $_reslut->data_seek (5);//Move the field pointer $_reslut->field_ Seek (2);

5. Execute multiple SQL statements

Sometimes we need to execute multiple SQL statements on one page at a time, and the previous method is to create multiple result sets and then use them. But this resource consumption is very big, also is not conducive to management. PHP provides a way to execute multiple SQL statements $_mysqli->multi_query ();

Create multiple SQL statements $_sql. = "SELECT * from Tg_user;"; $_sql. = "SELECT * from Tg_photo;"; $_sql. = "SELECT * from Tg_article";// Start executing multiple SQL statements if ($_mysqli->multi_query ($_sql)) {//start getting the result set of the first SQL statement $_result= $_mysqli->store_result ();p Rint_r ( $_result->fetch_array ());//Move the result set pointer to the next $_mysqli->next_result (); $_result= $_mysqli->store_result ();p rint _r ($_result->fetch_array ()); $_mysqli->next_result (); $_result= $_mysqli->store_result ();p Rint_r ($_ Result->fetch_array ());} else {echo ' SQL statement is wrong! ' ;}

6. Performing Database transactions

A transaction (transaction) is an ordered set of database operations that acts as an entire unit. If all operations in a group are successful, the transaction is considered successful and the transaction is unsuccessful even if there is only one failed operation. If all operations are completed successfully, the transaction commits (commit) and its modifications are made to all other database processes. If an operation fails, the transaction is rolled back, and the effect of the firm's operations is canceled.

First of all, your MySQL is a innodb or BDB engine, in general, you install the Appserv integration package, you select the InnoDB engine database. If you create a table that is not InnoDB, you can modify it in phpMyAdmin.

First you must turn off autocommit data $_mysqli->autocommit (FALSE);//Create an SQL statement that must run successfully at the same time, cannot appear a success, a failure $_sql. = "UPDATE tg_friend SET tg_ state=tg_state+5 where tg_id=1; "; $_sql. =" UPDATE tg_flower SET tg_flower=tg_flower-5 WHERE tg_id=1; "//Execute two SQL statements if (  $_mysqli->multi_query ($_sql)) {//Gets the number of rows affected by the first SQL $_success= $_mysqli->affected_rows = = 1? true:false;//Move Down, second Sql$_mysqli->next_result ();//Gets the number of rows affected by the second SQL $_success2 = $_mysqli->affected_rows = = 1? True:false;//Judging whether all have passed normally, two sqlif ($_success && $_success2) {$_mysqli->commit (); Echo ' Perfect submission! ' ;} else {$_mysqli->rollback (); Echo ' program has an exception! ' ;}}} else {echo "SQL statement error:". $_mysqli->errno. $_mysqli->error;} Finally, you must turn on autocommit $_mysqli->autocommit (true);

The above is the whole content of this article, I hope that everyone's study has helped.