PHP mysqli Common Code Collection

PHP5.0 began with the use of the early MySQL database extension functions, but also with the new extended mysqli technology to achieve the 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 , a version of 1 or higher can be used, (i) indicates that the input, using mysqli, executes faster, more convenient, more efficient, and can make the database access more secure (because of the use of class mode)

Use mysqli simple process

Setting 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 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 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 up/down] [field 2],[asc/desc L/descending];
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 the 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

Connection mysqli

Build MySQL configuration file config.ini.php

config.ini.php content is as follows:

<?php  $dbhost = "Locallhost"; $dbuser = "hehehe"; $dbpwd = "123456"; $dbname = "Student"; $charName = "gbk2312"; Set check To inquire about 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 operations)

Inquire

(single query) $sql = "DROP table if exists user;"; $MYSQLIOBJ->query ($sql);  (Multiple queries) $MUSQLIOBJ->multip_query ($sql)   returns the number of rows affected by the execution $sql ()  if ($mysqliObj->query ($sql)) echo $MYSQLIOBJ- >affected_rows;  When insert is inserted, returns the inserted ID (useful)   $num = $MYSQLIOBJ->insert_id;

Three processing of 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 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> ";  }

An associative array, which is a nested array 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 ',        ' 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 after the parameter changes, MySQL only need to do a single analysis of the command is enough, greatly reducing the amount of data to be transferred, It also improves the processing efficiency of commands (note that close () should be closed immediately when no connection is required)

Steps:

1, prestaged SQL command
$sql = "INSERT into user (NAME,PWD) VALUES (?,?)";
$stms = $mysqli->prepare ($sql);
2. Bind Data
$stms->bind_param (' SS ', $name, $pwd); (Note ' SS ': it is to correspond to the following variable ($name, $pwd)
I all Inerger type
D double or float type
s all other types include string
B binary (blob, binary byte string)

)
$name = "Huang";
$pwd = "123456";
3. Execute
$stms->execute ();
[4. Perform 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; The number of rows affected
$id = $stmt->insert_id; Returns the inserted row ID (automatic) when it is an insert command

Transaction

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,
Transaction
is a behavior that you do not want autocommit to occur when you execute multiple MySQL commands, of course, when one of the commands fails, all the commands do not execute, and the status before the command is returned
this uses the transaction.

Simply apply transaction flow
1. Write the SQL command
$sql 1 = "Insert User (name) values (' Huang ', ' 123456 ')";
$sql 2 = "Update account set number = Number+1";
2. Turn off the automatic commit mode for MySQL transaction
$mysqli->cutocommit (0);
3. Test 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 execution, all executed successfully, with failed rollback initial
if ($success) {
$mysqli->commit ();
echo "Execution succeeded";
} else{
$mysqli->rollback ();
echo "Execution failed";
}
5. Restore the automatic commit mode for MySQL transaction
$mysqli->cutocommit (1);
$mysqli->close ();

MySQL currently only supports transactions with INNDB and BDB two packet types
InnoDB fastest
(create inndb type table)

CREATE table user (id int () NOT NULL auto_increment,name varchar (a) not null,pwd varchar (a) Not null,primary key (ID)) Ty Pe=innodb

