Querying databases from the Web: PHP and MySQL

Querying databases from the Web: How the WEB database architecture works
A user's browser sends an HTTP request to request a specific web page and submits the form from this page to the PHP script file (for example, results. php) for processing.
After receiving a request to the results. PHP page, the web server retrieves the file and passes it to the PHP engine for processing.
The php engine starts parsing scripts. The script mainly includes commands for connecting to the database and executing the query. PHP starts a connection to the MySQL server and sends an appropriate query to the server.
When the MySQL server receives a database query request, it starts to process the query and returns the query result to the PHP engine.
After the PHP engine runs the script, the HTML is returned to the web server.
The Web server then returns HTML to the client browser, and the user can see the response result page.
Basic Steps for querying databases from the Web
Check and filter data from the user. First, we will filter the blank characters that the user may accidentally enter at the start or end of the search condition, which is implemented using the trim () function. The reason why we are so troublesome to check user input data is to prevent multiple interfaces from connecting to the database, because the user enters from different interfaces, which may cause security problems.
Then, when preparing to use any data entered by the user, filter some control characters as well. When the user inputs data to the database, the data must be escaped ,, in this case, the stolen functions include the addslashes () function, the stripslashes () function, and the get_magic_qutoes_gpc () function. The addslashes () function requires a backslash before certain characters for the database query statement. The stripslashes () function removes the backslash character from the string. get_magic_qutoes_gpc () function magic adds the Escape Character "\" to get the magic_quotes_runtime setting of the current activity configuration. If the magic quotation marks are disabled during runtime, 0 is returned; otherwise, 1 is returned. We can also use htmispecialchars () to encode special characters in HTML. The htmispecialchars () function converts some predefined characters into HTML entities. [the predefined characters are: & (and) becomes & "(double quotation marks) becomes" '(single quotation marks) becomes' <(less than) becomes <> (greater than) becomes>]
Establishing a connection to the appropriate database PHP provides the function library mysqli (I indicates improvement) for connecting to MySQL ).
When using the mysqli function library in PHP, you can use object-oriented or process-oriented Syntax:
1. object-oriented, @ $ db = new mysqli ('hostname', 'username', 'Password', 'dbname'); returns an object
2. process-oriented: @ $ db = mysqli_connect ('hostname', 'username', 'Password', 'dbname'); a resource is returned, which indicates the database connection, if you use the procedure method, you must pass this resource to all other functions of mysqli. This is very similar to processing functions.
Most functions of mysqli have object-oriented interfaces and process interfaces. The difference between them is that the function name of the Process version starts with mysqli _ and the resource handle obtained by the mysqli_connect () function must be passed in. For this rule, data connection is an exception because it is created by the constructor of the mysqli object. Therefore, you must check the connection attempt. The mysqli_connect_errno () function returns an error code when a connection error occurs. If the connection is successful, 0 is returned.

Note:
When connected to the database, the Meeting error blocker @ is usually used as the first include Code . In this way, you can skillfully handle any errors or exceptions. In addition, mysqk limits the number of connections to the database at the same time. The mysqli parameter max_connections determines the number of simultaneous connections. This parameter and the related Apache parameter maxclients are used to tell the server to reject new connection requests, this ensures that system resources are no longer requested or used when the system is busy or when the system is paralyzed. To set the maxclients parameter in Apache, edit the httpd. conf file in the system. To set the max_connections parameter for mysqli, You can edit the file my. conf.
Select the database to use: Use the use dbname command on the MySQL command line; use $ db-> select_db (dbname); or mysqli_select_db (db_resource, dbname) in PHP ).
To query a database, you must first construct a query statement: $ query = "select * from user"; then run $ result = $ db-> query ($ query ); or $ result = mysqli_query ($ db, $ query). The object-oriented version returns a result object, and the procedural version returns a result resource. No matter which method saves the result in the $ result variable, it will be used after work. If the function fails to run, false is returned.
Different functions are used to obtain the query results from the result object or identifier in different ways. The result object or identifier is the key to accessing the returned row of the query.
We usually need to obtain the number of rows in the result set, and use the mysqli_fetch_assoc () function.
Number of returned rows: $ num_results = $ result-> num_rows; (the number of rows is saved in the num_rows member variable of the object) or $ num_results = mysqli_num_rows ($ result );
Then, traverse each row in a loop and call $ ROW = $ result-> fectch_assoc (); or $ ROW = mysqli_fetch_assoc ($ result); to return information about the row. If the row is returned by an object, each keyword is an attribute name, and each value is the corresponding value in the attribute. If the row is returned by a resource, an array is returned.
There are other methods to get results from the result identifier, such as using $ ROW = $ result-> fecth_row ($ result); or $ ROW = mysqli_fetch_row ($ result ); you can also use $ ROW = $ result-> fecth_object (); or $ ROW = mysqli_fecth_object ($ result ); jiang and his entourage go back to an object.
First release the result set from database disconnection: $ result-> free (); or mysqli_free_result ($ result); then close the database connection: $ db-> close () or mysqli_close ($ dB); strictly speaking, this is not required because they will be automatically disabled when the script is executed.

Query databases from the Web: Use prepared statements

The mysqli function library supports the use of prepared statements. They can speed up the execution of a large number of identical queries with different data, or be immune from the SQL Injection style (injection-stytle -- attacks.
The basic idea of the prepared statement is to send a query template to MySQL and then send data separately. We can send a large amount of identical data to the same prepared statement. This feature is very useful for batch insert operations.

The following steps are generally used:
1. Create a template. Example: $ query = "insert into user values (?,?,?,?) ";

2. Use the prepared statement to construct a statement object or resources to be used for actual processing. $ Stmt = $ db-> prepare ($ query); or mysqli_stmt_prepare ($ query );

3. Call $ stmt-> bind_param ("sssd", $ str1, $ str3, $ str3, $ int4) or mysqli_stmt_bind_param ("sssd", $ str1, $ str3, $ str3, $ int4) tells PHP that the variables should be replaced by question marks. The first parameter is a formatted string followed by the variable to be replaced.

3. Call the $ stmt-> execute () or mysqli_stmt_execute () function to run the query statement.
For select-type queries, you can use the $ stmt-> bind_result () or mysqli_stmt_bind_result () function to provide a list of variables to be filled in the result column, and then call $ stmt-> fetch () each time () or mysqli_stmt_fetch () function, the values of the next row in the result set will be filled in these bind variables.

use other interfaces for PHP interaction with databases
PHP supports functions connected to many different databases, including Oracle, Microsoft SQL Server, and PostgreSQL. Generally, the basic principles for connecting and querying these data sets are the same, and the names of some functions may be different. If you want to use a special database that PHP does not support, you can use the conventional ODBC function.
ODBC indicates an open database connection, which is the standard for database connection. ODBC only has the priority function of any function set. If it is required to be compatible with all databases, it cannot use any special functions of the database.
apart from the function libraries attached to PhP, some database abstract classes available, such as mdb2, allow the same function name for different database types. However, you must install the abstraction layer in advance, such as the pear mdb2 abstraction layer.