Querying databases from the Web with PHP and MySQL_PHP Tutorial

Querying the database from the Web: How the Web database architecture works
A user's browser issues an HTTP request for a specific Web page, where the form is submitted to a PHP script file (such as results.php) Processing
After the web server receives the request for the results.php page, it retrieves the file and passes it to the PHP engine for processing
The PHP engine starts parsing the script. The script mainly includes commands to connect to the database and execute queries. PHP initiates a connection to the MySQL server and sends the appropriate query to the server.
The MySQL server receives the database query request, starts processing the query, and returns the query results to the PHP engine.
After the PHP engine completes running the script, it returns the HTML to the web server.
The web server then returns the HTML to the client browser, and the user can see the response result page.
Basic steps for querying a database from the web
Checking and filtering data from users First, we will filter for whitespace characters that users may have accidentally entered at the beginning or end of their search criteria, this is done with the function trim( ) to achieve. The reason why we go to such trouble to check user input data is to prevent multiple interfaces from connecting to the database, because users enter from different interfaces, which may cause security issues.
Then, when preparing to use any data input by the user, some control characters must also be appropriately filtered. When the user inputs data into the database, the data must be escaped. The stolen functions used at this time include the addslashes() function and stripslashes. () function and get_magic_qutoes_gpc() function. The addslashes() function adds a backslash before certain characters for database query statements, etc.; the stripslashes() function removes the backslash characters in the string; the get_magic_qutoes_gpc() function magically adds the escape character "" to get The currently active configuration magic_quotes_runtime setting, returns 0 if magic quotes are turned off at runtime, 1 otherwise. We can also use htmispecialchars() to encode special meaning characters in HTML. The htmispecialchars() function converts some predefined characters into HTML entities [the predefined characters are: & (ampersand) becomes & " (double quote) Become " ' (single quote) Become ' < (less than) Become < > (greater than) Become >]
Establish a connection to an appropriate database. PHP provides a function library mysqli for connecting to MySQL (i represents improvement) .
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'); returns a resource, which represents the database connection, and If you use the procedural method, you must pass this resource to all other functions of mysqli. This is very similar to the processing function
Most functions of mysqli have object-oriented interfaces and procedural interfaces. The difference between the two is that the function name of the procedural version starts with mysqli_ and requires the resources obtained by the mysqli_connect() function to be passed in. handle. Data joinability is an exception to this rule because it is created by the mysqli object's constructor. Therefore, you need to check when trying to connect. The mysqli_connect_errno() function will return an error number when a connection error occurs. If successful, it will return 0.

Please note:
When connecting to the database, usually the session The error suppressor @ is used as the first containing code. This allows any errors to be handled gracefully or through exceptions. In addition, MySQK has certain limits on the number of connections to the database at the same time. The MySQLi parameter max_connections determines the number of simultaneous connections. The function of this parameter and the related Apache parameter MaxClients is to tell the server to reject new connection requests, thereby ensuring that system resources will not be requested or used when the system is busy or when the system is paralyzed. To set the MaxClients parameters in Apache, you can 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; in PHP, you can use $db->select_db(dbname); or mysqli_select_db(db_resource,dbname).
Query the database To perform a database query, you should first construct the query statement: $query = "select * from user"; and then run $result = $db->query($query); or $result = mysqli_query($db ,$query); The object-oriented version will return a result object; the procedural version will return a result resource. Regardless of the method, the result is saved in the $result variable for later use. If the function fails to run, it will return false.
Get query results Use different functions in different ways to get the query results out of the result object or identifier, which is the key to accessing the rows returned by the query.
Usually we want to get the number of rows in the result set and use the mysqli_fetch_assoc() function.
Return the number of rows: $num_results = $result->num_rows; (the number of rows is stored in the num_rows member variable of the object) or $num_results = mysqli_num_rows($result);
Then use a loop to traverse each row, in Call $row = $result->fectch_assoc(); or $row = mysqli_fetch_assoc($result); in the loop to return the row information. If the row is returned as an object, each keyword is an attribute name, and each value is the corresponding value in the attribute; if it is returned as a resource, an array is returned.
There are other ways to get the result from the result identifier, for example: use $row = $result->fecth_row($result); or $row = mysqli_fetch_row($result); to get the result back into an enumeration In an array; you can also use $row = $result->fecth_object(); or $row = mysqli_fecth_object($result); to return to an object line by line.
Disconnect from the database first release the result set: $result->free(); or mysqli_free_result($result); and then close the database connection: $db->close() or mysqli_close($db); Strict That said, this is not necessary as they will be automatically closed when the script is finished executing.

Querying the database from the Web: using Prepared statements

The mysqli function library supports the use of prepared statements. They can improve speed when executing a large number of the same query with different data, and can also protect against SQL injection-style attacks.
The basic idea of ​​the prepared statement is to send a query to MySQL that needs to be executed. Query the template and then send the data separately. We can send a large amount of the same data to the same prepared statement; this feature is very useful for batch insert operations.

We generally use a few. Steps:
1. Construct a template. Inserted as an example: $query = "insert into user values(?,?,?,?)";

2. Use prepared statement to construct a statement object Or the resources needed to complete the 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 which variables should be replaced by question marks. The first parameter is a formatter. String, followed by the variable to be replaced.

3. Call the $stmt->execute() or mysqli_stmt_execute() function to actually run the query statement
For select type queries, you can use The $stmt->bind_result() or mysqli_stmt_bind_result() function provides the list of variables you want to populate the result column, and then each time the $stmt->fetch() or mysqli_stmt_fetch() function is called, the value of the next row of the result set will be filled. into these bind variables.

Other interfaces for interacting with databases using PHP
PHP supports functions for connecting to many different databases, including Oracle, Microsoft SQL Server, and PostgreSQL. Typically, these are connected and queried quickly. The basic principle of the database is the same, and the individual function names may be different. If you want to use a special database that PHP has not provided support, you can use the regular ODBC function.
ODBC stands for open database connection, which is the standard for connecting to databases. . 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.
In addition to the function library that comes with PHP, some available database abstract classes such as MDB2 allow it. Use the same function name for different database types. But you need to install the abstraction layer in advance, such as installing the PEAR MDB2 abstraction layer.