Mysqli_stmt class: Using Preprocessing statements to process select query results

The SELECT statement differs from the other SQL query commands, and it needs to process the query results. The execution of the SQL statement also requires the Execute () method in the Mysqli_stmt object, but unlike the query () method in the Mysqli object, the return value of the Execute () method is not a Mysqli_result object. The Mysqli_stmt object provides a more sophisticated way to handle the query results of a SELECT statement by using the Bind_result () method in the Mysqli_stmt object after executing the SQL statement completion query using the Execute () method. Bind each data column of the query result to some PHP variables, and then use the Fetch () method in the Mysqli_stmt object to read the next result record into these variables. If the next record is read successfully, the fetch () method returns True, otherwise false is returned, or false is returned if all result records have been read.

By default, the Select query results are left on the MySQL server, waiting for the fetch () method to fetch the records back to the PHP program and assign them to PHP variables that are bound using the Bind_result () method. If you need to process all the records, not just a small portion, you can call the Store_result () method in the Mysqli_stmt object to pass all the results back to the PHP program at once. This is not only more efficient, but also can reduce the burden on the server. The Store_result () method is optional, except that reading the data does not change anything. In the case of contact information table ContactInfo, the code that uses the preprocessing statement to process the results of a select query is as follows:

<?PHP$mysqli=NewMysqli ("localhost", "Mysql_user", "Mysql_pwd", "demo");//connect to MySQL databaseif(Mysqli_connect_errno()){    //Check for connection errors    printf("Connection failed:%s<br>",Mysqli_connect_error()); Exit();} $query= "SELECT ' Name ', ' address ', ' phone ' from ' contactinfo ' LIMIT 0,3";//declaring a SELECT statementif($stmt=$mysqli->prepare ($query)){    //Handling SQL commands that you intend to execute    $stmt-execute (); //Execute SQL statement    $stmt-Store_result (); //Retrieve all query results    Echo"Number of records:".$stmt->num_rows. " Line <br/> "; //number of records for output query    $stmt->bind_result ($name,$address,$phone); //when the result of the query is bound to a variable     while($stmt-Fetch ()) {        //fetch data from MySQL service article by clause        printf("%s (%s,%s) <br/>",$name,$address,$phone);//formatted result output    }     $stmt-Close (); //releasing resources occupied by Mysqli_stmt objects} $mysqli-close ();//Close the connection to the MySQL database?>

The output results are as follows:

1. Number of records: 3 rows
2. Gao xx (Haidian District, 15801688338)
3. Luo xxx (Chaoyang District, 15801681234)
4. Feng An (Dongcheng District, 15801689876)

If you get how many records the SELECT statement finds, you can retrieve it from the Num_rows property in the Mysqli_stmt object. However, this property can only be used if the Store_result () method is executed in advance and the results of all queries are passed back to the PHP program.

You can also use the Bind_param () and Bind_result () methods in the Mysqli_stmt object if you also use a placeholder symbol (?) in a SELECT statement and need to execute the statement multiple times. The code looks like this:

<?PHP$mysqli=NewMysqli ("localhost", "Mysql_user", "Mysql_pwd", "demo");//connect to MySQL databaseif(Mysqli_connect_errno()){    //Check for connection errors    printf("Connection failed:%s<br>",Mysqli_connect_error()); Exit();} //declare a SELECT statement, find by department number, use a placeholder symbol (?) to indicate the department you will be looking for$query= "SELECT ' Name ', ' address ', ' phone ' from ' contactinfo ' WHERE ' departmentid ' =? LIMIT 0,3 ";if($stmt=$mysqli->prepare ($query)){    //Handling SQL commands that you intend to execute    $stmt->bind_param (' s ',$departmentId); //Binding Parameter Department number    $departmentId= "D01"; //Assigning a value to a bound variable    $stmt-execute (); //Execute SQL statement    $stmt-Store_result (); //Retrieve all query results    $stmt->bind_result ($name,$address,$phone); //when the result of the query is bound to a variable    EchoThe list of contact information for the D01 department is as follows: <br/> "; //Print tip Information     while($stmt-Fetch ()) {        //fetch data from MySQL service article by clause        printf("%s (%s,%s) <br/>",$name,$address,$phone);//formatted result output    }     EchoThe list of contact information for the D02 department is as follows: <br/> "; //Print tip Information    $departmentId= "D02"; //Assigning a new value to a bound variable    $stmt-execute (); //Execute SQL statement    $stmt-Store_result (); //Retrieve all query results     while($stmt-Fetch ()) {        //fetch data from MySQL service article by clause        printf("%s (%s,%s) <br/>",$name,$address,$phone);//formatted result output    }     $stmt-Close (); //releasing resources occupied by Mysqli_stmt objects} $mysqli-close ();//Close the connection to the MySQL database?>

The output results are as follows:

1. A list of contact information for the D01 department is as follows:
2. Gao xx (Haidian District, 15801688338)
3. Chen xxx (Changping District, 15801682468)
4. Bai xx (Haidian District, 15801689675)
5. A list of contact information for the D02 department is as follows:
6. Luo xxx (Chaoyang District, 15801681234)

In the above example, the contact information for two departments is removed from the database, depending on the department parameters provided. As long as you bind the result using the Bind_result () method, you do not need to bind each data column of the query result to some PHP variables each time.

When generating a Web page, many PHP scripts usually execute a query that is exactly the same, except for the parameters, for which a query is repeated, and each iteration uses a different parameter condition, and MySQL starts with the 4.1 version of a named preprocessing statement (prepared statement) mechanism. It can send the entire command to the MySQL server once, and later only the parameters change, the MySQL server only need to do an analysis of the structure of the command is enough. This not only greatly reduces the amount of data that needs to be transferred, but also improves the processing efficiency of the command. You can define and execute parameterized SQL commands with the Mysqli_stmt class object provided in the mysqli extension schema, and all member properties and member methods contained in the Mysqli_result class are shown in table 13-6 and table 13-7.

Table 13-6 member methods in the Mysqli_stmt class (12 total)

Member Method name	Description
Bind_param ()	This method binds the parameters of the preprocessing statements to some PHP variables, and notes the order of the parameters.
Bind_result ()	After the preprocessing statement executes the query, it uses the method to bind the variable to the obtained field
Close ()	Once a preprocessing statement uses the result, the resources it consumes can be recycled by this method
Data_seek ()	To move the internal result pointer in a preprocessing statement
Execute ()	Execute prepared pre-processing statements
Fetch ()	Gets each record of the preprocessed statement result and assigns the corresponding field to the bound result
Free_result ()	Reclaims memory consumed by statements specified by this object
Result_metadata ()	Returning result set original data from preprocessing
Prepare ()	Whether it is a binding parameter or a binding result, you need to prepare the preprocessing statement to be executed using this method
Send_long_data ()	Send data block
Reset ()	To reset a preprocessing statement
Store_result ()	Getting a result set from a preprocessing statement

Table 13-7 member properties in the Mysqli_stmt class (6 total)

member Property name	Description
$affected _rows	Returns the number of records affected by the last statement specified by the object. Note that this method is only relevant for inserting, modifying, and deleting three query sentences
$errno	Returns the error code for the object that specifies the most recently executed statement
$error	Returns the error description string for the object that specifies the most recently executed statement
$param _count	Returns the number of arguments that need to be bound in a given preprocessing statement
$sqlstate	Returning a SQL status error code from a previously preprocessed statement
$num _rows	Returns the number of records obtained by the SELECT statement specified by the Stmt object

Mysqli_stmt class: Using Preprocessing statements to process select query results