Use of MySQLi function library in PHP and character encoding configuration of forms

Use of MySQLi function library in PHP and character encoding configuration of Forms 1. what is mysqli?

The PHP-MySQL function library is the most primitive extension library for PHP to operate MySQL databases. The I of PHP-MySQLi represents Improvement, which is equivalent to the improved enhanced version of the former and also includes relatively advanced functions, in addition, it also increases security, for example, it can greatly reduce the occurrence of SQL injection and other problems.

2. concepts related to mysql and mysqli

(1) mysql and mysqli are both functional sets in php and are not associated with the mysql database.

(2) before php5, mysql functions in php are generally used to drive mysql databases. for example, mysql_query () functions are process-oriented.

(3) after php5, the mysqli function is added. in a sense, it is an enhanced version of mysql system functions, which is more stable, efficient, and secure () corresponding to mysqli_query (), which is an object-oriented, object-driven mysql database

3. main differences between mysql and mysqli

(1) mysql does not support the following connection functions. mysql opens a connection process each time it connects, so mysql consumes less resources.

(2) mysqli is a persistent connection function. mysqli runs mysqli multiple times and uses the same connection process, thus reducing the overhead of the server. Mysqli encapsulates some advanced operations, such as transactions, and many available methods in the database operation process.

(3) mysqli supports object-oriented programming and process-oriented programming, while mysql can only process-oriented.

For example, the following code is the object-oriented programming method and process-oriented method of mysqli.

Object-oriented approach

 host_info);# close connection$mysqli->close();?>

Process-oriented approach

(4) mysqli can reduce overhead and SQL Injection risks through preprocessing statements, but mysql cannot.

To sum up, if you are using PHP5 and the mysql version is later than 5.0, we hope you can use mqsqli in the future, which is both efficient and safer, we recommend that you use object-oriented programming.

Here, we only introduce the object-oriented programming method.

Use of object-oriented functions

1,Connect to the database and obtain relevant information

 Character_set_name ()."
"; # Obtain client information echo $ mysqli-> get_client_info ()."
"; # Obtain mysql host information echo $ mysqli-> host_info ."
"; # Obtain server information echo $ mysqli-> server_info ."
"; # Obtain the server version echo $ mysqli-> server_version ."
"; # Close the database connection. Strictly speaking, this is not necessary, because when the script is executed, it will be automatically closed $ mysqli-> close ();?>

The above code runs if the connection is successful.

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 to connect to database: Unknown database 'Hello'

2.Query data

 Query ($ query); # traverse the result while ($ row = $ result-> fetch_array (MYSQLI_BOTH) {echo "id". $ row ["id"]."
";}# Release result set $ result = free (); # close the connection $ mysqli-> close ();?>

Running result

id10062id10063id10064

Note that

fetch_array(MYSQLI_BOTH)

This method has three parameters: MYSQLI_BOTH, MYSQLI_NUM, and MYSQLI_ASSOC.

If MYSQLI_BOTH is input, the index of the returned data includes both numbers and names.

array(size=26)    0=>string '10062'(length=5)    'id' => string '10062' (length=5)      1 => string '??' (length=2)      'name' => string '??' (length=2)      2 => string '10169103@qq.com' (length=17)      'email' => string '10169103@qq.com' (length=17)      3 => string '18366119732' (length=11)      'phone' => string '18366119732' (length=11)

There are also equivalent methods fetch_row (), fetch_assoc ()

The relationships between them are 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 () = Random () = $ result-> fetch_array (MYSQLI_ASSOC) = mysql_fetch_array (MYSQLI_ASSOC) returns the index column name

If the fetch_array () method does not transmit anything, MYSQLI_BOTH is input by default.

3. Insert data

 Query ($ SQL); # if the execution error is if (! $ Result) {echo "SQL statement error
"; Echo" ERROR :". $ mysqli-> errno. "| ". $ mysqli-> error; exit () ;}# if the insert operation is successful, the number of affected rows is returned echo $ mysqli-> affected_rows; # close database connection $ mysqli-> close () ;?>

If the insertion is successful, the result is 1. if the insertion fails, an error is returned.

4,Modify content

 Query ($ SQL); # if the execution error is if (! $ Result) {echo "SQL statement error
"; Echo" ERROR :". $ mysqli-> errno. "| ". $ mysqli-> error; exit () ;}# if the modification is successful, the affected rows are returned echo $ mysqli-> affected_rows; # close database connection $ mysqli-> close () ;?>

If the modification is successful, the number of modified rows is returned.

5,Preprocessing statement

 Prepare ($ SQL); # each? No. Pass (bind parameter) I d s B. None of the parameters are formatted characters. ss represents two strings. d represents the number $ stmt = bind_param ("ss", $ name, $ email); # run $ stmt-> excute (); # assign $ name = "Mike" to the variable; $ email = "mike@live.cn "; # execute $ stmt-> execute (); # execute the output echo "last ID ". $ stmt-> insert_id."
"; Echo" affects ". $ stmt-> affected_rows ."
"; # Close the database connection $ mysqli-> close ();?>

With the preceding pre-processing statements, we can also insert data.

So what are the features of preprocessing?

• High efficiency: If you execute the same statement multiple times, only the statement data is different, because you have prepared a statement on the server, then pass the different values to the server, and then execute this statement. It is equivalent to compiling once and used multiple times.

• Security: it can prevent SQL injection (? Placeholder) to prevent abnormal variable injection.

  
  

Therefore, we recommend that you use the mysqli pre-processing statement method, which is not only efficient, but also safer.

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

Question about character encoding when html forms submit characters to MySQL

First, let's look at the code in the html file.

         
          
  Test input values to MySQL
  

  

  
  
 

A form named isbn is defined here, and the allowed data type is text.

Use the post method to pass to the backend tt. php script page.

Tt. php file code

        
         
 Input to MySQL page!Returned results:
 Please go back and try again. "; exit ;}# check whether php magic characters are converted if (! Get_magic_quotes_gpc () {$ isbn = addslashes ($ v);} # define a database connection object @ $ db = new mysqli ('2017. 162.188.60 ', 'test', '2017. com ', 'test'); # set the character if ($ db-> set_charset ("utf8 ")) {// note that utf8 is not UTF-8 printf ("Current charset is: % s
", $ Db-> character_set_name ();} else {echo" Error: cocould not set this charset! ";}# Check for database connection errors if (mysqli_connect_errno () {echo" Error: cocould not connect to database. please try again later. "; exit ;}# insert data $ insert =" insert into ttt set S22 = '". $ isbn. "'"; # perform the insert operation $ result = $ db-> query ($ insert); # check whether execution is successful if ($ result) {echo $ db-> affected_rows. "values inserted into database. "; // Print the number of affected rows} else {echo" An error has occurred. the item was not added. ";}# close the connection $ db-> close () ;?>

Execution result:

Returned result: 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)