Querying the database from the Web: how the Web Database schema works
A user's browser issues an HTTP request that requests a specific Web page, where the form form is submitted to a PHP script file (for example: results.php) for processing
After the Web server receives a request to 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 the command to connect to the database and execute the query. PHP initiates a connection to the MySQL server and sends the appropriate query to the server.
The MySQL server receives a request for a database query, starts processing the query, and returns the query results to the PHP engine.
After the PHP engine has finished running the script, return the HTML to the Web server.
The Web server then returns HTML to the client browser, and the user can see the resulting page after the response.
Basic steps for querying a database from the Web
Checking and filtering data from users first, we will filter white space characters that the user may accidentally enter at the beginning or end of their search criteria, which is implemented with function trim (). We're so in trouble. The reason for checking user input data is to prevent multiple interfaces from connecting to the database because users enter from different interfaces, which can lead to security issues.
Then, when you are ready to use any data entered by the user, you also need to filter some control characters appropriately, and when the user enters the data into the database, the data must be escaped, at which point the stolen function has the addslashes () function, the stripslashes () function, and the Get_magic_qutoes The _GPC () function. The Addslashes () function adds a backslash to some characters, such as a database query statement, and the Stripslashes () function removes the backslash character from the string, and the GET_MAGIC_QUTOES_GPC () function tricks the escape character "\". Gets the current active configuration Magic_quotes_runtime setting, returns 0 if the runtime closes the magic quotation mark, or returns 1. We can also use Htmispecialchars () to alert encoding of special meaning characters in HTML, Htmispecialchars () function converts some predefined characters to HTML entities "predefined characters are:& (and number) become &" (double quotes) becomes "' (single quote) becomes ' < (less than) becomes < > (greater than) becomes >"
Establish a connection to the appropriate database PHP provides a library of functions for connecting MySQL mysqli (i for 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 that represents the connection to the database, and if a process method is used, This resource must be passed to all other functions of mysqli. This is very similar to the handler function
Most functions of mysqli have object-oriented interfaces and procedural interfaces, and the difference is that the function name of the procedure version begins with Mysqli_ and requires a handle to the resource obtained by the incoming Mysqli_connect () function. For this rule, data can be concatenated as an exception because it is created by the constructor of the Mysqli object. Therefore, when attempting to connect, a check is required, and the Mysqli_connect_errno () function returns an error number when a connection error occurs and returns 0 if successful.
Please note:
When connecting to a database, it is usually the meeting error suppressor @ as the first containing code. This makes it possible to handle any error skillfully, or through exceptions. In addition, MYSQK has a certain limit on the number of connections to the database at the same time. The mysqli parameter max_connections determines the number of simultaneous connections, and the role of this parameter and the associated Apache parameter maxclients is to tell the server to reject new connection requests, thus ensuring that system resources are not requested or used when the system is busy or when the system is paralyzed. To set the MaxClients parameter 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 used: Use dbname on the MySQL command line, command, $db->select_db (dbname) in PHP, or mysqli_select_db (Db_resource,dbname).
Querying a database to execute a database query 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 Returns a result object, and the procedure version returns a result resource. Regardless of the method, the results are saved in the $result variable for later use. Returns False if the function fails to run.
Get query results use different functions to take query results out of a result object or identifier in different ways, and the resulting object or identifier is the key to accessing the query's returned rows.
Usually we want to get the number of rows in the result set and use the MYSQLI_FETCH_ASSOC () function.
Number of rows returned: $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);
It then iterates through each row, calls $row = $result->fectch_assoc () in the loop, or $row = Mysqli_fetch_assoc ($result), and returns information about the row. If the object returns a row, each keyword is a property name, each value is the corresponding value in the property, and an array is returned if returned as a resource.
There are other ways to get results from the result identifier, such as using $row = $result->fecth_row ($result), or $row = Mysqli_fetch_row ($result), and retrieving the results into an array of enumerations ; You can also use $row = $result->fecth_object (), or $row = Mysqli_fecth_object ($result), and the river goes back to an object.
Release result set from database disconnect first: $result->free (), or Mysqli_free_result ($result), and then close database connection: $db->close () or mysqli_close ($DB); Strictly speaking, this is not necessary because they will be automatically closed when the scripts are executed.
Querying databases from the Web: using the prepared statement
The MYSQLI function library supports the use of prepared statements. They can increase speed and protect against SQL injection style (injection-stytle--attacks) when executing a large number of identical queries with different data.
The basic idea of the prepared statement is that you can 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, which is very useful for bulk-processing insert operations.
We generally use a few steps:
1, the construction template. Insert as Example: $query = "INSERT into user values (?,?,?,?)";
2. Use the prepared statement to construct a statement object or a resource that needs to be used to complete the actual processing. $stmt = $db->prepare ($query) or Mysqli_stmt_prepare ($query);
3. Call $stmt->bind_param ("SSSD", $str 1, $str 3, $str 3, $int 4) or Mysqli_stmt_bind_param ("SSSD", $str 1, $str 3, $str 3,$ INT4) tells PHP that those variables should be replaced by question marks. The first parameter is a formatted string followed by the variable that will be replaced.
3, call $stmt->execute () or Mysqli_stmt_execute () function, will actually run this query statement
For a Select Type query, you can use the $stmt->bind_result () or Mysqli_stmt_bind_result () function to provide a list of variables that you want to populate the result column, and then each time you call $stmt->fetch () or the Mysqli_stmt_fetch () function, the value of the next row in the result set is populated into these binding variables.
Other interfaces that use PHP to interact with the database
PHP supports functions that connect to many different databases, including Oracle, Microsoft SQL server, and PostgreSQL. In general, the rationale for connecting and querying these repositories is the same, and individual function names may differ. If you want to use a special database that is not supported by PHP, you can use regular ODBC functions.
ODBC represents an open database connection, which is the standard for connecting to a database. ODBC has only the first function of any set of functions, and if it is required to be compatible with all databases, you cannot use any special features of the database.
In addition to the library of functions included with PHP, some of the available database abstract classes, such as MDB2, allow the use of the same function name for different database types. However, you should install the abstraction layer in advance, such as installing the Pear MDB2 Abstraction layer.
http://www.bkjia.com/PHPjc/320676.html www.bkjia.com true http://www.bkjia.com/PHPjc/320676.html techarticle querying databases from the Web: how the Web database schema works a user's browser issues an HTTP request requesting a specific Web page where the form form is submitted to the PHP script ...
