take me home country roads Querying databases from the Web: PHP and MySQL

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) for 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 to query a database from the web
Check and filter data from users First, we will filter out whitespace characters that users may have accidentally entered at the beginning or end of their search criteria, which is achieved using the function trim(). 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 quotation mark) Becomes " ' (single quote) becomes ' < (less than) becomes < > (greater than) becomes >]
Establish a connection to an appropriate database. PHP provides the function library mysqli (i represents 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'); Return an object
2. Process-oriented: @ $db = mysqli_connect('hostname','username','password','dbname'); Returns a resource. This resource represents the database connection, and if the process method is used, it 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 resource handle obtained by the mysqli_connect() function to be passed in. Data joinability is an exception to this rule because it is created by the mysqli object's constructor. Therefore, a check is required when trying to connect. The mysqli_connect_errno() function will return an error number when a connection error occurs, and 0 if successful.
Please note:
When connecting to the database, usually the error suppressor @ is used as the first Contains 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, edit the file my.conf.
Choose 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 a 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, 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 and call $ in the loop row = $result->fectch_assoc(); or $row = mysqli_fetch_assoc($result); returns 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 array ; You can also use $row = $result->fecth_object(); or $row = mysqli_fecth_object($result); to return to an object.
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); Strictly speaking, This is not necessary as they will be automatically closed when the script is finished executing.
Query the database from the Web: Use Prepared statement
The mysqli function library supports the use of prepared statements. They can improve speed when executing large numbers of the same query with different data, and also protect against SQL injection-style attacks. The basic idea of ​​​​the prepared statement is to send a query template to MySQL that needs to be executed. , 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 insertion operations.
We generally use the following steps:
1. Construct a template. . Inserted as an example: $query = "insert into user values(?,?,?,?)";
2. Use the 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 format string, followed by the variables to be replaced.
3. Call $stmt->. ;execute() or mysqli_stmt_execute() function will actually run this query statement
For select type queries, you can use the $stmt->bind_result() or mysqli_stmt_bind_result() function to provide the list of variables you want to populate the result column, and then call each time $stmt->fetch() or mysqli_stmt_fetch() function, the value of the next row of the result set will be filled into these bind variables.
Use other interfaces for PHP to interact with the database
PHP supports functions that connect to many different databases. , including Oracle, Microsoft SQL Server, and PostgreSQL. Generally, the basic principles of connecting and querying these databases are the same, and individual function names may be different. If you want to use a special database that PHP does not yet provide support, you can use regular ODBC functions. .
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 must be compatible with all databases, you cannot use any special functions of the database except the function library that comes with PHP. In addition, some available database abstract classes such as MDB2 allow the use of 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.
The above has introduced the take me home country roads PHP and MySQL article on querying the database from the Web, including the content of take me home country roads. I hope it will be helpful to friends who are interested in PHP tutorials.