PHP Mysqli common code set and phpmysqli code set

PHP Mysqli common code set and phpmysqli code set

Beginning with PHP5.0, you can not only use the early mysql database extension functions, but also use the new extended mysqli technology to exchange information with the mysql database. The mysqli extension of PHP is encapsulated in a class, it is an object-oriented technology. It can only be used in PHP5, MYSQL4, 1, or later versions. (I) indicates that it should be used. Using mysqli, the execution speed is faster and more convenient, more efficient, and more secure database access (because the class mode is used)

Simple process of using mysqli

Set the PHP. ini configuration file
Extension = php_mysqli.dll

// Create a connection

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

// Detect connections

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

// Obtain the database Return Value

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,$P);   $HasData=false;   while($stmt->fetch())   {     $HasData=true;     echo "username->".$U." password->".$P."<br>";   } }

// 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 upgrade/downgrade] [Field 2], [ASC/DESC upgrade/downgrade];
Delete from [Table name] WHERE [expression];
UPDATE [Table name] SET [Field 1] = [value 1], [Field 2] = [value 2] WHERE [expression];
Obtain the total number of rows: select count (*) FROM [Table]

// Chinese garbled characters

// For the page header ("Content-Type: text/html; charset = UTF-8"); // for the database mysqli-> query ("set character set 'utf8 '"); // read database mysqli-> query ("set names 'utf8'"); // write Database

Below are some examples

Connect to mysqli

Create the MYSQL configuration file config. ini. php

The content of config. ini. php is as follows:

<? Php $ dbhost = "locallhost"; $ dbuser = "hehehe"; $ dbpwd = "123456"; $ dbname = "Student"; $ charName = "gbk2312 "; sets the query Character Set gbk, gbk2312, UTF-8?>

(Use mysqli to connect to the MYSQL database)

Requery_once ("config. ini. php "); $ mysqliObj = new mysqli ($ dbhost, $ dbuser, $ dbpwd, $ dbname); if (mysqli_connect_errno () {echo" connection failed ". 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 execution of $ SQL () if ($ mysqliObj-> query ($ SQL) echo $ mysqliObj-> affected_rows; returns the inserted id (useful) during insert) $ num = $ mysqliObj-> insert_id;

Three types of processing query results

$ SQL = "select * from user"; $ result = $ mysqli-> query ($ SQL); (1) fetch_row () returns the Index Array fetch_row () while (list ($ id, $ name, $ pwd) = $ result-> fetch_row () {echo "id :". $ id. "name :". $ name. "pwd :". $ pwd. "<br>" ;}( 2) fetch_assoc () returns the joined array fetch_assoc () while ($ row = $ result-> fetch_assoc () {echo "id :". $ row ["userId"]. "name :". $ row ["userName"]. "pwd :". $ row ["password"]. "<br>";} (3) fetch_object () returned object while ($ row = $ result-> fetch_object () {echo "id :". $ row-> userId. "name :". $ row-> uerName. "pwd :". $ row-> password. "<br> ";}

An associated array is a nested array, such:

<?php$data = array(     array('name' => 'John Smith', 'home' => '555-555-5555',        'cell' => '666-555-5555', 'email' => 'john@myexample.com'),     array('name' => 'Jack Jones', 'home' => '777-555-5555',        'cell' => '888-555-5555', 'email' => 'jack@myexample.com'),     array('name' => 'Jane Munson', 'home' => '000-555-5555',        'cell' => '123456', 'email' => 'jane@myexample.com')    );?>

Use the mysqli_stmt class

MYSQL provides a pre-processing (prepared statement) mechanism. You can see that the entire command is sent once to the MYSQL server, and only parameters change in the future, MYSQL only needs to analyze the command once, which greatly reduces the amount of data to be transmitted and improves the processing efficiency of the command (note, close () should be closed immediately when no connection is required ())

Steps:

1. Prepare SQL commands
$ SQL = "insert into user (name, pwd) values (?,?) ";
$ Stms = $ mysqli-> prepare ($ SQL );
2. Bind data
$ Stms-> bind_param ('ss', $ name, $ pwd); (Note: 'ss': it must correspond to the following variables ($ name, $ pwd)
I all inerger types
D double or float type
S all other types include strings
B binary (blob, binary byte string)

)
$ Name = "huang ";
$ Pwd = "123456 ";
3. Execute
$ Stms-> execute ();
[4. execute another set of data in the binding
$ Name = "he ";
$ Pwd = "666666 ";
$ Stms-> execute ();
]
5. Disable
$ Stmt-> close ();
$ Mysqli-> close ();
 
(Other useful parameters)
$ Num = $ stmt-> affected_rows; Number of affected rows
$ Id = $ stmt-> insert_id; when the command is inserted, the inserted row id is returned (automatic)
 
Transaction Processing

By default, MYSQL runs in autocommit mode, which means that every statement executed will be immediately written to the database. However, if you use the table type of transaction security, it does not require automatic submission.
Transaction Processing
When you execute multiple MYSQL commands, you certainly want to return the status before executing the command if one of the commands fails and all the commands are not executed.
This requires transactions.

Simple use of the transaction process
1. Write SQL commands
$ Sql1 = "insert user (name) values ('huangt', '20140901 ')";
$ Sql2 = "update account set number = number + 1 ";
2. Disable the automatic commit mode for MYSQL Transaction Processing
$ Mysqli-> cutocommit (0 );
3. Run the 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 operations are successfully executed and the initial state of rollback fails.
If ($ success ){
$ Mysqli-> commit ();
Echo "execution successful ";
} Else {
$ Mysqli-> rollback ();
Echo "failed to execute ";
}
5. Restore the automatic submission mode for MYSQL Transaction Processing
$ Mysqli-> cutocommit (1 );
$ Mysqli-> close ();

 
Currently, mysql supports transactions only for InnDB and BDB data packet types.
InnoDB is the fastest
(Create an InnDB 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