A Simple Method for PHP to use mysqli to operate MySQL databases, mysqlimysql

A Simple Method for PHP to use mysqli to operate MySQL databases, mysqlimysql

The mysqli extension of PHP provides all the functions of its first-line version. In addition, because MySQL is already a database server with complete features, it adds some new features to PHP. Mysqli also supports these new features.

1. Establish and disconnect a connection

When interacting with a MySQL database, you must first establish a connection and then disconnect the connection. This includes connecting to the server, selecting a database, and closing the connection. Similar to almost all the features of mysqli, this can be done using object-oriented methods or procedural methods.

1. Create a mysqli object

$ _ Mysqli = newmysqli ();

2. Connect the MySQL host, user, password, and database

$ _ Mysqli-> connect ('localhost', 'root', 'angfan ', 'Guest ');

3. Create a mysqli object with connection Parameters

$ _ Mysqli = newmysqli ('localhost', 'root', 'angfan ', 'Guest ');

4. Select a database separately

$ _ Mysqli-> select_db ('testguest ');

5. Disconnect MySQL

$ _ Mysqli-> close ();

2. Handle connection errors

If you cannot connect to the MySQL database, this page is unlikely to continue with the expected work. Therefore, be sure to monitor connection errors and respond accordingly. The Mysqli extension contains many features that can be used to capture error information, such as the mysqli_connect_errno () and mysqli_connect_error () methods.

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

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

If (mysqli_connect_errno () {echo 'database connection error, error message: '. mysqli_connect_error (); exit ();}

The error code returned by the errno attribute during database operations.

The error code returned when the database operation is performed.

If ($ _ mysqli-> errno) {echo 'database operation error. error code: '. $ _ mysqli-> error ;}

3. Interaction with databases

Most queries are related to Creation, Retrieval, Update, and Deletion tasks, collectively referred to as CRUD.

1. Get Data

Most of the work of Web applications is to obtain and format the requested data. To this end, you need to send a SELECT query to the database, then perform iterative processing on the results, output the rows to the browser, and output the results according to your own requirements.

// Set the encoding utf8 $ _ mysqli-> set_charset ("utf8"); // create an SQL statement $ _ SQL = "SELECT * FROM t g_user "; // execute the SQL statement and assign the result set to $ _ result =$ _ mysqli-> query ($ _ SQL ); // output print_r ($ _ result-> fetch_row () in the first row of the result set; // release the query memory (destroy) $ _ result-> free ();

2. parse query results

Once the query is executed and the result set is ready, the obtained results can be parsed below. You can use multiple methods to obtain fields in each row. The method you choose depends on your preferences, because the method for referencing fields is different.

Put the result set into the object

Because you may use the object-oriented Syntax of mysqli, you can use the object-oriented method to manage the result set. You can use the fetch_object () method.

// Wrap the result set into an object

$ _ Row =$ _ reslut-> fetch_object ();

// A field (attribute) in the output object)

Echo $ _ row-> tg_username;

// Traverse all user names

While (!! $ _ Row =$ _ reslut-> fetch_object ()){

Echo $ _ row-> tg_username. '<br/> ';

}

Use Index Array and associated array

// Wrap the result set into an array (index + Association)

$ _ Row =$ _ reslut-> fetch_array ();

// The output subscript is a 3 field (attribute)

Echo $ _ row [3];

// Wrap the result set into an Index Array

$ _ Row =$ _ reslut-> fetch_row ();

Echo $ _ row [3];

// Wrap the result set into an associated array

$ _ Row =$ _ reslut-> fetch_assoc ();

Echo $ _ row ['tg _ username'];

3. Determine the selected row and the affected row

You usually want to determine the number of rows returned by the SELECT query, or the number of rows affected by the INSERT, UPDATE, or DELET query. We can use the num_rows and affected_rows attributes.

// When using a query, you can use num_rows to know the number of rows queried by the SELECT statement.

Echo $ _ reslut-> num_rows;

// When using a query, you can use affected_rows to understand the number of rows affected by the SELECT, INSERT, UPDATE, and DELETE queries. Note that it is an attribute under $ _ mysqli.

Echo $ _ mysqli-> affected_rows;

4. Move pointer operations and obtain Fields

If you do not want to get the first data or the first field, you can use the Data Pointer movement or field pointer movement method to adjust it to the appropriate position. Of course, you can also obtain the field name and its related attributes.

// Calculate the number of fields echo $ _ reslut-> field_count; // obtain the field name $ _ field =$ _ reslut-> fetch_field (); echo $ _ field-> name; // traverses the field while (!! $ _ Field =$ _ reslut-> fetch_field () {echo $ _ field-> name. '<br/>';} // obtain the field array print_r ($ _ reslut-> fetch_fields () at a time ()); // 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 simultaneously on a page. The previous method is to create multiple result sets and then use them. However, the resource consumption is very high, which is not conducive to management. PHP provides a method 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 to execute multiple SQL statements if ($ _ mysqli-> multi_query ($ _ SQL )) {// start to obtain the result set of 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 (); $ _ resul T = $ _ mysqli-> store_result (); print_r ($ _ result-> fetch_array ();} The else {echo 'SQL statement is incorrect! ';}

6. Execute database transactions

A transaction is a group of sequential database operations as a whole unit. If all operations in a group are successful, the transaction is considered successful. Even if there is only one failed operation, the transaction is not successful. If all operations are completed successfully, the transaction is committed, and the modification takes effect on all other database processes. If an operation fails, the transaction will be rolled back and all operations affected by the transaction will be canceled.

First, your MySQL is a type of InnoDB or BDB engine. Generally, if you have installed the AppServ integration package, you can select the database of the InnoDB engine. If the table you created is not InnoDB, you can modify it in phpmyadmin.

// First, you must disable automatic data submission $ _ mysqli-> autocommit (false); // create an SQL statement that must run successfully at the same time, one failed $ _ 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 )) {// obtain the number of rows affected by the first SQL statement $ _ success =$ _ mysqli-> affected_rows = 1? True: false; // move down, the second SQL $ _ mysqli-> next_result (); // obtain the number of rows affected by the second SQL statement $ _ success2 =$ _ mysqli-> affected_rows = 1? True: false; // check whether both SQLif ($ _ success & $ _ success2) {$ _ mysqli-> commit (); echo 'is submitted perfectly! ';} Else {$ _ mysqli-> rollback (); echo' is abnormal! ';}} Else {echo "the SQL statement is incorrect :". $ _ mysqli-> errno. $ _ mysqli-> error;} // You must enable automatic submission at the end. $ _ mysqli-> autocommit (true );

The above Simple Method of Using mysqli to operate MySQL Databases in PHP is to share all the content with you. I hope you can give me a reference and support me a lot.