Shulou(Shulou.com)06/03 Report--

This article will explain in detail what the commonly used Mysqli code in PHP is, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

At the beginning of PHP5.0, not only the early mysql database extension function can be used, but also the newly extended mysqli technology can be used to communicate with mysql database. The mysqli extension of PHP is encapsulated in a class. It is an object-oriented technology and can only be used in PHP5 and MYSQL4,1 or higher. (I) it means that it is faster, more convenient and more efficient to use mysqli. You can also make database access more secure (because you use class mode)

Simple process using mysqli

Set up the PHP.ini profile

Extension=php_mysqli.dll

/ / create a connection

$conn = new mysqli ($servername, $username, $password, $dbname)

/ / check the connection

If ($conn- > connect_error) {die ("Connection failed:". $conn- > connect_error);}

/ / get the return value of the database

If ($stmt=$con- > prepare ("SELECT username,password FROM member WHERE username=? AND password=?")) {$stmt- > bind_param ("ss", $username,$password); $stmt- > execute (); $stmt- > bind_result ($U penny P); $HasData=false; while ($stmt- > fetch ()) {$HasData=true; echo "username- >". $U. "password- >". $P. "

";}}

/ / close the connection

$conn- > close ()

/ / Common syntax

INSERT INTO [Table name] ([Field 1], [Field 2]) VALUES ([value 1], [value 2])

SELECT * FROM [table name] WHERE [expression] ORDER BY [field 1], [ASC/DESC rise / drop] [field 2], [ASC/DESC rise / drop]

DELETE FROM [table name] WHERE [expression]

UPDATE [table name] SET [field 1] = [value 1], [field 2] = [value 2] WHERE [expression]

Get the total number of rows: SELECT COUNT (*) FROM [Table]

/ / Chinese garbled code

/ / for the page header ("Content-Type:text/html;charset=utf-8"); / / for the database mysqli- > query ("SET CHARACTER SET 'utf8'"); / / for the mysqli- > query ("SET NAMES' utf8'"); / / for the write library

Here are some examples

Connect mysqli

Establish MYSQL profile config.ini.php

The config.ini.php content is as follows:

(link MYSQL database with mysqli)

Requery_once ("config.ini.php"); $mysqliObj = new mysqli ($dbhost,$dbuser,$dbpwd,$dbname); if (mysqli_connect_errno ()) {echo "connection failure" .mysqli _ connect_error (); exit ();} $mysqliObj- > query ("set name $charName")

(other operations)

Query

(single query) $sql = "drop table if exists user;"; $mysqliObj- > query ($sql); (multiple queries) $musqliObj- > multip_query ($sql) returns the number of rows affected by $sql () if ($mysqliObj- > query ($sql)) echo $mysqliObj- > affected_rows; insert insert, returns the inserted id (useful) $num = $mysqliObj- > insert_id

Three ways to process query results

$sql = "select * from user"; $result = $mysqli- > query ($sql); (1) fetch_row () returns an indexed array fetch_row () while (list ($id,$name,$pwd) = $result- > fetch_row ()) {echo "id:". $id. "name:". $name. "pwd:". $pwd. "

";} (2) fetch_assoc () returns the associative array fetch_assoc () while ($row = $result- > fetch_assoc ()) {echo" id: ". $row [" userId "]." Name: ". $row [" userName "]. Pwd: ". $row [" password "].

";} (3) fetch_object () returns the object while ($row = $result- > fetch_object ()) {echo" id: ". $row- > userId." Name: "$row- > uerName." Pwd: "$row- > password."

";}

Associative arrays, which are nested arrays such as:

Use the mysqli_stmt class

The mysql4.1 version begins to provide a prepared statement mechanism, which can see that the whole command is sent to the MYSQL server once, and then only the parameters change. MYSQL only needs to analyze the command once, which greatly reduces the amount of data to be transferred and improves the processing efficiency of the command (Note, close () should be closed immediately when you do not need to connect).

Steps:

1, prepare the SQL command

$sql = "insert into user (name,pwd) values (?)"

$stms = $mysqli- > prepare ($sql)

two。 Bind data

$stms- > bind_param ('ss',$name,$pwd); (note that' ss': corresponds to the following variable ($name,$pwd))

I all inerger types

D double or float type

S all other types including strings

B binary (blob, binary byte string)

)

$name = "huang"

$pwd = "123456"

3. Execution

$stms- > execute ()

[4. Execute another set of data in the binding

$name = "he"

$pwd = "666666"

$stms- > execute ()

]

5. Close

$stmt- > close ()

$mysqli- > close ()

(other useful parameters)

$num = $stmt- > number of rows affected by affected_rows;

$id = $stmt- > insert_id; returns the inserted row id when it is an insert command (automatic)

Transaction processing

By default, MYSQL runs in autocommit mode, which means that every statement executed is immediately written to the database, but if you use a transaction-safe table type, you do not want autocommit behavior

Transaction processing

When you execute multiple MYSQL commands, you certainly hope that when one of the commands goes wrong, all the commands will not be executed and you will return to the state before the command was executed.

It's time to use transactions.

Simple use of transaction processes

1. Write the SQL command

$sql1 = "insert user (name) values ('huang','123456')"

$sql2 = "update account set number = number+1"

two。 Turn off autocommit mode for MYSQL transactions

$mysqli- > cutocommit (0)

3. Trial execution of command

$success = true

$res1 = $mysqli- > query ($sql1)

If (! $res1 or $mysqli- > affected_rows! = 1) {

$success = false

}

$res2 = $mysqli- > query ($sql2)

If (! $res2 or $mysqli- > affected_rows! = 1) {

$success = false

}

4. Check the execution status, all were executed successfully, and there are failed rollback initials.

If ($success) {

$mysqli- > commit ()

Echo "execution successful"

} else {

$mysqli- > rollback ()

Echo "execution failed"

}

5. Restore the autocommit mode of MYSQL transactions

$mysqli- > cutocommit (1)

$mysqli- > close ()

Mysql currently supports transactions only with InnDB and BDB packet types.

InnoDB is the fastest.

(create InnDB type table)

Create table user (id int (10) not null auto_increment,name varchar (50) not null,pwd varchar (50) not null,primary key (id)) type=InnoDB on the "what are the common Mysqli code in PHP" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.