PHP mysqli Common code collection _php tips

PHP5.0 began, not only can use the early MySQL database extension functions, but also with the new extended mysqli technology to achieve information exchange with the MySQL database, php mysqli extension is encapsulated in a class, it is an object-oriented technology, only in PHP5 and MYSQL4 , 1 or higher can be used, (i) to indicate that the access, using MYSQLI, faster, more convenient, more efficient, can also make database accesses more secure (because of the class mode)

Using mysqli simple process

Setting up the php.ini configuration file
Extension=php_mysqli.dll

Create a connection

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

detecting connections

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

Get 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 connection

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 l/drop] [field 2],[asc/desc/drop];
DELETE from [table name] WHERE [expression];
UPDATE [table name] SET [Field 1]=[value 1],[field 2]=[value 2] WHERE [expression];
Get total number of rows: SELECT COUNT (*) from [table]

Chinese garbled

For page 
header ("Content-type:text/html;charset=utf-8"); 
For database 
mysqli->query ("Set CHARACTER set ' UTF8 '");/Read library  
mysqli->query ("Set NAMES ' UTF8 '");//write Library  

Here are some examples

Connect mysqli

Set up MySQL configuration file config.ini.php

config.ini.php contents are as follows:

<?php 
 $dbhost = "Locallhost";
 $dbuser = "hehehe";
 $dbpwd = "123456";
 $dbname = "Student";
 $charName = "gbk2312"; Set query character set Gbk,gbk2312,utf-8
?>

(link MySQL database with mysqli)

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 actions)

Query

(single query)
$sql = "DROP table if exists user;";
$MYSQLIOBJ->query ($sql);
 
(more than one query)
$MUSQLIOBJ->multip_query ($sql)
 

returns the number of rows affected by the execution $sql ()
 
if ($mysqliObj->query ($sql))
Echo $MYSQLIOBJ- >affected_rows;
 
When inserting inserts, returns the inserted ID (useful) 

 $num = $MYSQLIOBJ->insert_id;

Three kinds of processing 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. " <br> ";
 
}
(2) Fetch_assoc () returns the associative array
FETCH_ASSOC () while
($row = $result->fetch_assoc ()) {
 echo ID:. $row [" UserId "]." Name: ". $row [" UserName "]." pwd: ". $row [" Password "]." <br> ";
}
 
(3) Fetch_object () returns the object while
($row = $result->fetch_object ()) {
echo ID:. $row->userid. "Name:". $ Row->uername. "PWD:". $row->password. " <br> ";
 
}

Associative arrays, which are nested arrays such as:

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

>

Using the Mysqli_stmt class

mysql4.1 version began to provide a preprocessing (prepared statement) mechanism, it can see the entire command sent to the MySQL server once, only the parameter changes, MySQL only need to do the analysis of the command is enough, greatly reduce the amount of data to be transmitted, Also increases the processing efficiency of commands (note that close () should be closed immediately when no connection is required)

steps:

1, Prepare SQL command
$sql = "INSERT into user (NAME,PWD) VALUES (?,?)";
$stms = $mysqli->prepare ($sql);
2. Binding Data
$stms->bind_param (' SS ', $name, $pwd); (Note ' SS ': it corresponds to a subsequent variable ($name, $pwd)
I all Inerger types
D double or float type
s all other types include string
b Binary (blob, binary byte string)

)
$name = "Huang";
$pwd = "123456";
3. Implementation
$stms->execute ();
[4. Executing another set of data in a 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; Returns the inserted row ID (automatic) When the Insert command is inserted

Transaction Processing

By default, MySQL runs in autocommit (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 to automatically commit the behavior of the
Transaction processing
When executing multiple MySQL commands, of course, when one of the commands fails, all commands are not executed, returning to the state before executing the command
It's going to take a business.

Simple use of transactional processes
1. Write Good SQL commands
$sql 1 = "Insert User (name) values (' Huang ', ' 123456 ')";
$sql 2 = "Update account set number = Number+1";
2. Turn off automatic submission mode for MySQL transaction processing
$mysqli->cutocommit (0);
3. Trial execution Command
$success = true;
$res 1 = $mysqli->query ($sql 1);
if (! $res 1 or $mysqli->affected_rows!=1) {
$success = false;
}
$res 2 = $mysqli->query ($sql 2);
if (! $res 2 or $mysqli->affected_rows!=1) {
$success = false;
}
4. View performance, all successful execution, with failure rollback initial state
if ($success) {
$mysqli->commit ();
echo "Successful execution";
}else{
$mysqli->rollback ();
echo "Execution failed";
}
5. Restore the automatic submission mode of MySQL transaction processing
$mysqli->cutocommit (1);
$mysqli->close ();


MySQL currently only supports transactions with INNDB and BDB two types of packets
InnoDB the fastest
(Create inndb Type table)

CREATE TABLE user (
ID int (a) NOT NULL auto_increment,
name varchar is not NULL,
pwd varchar (x) not Null,
   primary key (ID)
) type=innodb