PHP Mysqli common code collection

Starting with PHP 5.0, you can not only use the early mysql database extension functions, but also use the newly expanded mysqli technology to communicate with the mysql database. PHP's mysqli extension is encapsulated in a class, which is an object-oriented technology. , can only be used in PHP5 and MYSQL4,1 or higher versions, (i) indicates that it should be entered, using mysqli, the execution speed is faster, more convenient, more efficient, and can also make database access safer (because the class mode is used )

Simple process using mysqli

Set PHP.ini configuration file
extension=php_mysqli.dll

//Create connection

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

//Detect connection

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

//Get 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 rise/fall] [field 2],[ASC/DESC rise/fall];
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

//针对页面 
header("Content-Type:text/html;charset=utf-8"); 
//针对数据库 
mysqli->query("SET CHARACTER SET 'utf8'");//读库  
mysqli->query("SET NAMES 'utf8'");//写库

Here are some examples

Connect to mysqli

Create 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"; 设置查询字符集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 "连接失败".mysqli_connect_error();
 exit();
}
$mysqliObj->query("set name $charName");

(Other operations)

Inquire

(单条查询)
$sql = "drop table if exists user;";
$mysqliObj->query($sql);
  
(多条查询)
$musqliObj->multip_query($sql)
  
 
返回执行$sql受影响的行数（）
  
if($mysqliObj->query($sql))
echo $mysqliObj->affected_rows;
  
insert 插入时，返回插入的id (很有用) 
 
 $num = $mysqliObj->insert_id;

Three types of processing query results

$sql = "select * from user";
 $result = $mysqli->query($sql);
  
(1)fetch_row()  返回索引数组
fetch_row()
while(list($id,$name,$pwd)=$result->fetch_row()){
echo "id: ".$id." name:".$name." pwd:".$pwd."<br>";
  
}
(2)fetch_assoc() 返回关联数组
fetch_assoc()
while ($row = $result->fetch_assoc()){
 echo "id:".$row["userId"]." name:".$row["userName"]." pwd:".$row["password"]."<br>";
}
  
（3）fetch_object()返回对象
while($row = $result->fetch_object()){
echo "id:".$row->userId." name:".$row->uerName." pwd:".$row->password."<br>";
  
}

Associative arrays are nested arrays, such as:

<?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 mysqli_stmt class

Mysql4.1 version began to provide a preprocessing (prepared statement) mechanism, which can see the entire command sent to the MYSQL server once. Only the parameters will change in the future. MYSQL only needs to analyze the command once, which greatly reduces the need for transmission. The amount of data also improves the processing efficiency of commands (note, close() should be closed immediately when the connection is not needed)

step:

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)
                                                                                                                                                                 because Dough or float type
                                                                                                                                                                            s 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.Close
$stmt->close();
$mysqli->close();

(Other useful parameters)
$num = $stmt->affected_rows; Number of rows affected
$id = $stmt->insert_id; When it is an insert command, return the inserted row id (automatic)

事务处理

在默认情况下，MYSQL是以自动提交（autocommit）模式运行，这就意味着所执行的每一个语句都将立即写入数据库中，但如果使用事务安全的表格类型，是不希望自动提交的行为的
事务处理
当执行多条MYSQL命令时，当然希望当其中一条命令出错时，所有的命令都不执行，返回执行命令之前的状态
这就用到事务了
  
 简单运用事务流程
1.写好SQL命令
  $sql1 = "insert user(name) values('huang','123456')";
  $sql2 = "update account set number = number+1";
2.关闭MYSQL事务处理的自动提交模式
  $mysqli->cutocommit(0);
3.试执行命令 
  $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.查看执行情况，都成功执行，有失败回滚初态
  if($success ){
     $mysqli->commit();
     echo "执行成功";
  }else{
     $mysqli->rollback();
     echo "执行失败";
  }
5.恢复MYSQL事务处理的自动提交模式
  $mysqli->cutocommit(1);
  $mysqli->close();
  
mysql 目前只有InnDB和BDB两种数据包类型才支持事务 
InnoDB最快
（创建InnDB类型表）

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