manipulating databases with mysqli

First, MySQL and the concept of MYSQLI Related:

1, MySQL and mysqli are the function set of PHP, and the MySQL database is not very relevant.

2, before the PHP5 version, is generally used in PHP MySQL function to drive MySQL database, such as mysql_query () function, belongs to process 3, after the PHP5 version, added mysqli function function, in a sense, It is an enhanced version of MySQL system functions, more stable and more efficient and more secure, with mysql_query () corresponding to the Mysqli_query (), belongs to the object-oriented, the operation of the object to drive the MySQL database

Second, the difference between MySQL and mysqli:

1, MySQL is a non-holding connection function, MySQL each link will open a connected process.

2, Mysqli is the permanent connection function, mysqli multiple runs mysqli will use the same connection process, thereby reducing the server overhead. Mysqli encapsulates some of the advanced operations, such as transactions, while encapsulating many of the methods available in the DB operation process.

Iii. usage of MySQL and mysqli:

1:mysql (Process mode):

$conn = mysql_connect (' localhost ', ' user ', ' password '); Connect to MySQL Database

mysql_select_db (' data_base '); Select Database

$result = mysql_query (' select * from Data_base ');//The Second optional parameter that specifies the open connection

$row = Mysql_fetch_row ($result))//Fetch only one row of data

echo $row [0]; Output the value of the first field

PS:MYSQLI operates in a procedural way, some functions must specify a resource, such as mysqli_query (resource ID, SQL statement), and the resource identity parameter is placed in front, and the resource identifier of the mysql_query (SQL statement, ' resource ID ') is optional, The default value is the previous open connection or resource.

2, Mysqli (object mode):

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 = new mysqli ();

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

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

3. Create a Mysqli object with connection parameters

$_mysqli = new mysqli (' 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 in 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 Tg_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

Number, you can use affected_rows; Note that it is a property under $_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);

Use new mysqli (' localhost ', usenamer ', ' Password ', ' databasename ');

Fatal error:class ' mysqli ' not found in ...

Generally mysqli is not open, because Mysqli class is not the default open, win under to change php.ini, remove php_mysqli.dll ago, Linux to put Mysqli in.

Iv. mysql_connect () and Mysqli_connect ()

1. Using mysqli, you can pass the database name as a parameter to the Mysqli_connect () function, or to the Mysqli constructor;

2. If you call Mysqli_query () or Mysqli's object query () method, the connection identity is required.

manipulating databases with mysqli