The use of PHP's mysqli function library and the character encoding configuration of the form

1. What is Mysqli

Php-mysql function library is PHP operation MySQL Database The most primitive extension library, php-mysqli I for improvement , the equivalent of the former improvement of the enhanced version, also contains the relative advanced function, in addition to increase the security itself, For example, it can greatly reduce the occurrence of SQL injection and other problems.

2. MySQL is related to the concept of mysqli

(1) MySQL and mysqli are both functional sets of PHP, which are not associated with MySQL database.

(2) before the PHP5 version, the general is to use the PHP MySQL function to drive the MySQL database, such as mysql_query () function, belongs to the process-oriented

(3) After the PHP5 version, added mysqli function, in a sense, it is the MySQL system function enhanced version, more stable more efficient and more secure, and mysql_query () corresponding to the Mysqli_query (), belong to the object- oriented , Manipulating the MySQL database with the object's way

3. The main difference between MySQL and mysqli

(1) MySQL is a non-holding connection function, MySQL each link will open a connected process, so mysqli cost less resources.

(2)mysqli is a permanent connection function , mysqli multiple runs mysqli will use the same connection process, thus reducing the server overhead. Mysqli encapsulates some of the advanced operations, such as transactions, while encapsulating many of the methods available in the DB operation process.

(3) MYSQLI supports object-oriented programming and process-oriented programming, while MySQL can only process-oriented.

For example, the following code is MYSQLI object-oriented programming and process-oriented approach

Object-oriented approach

<?php$mysqli = new Mysqli ("localhost", "Db_user", "db_passwd", "Database"); # check connectionif (mysqli_connect_    errno ()) {printf ("Connect failed:%s\n", Mysql_connect_error ()); Exit;} printf ("Host infomation:%s\n", $mysqli->host_info); # close Connection$mysqli->close (); >

Process-oriented approach

<?php$link = Mysqli_connect ("localhost", "Db_user", "db_passwd", "Database"); # check connectionif (! $link) {printf ("    Connect failed:%s\n ", Mysqli_connect_error ()); Exit;} printf ("Host infomation:%s\n", Mysqli_get_host_info ($link)); #close Connectionmysqli_close ($link);? >

(4) Mysqli can reduce the risk of overhead and SQL injection by preprocessing statements, while MySQL does not.

To sum up, if you use PHP5, and MySQL version in more than 5.0, I hope you can use mysqli in the future as far as possible using mqsqli, not only efficient, but also more secure, and recommend that you use object-oriented programming.

Here, we also only introduce object-oriented programming methods.

Object-oriented function usage

1. connect to the database and get relevant information

<?php     $mysqli  = new mysqli ("localhost", "root", "" "," MySQL ");          #如果连接失败     if  (Mysql_connect_errno ()) {         echo  "Database connection failed:". Mysql_connect_error ();          $mysqli  = null;        exit ();     }     #获取当前字符集     echo  $mysqli Character_set_name (). " <br> ";         #获取客户端信息     echo  $mysqli- >get_client_info (). " <br> ";         #获取mysql主机信息     echo $ Mysqli->host_info. " <br> ";         #获取服务器信息     echo  $mysqli- >server_info. " <br> ";        &nbSP; #获取服务器版本     echo  $mysqli->server_version. " <br> ";         #关闭数据库连接, strictly speaking, this is not necessary because they will be automatically closed when the script is completed       $mysqli->close ();? >

The above code runs the result if the connection succeeds

Latin1mysqlnd 5.0.10–20111026– $Id: e707c415db32080b3752b232487a435ee0372157 $localhost via tcp/ip5.6.12-log50612

If the connection fails, the result may be

Failed to connect to database: Access denied for user ' root ' @ ' localhost ' (using Password:yes) failed: Unknown database ' Hello '

2. Querying Data

<?php     $mysqli  = new mysqli ("localhost", "root", "" "," Test ");      #如果连接失败     if  (Mysql_connect_errno ()) {         echo  "Database connection failed:". Mysql_connect_error ();         $ Mysqli = null;        exit ();    }          #构造SQL语句      $query  =  "select *  from test order by id limit 3 ";     #执行SQL语句       $result  =  $mysqli->query ($query);          #遍历结果     while  ($row  =  $result->fetch_array (mysqli_both)) {         echo  "id". $row ["id"]. " <br> ";    }     #释放结果集 &NBsp;    $result =free ();     #关闭连接      $mysqli->close ();? >

Run results

id10062id10063id10064

It's important to note here that

Fetch_array (Mysqli_both)

In this method, there are three parameters, namely Mysqli_both,mysqli_num,mysqli_assoc.

If Mysqli_both is passed in, the index of the returned data includes both the number and the name

Array (size=26) 0=>string ' 10062 ' (length=5) ' id ' = = String ' 10062 ' (length=5) 1 = String '?? '      (length=2) ' Name ' = = String '?? '      (length=2) 2 = string ' [email protected] ' (length=17) ' email ' + string ' [email protected] ' (LENGTH=17) 3 = String ' 18366119732 ' (length=11) ' phone ' = = String ' 18366119732 ' (length=11)

In fact, there is an equivalent method Fetch_row (), FETCH_ASSOC ()

The relationship between them is as follows

$result->fetch_row () = mysql_fetch_row () = $result->fetch_array (mysqli_num) = Mysql_fetch_array (Mysqli_num) Returns the index array $result->fetch_assoc () = MYSQL_FETCH_ASSOC () = $result->fetch_array (MYSQLI_ASSOC) = Mysql_fetch_array ( MYSQLI_ASSOC) returns the index column name

If the Fetch_array () method does not pass anything, the Mysqli_both is passed by default

3, inserting data

<?php     $mysqli  = new mysqli ("localhost", "root", "" "," Test ");      #如果连接失败     if  (Mysql_connect_errno ()) {         echo  "Database connection failed:". Mysql_connect_error ();         $ Mysqli = null;        exit ();    }          #插入数据      $sql  =  "Insert into  test (Name,phone)  values  ("Hello", "10086") ";     #执行插入语句       $result  =  $mysqli->query ($sql);         #如果执行错误     if  (! $result) {        echo  "SQL statement is wrong <br> ";        echo " ERROR: ". $mysqli->errno." | ". $mysqli->error;   &Nbsp;    exit ();    }     #如果插入成功, returns the number of rows affected      echo  $mysqli->affected_rows;     #关闭数据库连接      $mysqli->close ();? >

If the insert succeeds, the result will be 1, and if it fails, an error will be made.

4, modify the content

<?php     $mysqli  = new mysqli ("localhost", "root", "" "," Test ");      #如果连接失败     if  (Mysql_connect_errno ()) {         echo  "Database connection failed:". Mysql_connect_error ();         $ Mysqli = null;        exit ();    }      #执行语句      $sql  =  "update test set name =   ' Hello '  where id = 10062 ';     #执行修改语句     $ result =  $mysqli->query ($sql);     #如果执行错误     if  (!$ result) {        echo  "SQL statement error <br>";         echo  "ERROR:" $mysqli->errno. "|". $mysqli->error;        Exit ();    }     #如果修改成功, the number of rows affected is returned     echo $ mysqli->affected_rows;     #关闭数据库连接      $mysqli->close ();? >

If the modification succeeds, the number of rows modified is also returned.

5, preprocessing statements

<?php     $mysqli  = new mysqli ("localhost", "root", "" "," Test ");      #如果连接失败     if  (Mysql_connect_errno ()) {         echo  "Database connection failed:". Mysql_connect_error ();         $ Mysqli = null;        exit ();    }      #准备好一条语句放到服务器中, such as INSERT statement      $sql  =  "Insert into test ( Name,email)  values  (?,?) ";          #生成预处理语句      $stmt  =  $mysqli Prepare ($sql);         #给站位符号每个  ?   Pass value (binding parameter) I d s b, no parameter is a formatted character, the SS represents two strings, and D represents a number      $stmt  =  Bind_param ("ss", $name, $email);         #执行     $ Stmt->excute ();          #为变量赋值      $name  =  "Mike";      $email  =  "[email protected]";         #执行      $stmt->execute ();     #执行输出     echo  "Last ID". $stmt->insert_id. " <br> ";    echo " affected ". $stmt->affected_rows." Line <br> ";         #关闭数据库连接      $mysqli->close ();? >

Through the above preprocessing statements, we can also implement data insertion.

So what are the characteristics of pretreatment?

• High efficiency, that is, if you execute the same statement multiple times, only the statement data is different, because a statement is prepared on the server side, and then pass the different values to the server, and then let this statement execute. Equivalent to compiling once, using multiple times.

• On the Safe: SQL injection (? placeholder) can be prevented to prevent abnormal variable injection.

  
  

Therefore, it is recommended to use MYSQLI's pre-processing statement method, not only high efficiency, but also more secure.

Process-oriented style

<?php$link = Mysqli_connect (' localhost ', ' my_user ', ' my_password ', ' test ');/* Check connection */if (mysqli_connect_    errno ()) {printf ("Connect failed:%s\n", Mysqli_connect_error ()); Exit ();} /* Change character set to UTF8 */if (!mysqli_set_charset ($link, "UTF8")) {printf ("Error loading character set UTF8:% S\n ", Mysqli_error ($link));} else {printf ("current character set:%s\n", Mysqli_character_set_name ($link));} Mysqli_close ($link);? >

-------------------------------------------------------------------

Questions about character encoding in HTML form submission characters to MySQL

First, let's look at the code inside the HTML file.

Here a form is defined, the name is ISBN, the allowable data type is text
Use the Post method to pass to the back-end tt.php script page.

tt.php File Code
Execution Result:
Results returned: Current charset Is:utf8.1 values inserted into database.
View Database
+----+------+--------------+| S1 | Tax |  S22 |+----+------+--------------+| 0 | 1.00 |  Test Data | | 0 | 1.00 |  Test Data | | 0 | 1.00 |  Test Data | | 0 | 1.00 |  Test Data | | 0 | 1.00 | Test Data |+----+------+--------------+5 rows in Set (0.02 sec)

The use of PHP's mysqli function library and the character encoding configuration of the form