How to operate the MySQL database mysqli

This article mainly introduces the PHP operation MySQL database Mysqli Way, has a certain reference value, now share to everyone, the need for friends can refer to

PHP three ways to operate MySQL

Mysql

Non-permanent connections, each time a database is used, a new process is opened,

Disadvantages:
Low performance, PHP5.0 later discarded.

Mysqli

Permanent connection, eases server pressure, supports MySQL only

Disadvantages:
Memory consumption

PDO (recommended)

It can implement common functions of mysqli and support most databases.

Mysqli mode (object-oriented)

Basic use:

1) Connect to the database

$mysqli = new mysqli (' Host ', ' username ', ' password ', ' database ')

2) Execute SQL statement

$mysqli, query ($sql);

3) Get Results

$result-Fetch_array ([MYSQLI_ASSOC]) a     $result-Fetch_assoc () $result fetch_all                   ([mysqli_ ASSOC])       all

The default method is 索引数组 to add a parameter and then change it 关联数组 .

Method Two, which is equivalent to method one plus argument.

4) Close the connection

$mysqli-Close ();

Instance:

<?php//mysqli Additions/deletions///mysqli mode connection Database $mysqli = new mysqli (' localhost ', ' root ', ' 123456789 ', ' test ');//Execute SQL statement// Use UTF-8 encoding $mysqli, query (' Set names UTF8 ');//insert/$result = $mysqli, query ("Insert Users (Name,money) VALUES (' cone ' , 4);//$result = $mysqli, "INSERT users (Name,money) VALUES (' Spicy chicken leg fort ', 16)");//modify//$result = $mysqli, Quer Y ("UPDATE users SET ' name ' = ' wheat cyclone ' WHERE ' id ' = 2");//delete//$result = $mysqli, query ("Delete from users where ' id ' = 5" );//var_dump ($result);/********************* query *****************/$result = $mysqli, Query ("SELECT * from users") ;//Get result set $data = $result-Fetch_all (MYSQLI_ASSOC); Var_dump ($data);

Transaction control

A transaction is a database operation (a total success or failure) that combines multiple logical tasks into one execution unit.

1) Open transaction

Autocommit, $mysqli (false)

This method will cause the transaction to be committed immediately, so you need to fill in the parameter false.

2) Transaction rollback

Rollback, $mysqli ()

If something goes wrong with the data, you can use this method to restore the data.

3) Transaction Submission

Commit ($MYSQLI)

4) Close the connection

Close $mysqli ()

Attention:

When we use Query () to execute the SQL statement, he does not control the database changes, just the SQL statement is wrong.

So we can't rely on the return value of query () to determine whether the operation is successful or not.

To use the number of affected rows, determine:

Affected_rows, $mysqli

Instance

<?php//mysqli Transaction Control Example Header ("Content-type:text/html;charset=utf-8");//mysqli mode connection Database $mysqli = new Mysqli (' LocalHost ', ' root ', ' 123456789 ', ' test ');//query with UTF-8 encoding $mysqli (' Set names UTF8 ');//Open Transaction $mysqli Autocommit (false);//SQL$SQL1 = "Update users set ' money ' = ' money ' +1  WHERE ' id ' = 1"; $sql 2 = "Update the users set ' money ' = ' Money '-1  WHERE ' id ' = 11 ';//Execute SQL statement $result = $mysqli, query ($sql 1); $r 1 = $mysqli, affected_rows; $result = $ mysqli, Query ($sql 2), $r 2 = $mysqli affected_rows;if ($r 1 > 0 && $r 2 > 0) {    //COMMIT TRANSACTION    $mysqli Commit ();    Echo ' Operation succeeded ';} else{    //transaction rollback    $MYSQLI-rollback ();    Echo ' operation failed ';}

Pretreatment

Pre-processing, the SQL statement is submitted to the server to perform precompilation.

When the client executes the SQL statement, only the input parameters can be uploaded;

If multiple reads or storage are involved, the efficiency is higher than the normal SQL execution operation.

1) $sql = "???"

The parameter inside the value of the SQL statement, using a question mark instead.

2) Creating precompiled objects

$stmt = $mysqli-Prepare ($sql)

3) Parameter Assignment

$stmt-Bind_param (' parameter type ', Parameter 1, parameter 2 ...)

parameter type description:
String-s int-i double-d Bool-b

Special Reminders:
The parameter types are written in order, without spaces, commas, such as parameter 1 as String, and parameter 2 as bool, written like this:
$stmt -> bind_param('si', 参数1， 参数2)

4) Variable binding result set (the query requires this step, other actions skip this step)

Bind_result, $stmt ()

Special attention:
The Bind_result variable corresponds to the field name in the SELECT statement, so the number and order must be noted!!!

5) Code Execution

Execute (), $stmt

6) query Log in PHP program (query operation Optional)

$stmt->store_result ()

Please see the link below for more details

Detailed analysis

7) Get the results of each record

$stmt->fetch ()

Instance

<?php//mysqli preprocessing (query) example header ("Content-type:text/html;charset=utf-8");//mysqli mode connection Database $mysqli = new Mysqli (' LocalHost ', ' root ', ' 123456789 ', ' test ');//query with UTF-8 encoding $mysqli (' Set names UTF8 ');//SQL statement $sql = "SELECT * FROM use RS WHERE id>? ";//Create precompiled object $stmt = $mysqli-Prepare ($sql);//Parameter binding $id = 1; Bind_param (' I ', $id);//Bind the result set   parameter to match the parameters of the table $st MT-Bind_result ($id, $name, $money, $age);//Execute SQL statement $stmt, execute ();//Execute SQL Statement $stmt->store_result ();// Receive result set while ($STMT-to-fetch ()) {    $data [] = [        ' id ' = + $id,        ' name ' = = $name,        ' money ' = $money 
  ];} Var_dump ($data);