PHP query MySQL summary and preprocessing

Task: Further explore mysqli_multi_query () usage.

 

1. Connect to MySQL: $ DBC = mysqli_connect (host, user, password, databasename );

It is equivalent:

$ DBC = mysqli_connect (host, user, PWD); mysqli_select_db ($ DBC, db_name );

 

If an error occurs, call: mysqli_connect_error () to return the error message, without parameters.

 

$ DBC = @ mysqli_connect (host, user, PWD, DB) or die ('unable to connect to MySQL: '. mysqli_connect_error ());

 

@ Is an error control operator to prevent PHP errors from being displayed in a web browser. In addition, @ can also be placed before mysqli_query. The above is a preferred practice, because the error will be caused by or

 

Die processing. Die () terminates script execution. You can place the connection file outside the path.

 

Set the encoding: mysqli_query ("set names gb2312 ");

 

Ii. query execution: for select, delete, update, and insert queries, $ result = mysqli_query ($ DBC, SQL) is used.

 

Insert, delete, update, and other queries do not return results, $ result returns true or false, so you can use this to determine the next step: $ result = mysqli_query

 

($ DBC, SQL); if ($ result) {// susses} if the query fails, a MySQL error may occur. You may call mysqli_error ($ DBC) // note and

 

Differences between mysqli_connect_error ()

 

3. Close the connection: mysqli_close ($ DBC) is not required. php will automatically close the connection at the end of the script, but it is best to write it down.

 

4. Multiple queries: mysqli_multi_query () allows multiple queries to be executed simultaneously. But the syntax is more complex. Especially when the result is returned. 5. Search the SELECT query results:

 

Mysqli_fetch_array ($ result [, type]) is the most common method. A row of data is returned in array format once. It is used with while () to traverse the returned data. With

 

Select the type parameter to specify the returned array type: associated or indexed, or both. The parameter type is as follows: mysqli_assoc example: $ rows

 

['Columnname'] mysqli_num example: $ rows [0], which is more efficient. Example of mysqli_both: $ rows [0] or $ rows

 

['Columnname'] When mysqli_fetch_array ($ result [, type]) is used, you can take an optional step to combine: Once the query result is finished, you can release this

 

To eliminate the system memory overhead occupied by $ result. This step is optional. php will also automatically clean up when results are returned: mysqli_free_result ($ result) // note

 

Is the parameter $ rows! The process is as follows: While ($ rows = mysqli_fetch_array ($ result) // or while ($ rows = mysqli_fetch_array

 

($ Result, mysqli_assoc) {// traverse ...... code do something ...... echo $ rows [0] mysqli_free_result ($ result )}

 

Note:

 

Mysqli_fetch_array () can be mysqli_num or mysqli_assoc

 

Mysqli_fetch_assoc () is equivalent to mysqli_fetch_array ($ result, mysqli_assoc.

 

6. To ensure SQL security, use the escape Function mysqli_real_escape_string ($ DBC, para). This function is used to check the variables submitted by the user and combine them into the SQL query statement.

 

It will escape characters that may be unintentional or contain malicious characters. For example, if a single quotation mark is used, the name of a foreigner may contain this symbol (for example, O 'Toole.Case:

 

$ Name = $ _ post ['name']; $ name = mysqli_real_escape_string ($ DBC, $ name); $ query = "Select... from TB where

 

Name = '$ name' "; // This ensures the security of parameters when SQL is introduced. Note: If the magic quotes magic reference is enabled for versions earlier than PhP6

 

Before using mysqli_real_escape_string, you need to use stripslashes (para) to delete any slashes added by the magic reference, as follows: $ fn =

 

Mysqli_real_escape_string ($ DBC, trim (stripslashes ($ _ post ['firstname']);

 

7. count the number of records returned by the SELECT statement: Use mysqli_num_rows ($ result) to count the number of rows returned by the SELECT statement. $ Num = mysqli_num_rows ($ R),

 

The while process mentioned above can be changed to the following more rigorous statement, not just to analyze whether the query is successful, because if the database is empty, no error will occur. $ SQL =

 

"Select * from TB where id = $ id"; $ r = @ mysqli_query ($ DBC, $ SQL); $ num = mysqli_num_rows ($ R ); if ($ num> 0) {// This is better than if

 

($ R) more accurate. It is not just an analysis of whether or not the operation is successful. // Do something; mysqli_free_result ($ R)} mysqli_close ($ DBC );

 

8. insert, update, and delete rows returned: different from the preceding statements, if the query is not select, The mysqli_affected_rows () function is used to return the affected rows.

 

Number. Usage: $ num = mysqli_affected_rows ($ DBC); // note that the parameter is $ dBc; for example, $ q = "Update TB set pass = sha1

 

('$ Newpassword') Where id = $ row [0] "; $ r = @ myslqi_query ($ DBC, $ q); If (mysqli_affected_rows ($ DBC) = 1) {// do

 

Something} else {echo mysqli_error ($ DBC); exit (); // terminate the script. }

 

Note: 1. If you use truncate TB to clear a table

 

Mysqli_affected_rows () returns 0 even if the query is successfully executed and each row is deleted.

 

2. If the value of any column is not changed in the update query, for example, if the same password is used to replace an old password, 0 is returned.

 

9. batch query: preprocessing statements

 

(Chapter 2 Section 12th: p311) version: MySQL 4th starts to add preprocessing.

 

PhP5 can be used. Benefits of preprocessing: 1. greater security. 2. better performance. 3. batch query

 

. For pre-processing statements, only the query itself is sent to MySQL, and the query is parsed once, and the value is sent to MySQL separately.

 

$ Q = 'insert into Tb (Num) values (?) ';

 

$ Stmt = mysqli_prepare ($ DBC, $ q );

 

Mysqli_stmt_bind_param ($ stmt, 'I', $ N );

 

For ($ n = 1; $ n <= 100; $ n ++) {mysqli_stmt_execute ($ stmt);} You can create preprocessing using insert, update, delete, and select queries,

 

Steps:

 

1. Define query: $ q = "select firstname, lastname from users where uid =? "; // (Normally, uid = $ id)

 

2. Pass the query to MySQL for preprocessing: $ stmt = mysqli_prepare ($ DBC, $ q); // MySQL parses the query but does not execute it.

 

3. Bind the variable to the query Placeholder "? ", As shown in the following figure: mysqli_stmt_bind_param ($ stmt, 'I', $ id). The 'I' function indicates mysql_stmt_bind_param.

 

The expected value is of the int type,

 

There are several types:

 

Letter indicates the bound value type

D decimal

 

I integer

 

B blob (binary type)

 

S all other types

 

If the query statement has multiple variables, for example, $ q = "select uid, firstname from users where email =? And pass = sha1 (?) "; // Note none here

 

Right? The question mark is enclosed in single quotation marks, even if it is a simplified type. This is different from standard query. Multiple variables are listed in quotation marks in sequence when they are bound. As follows: $ stmt =

 

Mysqli_prepare ($ DBC, $ q); mysqli_stmt_bind_param ($ stmt, 'ss', $ E, $ P); note that before calling the binding function, you do not need to change it first.

 

Volume definition settings, which are only set below $ E and $ P above. This will not cause errors. 4. After binding, you can assign values to the PHP variable (if no value exists ). Then execute the statement.

 

$ Id = 15; mysqli_stmt_execute ($ stmt); 5. Disable preprocessing: mysqli_stmt_close ($ stmt); 6. Close mysqli_close ($ DBC); run

 

If an error occurs during preprocessing, call mysqli_stmt_error ($ stmt. Example: $ DBC = mysqli_connect

 

('Localhost', 'username', 'pwd', 'Forum '); $ q = 'insert into messages (forumid, parentid, userid, subject, body, forumdate)

 

Values (?,?,?,?,?, Now () '; $ stmt = mysqli_prepare ($ DBC, $ q); mysqli_stmt_bind_param

 

($ Stmt, 'iiiss ', $ forumid, $ parentid, $ userid, $ subject, $ body); $ forumid = (INT) $ _ post ['forumid']; $ parentid = (INT) $ _ post

 

['Parentid']; $ user_id = 3; $ subject = strip_tags ($ _ post ['subobject']); // strip_tags $ body = strip_tags ($ _ post ['body']);

 

Mysqli_stmt_execute ($ stmt); If (mysqli_stmt_affected_rows ($ stmt) = 1) {// do...} else {echo mysqli_stmt_error

 

($ Stmt);} mysqli_stmt_close ($ stmt); mysqli_close ($ DBC); The preceding statement demonstrates preprocessing,

 

In fact, preprocessing has two types of statements:

 

1. Bind the parameter (bound parameter): the example above

 

2. Bind result: bind the query result to the PHP variable.

 

10. Prevent SQL injection: (Chapter 12th, section 4th: p311) 1. verify the data to be used in the query. If possible, the type can be forcibly converted. For example, $ forumid =

 

(INT) $ _ post ['forumid']; if ($ forumid> 0 ).... // if it is forcibly converted to int value = 0, the Data Type requirement is not met. 2. Use

 

Mysqli_real_escape_string ($ DBC, para) 3. Use mysqli_real_escape_string ($ DBC, para) as an alternative: preprocessing.

 

11. Earlier Connection Methods for PHP and MySQL: mysql_connect, which is similar to the above I letter. The following is a simple example: $ conn =

 

Mysql_connect ("127.0.0.1", "mysqltest", "123456 ");

 

Mysql_select_db ("Shop"); // If $ selectdb = mysql_select_db ("Shop") is used; then $ selectdb = 1

 

Mysql_query ("set names gb2312"); // mysql_query ("set names utf8 ");

 

$ Exec = "select * from product"; $ result = mysql_query ($ exec, $ conn); // or: $ result = mysql_query ($ Exec); While

 

($ Rs = mysql_fetch_object ($ result) {echo "Product Name :[". $ RS-> pname. "]"; echo "Price :". $ RS-> price. ""; echo"

 

Between: ". $ RS-> addtime." "; echo" ";}echo $ result; to determine whether the result is output again, use: $ conn = mysql_connect

 

("Maid", "mysqltest", "123456 ");

 

Mysql_select_db ("Shop ");

 

Mysql_query ("set names gb2312"); // mysql_query ("set names utf8 ");

 

$ Exec = "select * from product"; if ($ result = mysql_query ($ exec, $ conn) {While ($ rs = mysql_fetch_object ($ result ))

 

{Echo "Product Name :[". $ RS-> pname. "]"; echo "Price :". $ RS-> price. ""; echo "warehouse receiving time :". $ RS-> addtime. ""; echo"

";}}

 

Appendix:

 

A. Get the last record after insert: two methods:

 

1. Use the last_insert_id () function of MySQL. "Insert into...; select last_insert_id ()"

 

2. Use mysql_insert_id () or mysqli_insert_id () of PHP to return the same value: mysql_insert_id of PHP ([Resource $ link_identifier])

 

The function returns the id you need. The optional parameter is the handle for connecting PHP to MySQL. Each connection has a different handle. For example, mysql_query ("insert

 

Mytable (product) values ('kossu') "); printf (" Last inserted record has ID % d/N ", mysql_insert_id ());

 

B. Several Functions: trim (), ltrim (), rtrim () Exit (), strip_tags () remove any HTML and PHP tag strings contained in the string. If it is a string

 

The HTML and PHP labels are originally incorrect. For example, if there is less than the symbol, an error is returned. This function and fgetss () have the same functions. $ Text ='

 

Test paragraph.

 

 

Other text '; echo strip_tags ($ text); // result: test paragraph. Other text // license

 

 

And echo strip_tags ($ text, ''); // result:

 

Other text

 

 

Test paragraph.

 

 

 

Http://blog.sina.com.cn/s/blog_5674da320100i6tr.html