_php skills of PHP and MySQL articles from Web query database

Querying a database from a Web: How the Web Database schema works
A user's browser sends an HTTP request, requesting a specific Web page where the start form is submitted to a PHP script file (e.g., results.php) for processing
After the Web server receives a request for a results.php page, retrieves the file and passes it to the PHP engine for processing
The PHP engine begins parsing the script. Scripts mainly include 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 finishes running the script, it returns 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
Check and filter data from the user first, we will filter the blank characters that the user may accidentally enter at the beginning or end of their search condition, which is implemented using the function trim (). We're in so much trouble. The reason the user enters data is to prevent multiple interfaces from connecting to the database because the user enters from a different interface, which can cause security problems.
Then, when you are ready to use any data entered by the user, you also filter some control characters appropriately, and when the user enters data into the database, you must escape the data, at which point the stolen function has the addslashes () function, the stripslashes () function, and the Get_magic_qutoes _GPC () function. The Addslashes () function adds a backslash before some characters for the needs of a database query, and the Stripslashes () function removes the backslash character from the string; the GET_MAGIC_QUTOES_GPC () function tricks Add the escape character "\". Gets the current active configuration Magic_quotes_runtime setting, returns 0 if the magic quote is turned off at run time, or returns 1. We can also use Htmispecialchars () to alert code for special meaning characters in HTML, and the Htmispecialchars () function converts some predefined characters to HTML entities "predefined characters are:& (and numbers) become &" (double quotes) become "' (single quotes) become ' < (less than) become < > (greater than) become >"
Establish a connection to the appropriate database PHP provides a function library mysqli (i) 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 '); Returns a resource that represents the connection to the database and, if the process method is used, This resource must be passed to all other functions of the mysqli. This is very similar to the handler function
Most of the functions of mysqli have object-oriented interfaces and process interfaces, the difference being that the procedure version of the function name begins with Mysqli_ and requires the resource handle that is obtained by the incoming Mysqli_connect () function. For this rule, the data connection is an exception because it is created by the constructor of the Mysqli object. So when you try to connect, you need to check that the Mysqli_connect_errno () function returns an error number when a connection error occurs, and returns 0 if successful.

Please note:
When connecting to the database is, usually the meeting error suppressor @ as the first containing code. This can be handled with any error skillfully, or through exceptions. In addition, MYSQK has certain limitations on the number of connections to the database at the same time. The mysqli parameter max_connections determines the number of simultaneous connections, and this parameter, and the associated Apache parameter maxclients, tells the server to reject new connection requests, thus ensuring that system resources are not requested or used when the system is busy or 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 to use: Using the use dbname command on the MySQL command line; $db->select_db (dbname) or mysqli_select_db (db_resource,dbname) in PHP.
To query a 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 Returns a result object, and the procedure version returns a result resource. Whichever method saves the result in the $result variable is used later in the work. Returns False if the function fails to run.
Get the results of the query use different functions to remove the query results from the result object or identifier in different ways, and the resulting object or identifier is the key to accessing the rows returned by the query.
Usually we have to get the number of rows in the result set and use the MYSQLI_FETCH_ASSOC () function.
Returns the number of rows: $num _results = $result->num_rows; (the number of rows is saved in the object's num_rows member variable) or $num_results = Mysqli_num_rows ($result);
Then loop through each row, call $row = $result->fectch_assoc () in the loop, or $row = Mysqli_fetch_assoc ($result), and return the information for that row. If the object returns rows, each keyword is a property name, each value is the corresponding value in the property, and the array is returned if returned as a resource.
There are other methods for obtaining results from the result identifier, for example: using $row = $result->fecth_row ($result), or $row = Mysqli_fetch_row ($result), and retrieving the result into an enumerated array Or you can use $row = $result->fecth_object (), or $row = Mysqli_fecth_object ($result); The river goes back to an object.
Release the result set first from the database: $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 because they will be automatically closed when the script finishes.

Querying a database from the Web: using the prepared statement

The MYSQLI function library supports the use of prepared statements. They can improve speed when executing a large number of identical queries with different data, or be protected from SQL injection style (injection-stytle--attacks).
The basic idea of the prepared statement is that you can send a query template that needs to be executed to MySQL, and then send the data separately. We can send a lot of the same data to the same prepared statement, which is very useful for the insert operation of the batch processing.

We typically use a few steps:
1, the construction template. Inserted as example: $query = "INSERT into user values (?,?,?,?)";

2, use the prepared statement, build a statement object or need to complete the actual processing of resources. $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 argument 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 select type queries, 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 call $stmt->fetch each time () or the Mysqli_stmt_fetch () function, the values in the next row of the result set are 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. Typically, the rationale for connecting and querying these rushing libraries is the same, and individual function names may be different. If you want to use a special database that is not supported by PHP, you can use a regular ODBC function.
ODBC represents an open database connection, and he is the standard for connecting to a database. ODBC has only the priority function of any set of functions, and you cannot use the special features of any database if you require all databases to be compatible.
In addition to the libraries that are included with PHP, some database abstract classes, such as MDB2, allow you to use the same function name for different database types. However, to install the abstraction layer in advance, such as installing the Pear MDB2 Abstraction layer.