Using mysqli to manipulate databases in PHP

PHP's mysqli extension provides all the features of its antecedent version, in addition, since MySQL is already a

A database server with full features, which adds some new features to PHP. And Mysqli is supporting the

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

Select a database, and finally close the connection. Like almost all features of mysqli, this can be used for

object, or it can be done in a process-based way.

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, a

Be careful to monitor connection errors and react accordingly. The mysqli extension contains a number of features that can be used to capture an error message

For example: 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.

if ($_mysqli->errno) {

An error occurred during the Echo ' database operation, the error code is: '. $_mysqli->error;

}

Three Interacting with a database

Most queries are associated with the creation (Creation), get (retrieval), update, and delete (deletion)

These tasks are 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, you send to the database

Select query, and then iterate over the results, output the rows to the browser and output as per your requirements.

Set the encoding UTF8

$_mysqli->set_charset ("UTF8");

Create a sentence of 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 multiple

method to get the fields in each row, whichever method you choose depends primarily on your preferences, because only the reference field

Methods differ.

Putting the result set in an object

Since you might be using Mysqli's object-oriented syntax, you can manage the knot in an object-oriented manner entirely.

The fruit set. 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 by an INSERT, UPDATE, or delet query

The number of rows affected. 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 rows affected by Select, INSERT, UPDATE, DELETE queries

The number of affected_rows can be used; 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 data, or you don't want to get from the first field, you can use the data to refer to the

The needle movement or the way the field pointer moves is adjusted to the appropriate position. Of course, you can also get the name of the field and its phase

Properties of the switch.

Calculate how many fields

Echo $_reslut->field_count;

Gets the name of the field

$_field = $_reslut->fetch_field ();

Echo $_field->name;

Traversing fields

while (!! $_field =$_reslut->fetch_field ()) {

Echo$_field->name. ' <br/> ';

}

Get field Array once

Print_r ($_reslut->fetch_fields ());

Move data pointer

$_reslut->data_seek (5);

Move field pointer

$_reslut->field_seek (2);

5. Execute multiple SQL statements

Sometimes, we need to execute multiple SQL statements on a single page, and the previous method is to create multiple

A result set and then use it. But this resource consumption is very big, also is not conducive to management. PHP provides a way to execute multiple SQL statements

Method of $_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 for the first SQL statement

$_result= $_mysqli->store_result ();

Print_r ($_result->fetch_array ());

Move the result set pointer to the next

$_mysqli->next_result ();

$_result= $_mysqli->store_result ();

Print_r ($_result->fetch_array ());

$_mysqli->next_result ();

$_result= $_mysqli->store_result ();

Print_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 the operations in a group

Succeeds, the transaction is considered successful, and the transaction is unsuccessful even if there is only one failed operation. If all operations are completed successfully

Commit, and its modification will be used for all other database processes. If an operation fails, the thing

will be rolled back and the firm's operational impact will be canceled.

First of all, your MySQL is a kind of innodb or BDB engine, in general, you have installed Appserv integration

Package, you select the database of the InnoDB engine. If you build a table that is not InnoDB, you can phpMyAdmin

Changes in the.

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;

To determine if all of them have passed properly, two SQL

if ($_success && $_success2) {

$_mysqli->commit ();

Echo ' Perfect submission! ‘ ;

}else {

$_mysqli->rollback ();

Echo ' program is out of the ordinary! ‘ ;

}

}

} else {

echo "SQL statement error:". $_mysqli->errno. $_mysqli->error;

}

Finally, you must also turn on auto-commit

$_mysqli->autocommit (TRUE);

Using mysqli to manipulate databases in PHP