php query stored procedure

In PHP, sometimes it is necessary to call stored procedures in the database to complete specific operations. In this case, you need to use PHP's database extension integration to interact with the database. This article will introduce how to query stored procedures in PHP.

Step 1: Connect to the database

First you need to make sure that you have correctly connected to the database. Connecting to the database can be done using the mysqli extension in PHP. After the connection is successful, you need to select a specific database. This can be achieved by calling the mysqli_select_db() function. The following is an example:

$username = "yourusername";
$password = "yourpassword";
$hostname = "localhost";
$database = "yourdatabase";

$connection = mysqli_connect($hostname, $username, $password);
mysqli_select_db($connection, $database);

In the above code, the $username and $password variables store the username and password of the database respectively. The $hostname variable stores the hostname of the database. The $database variable stores the name of the database. Use the mysqli_connect() function when connecting to the database and store the result in the $connection variable. Then use the mysqli_select_db() function to select the database.

Step 2: Query the stored procedure

When querying the stored procedure in PHP, you need to use the mysqli_prepare() function to prepare the query and store the result in a variable. Use the CALL command in the query statement to call the stored procedure. For example:

$userid = 1;
$stmt = mysqli_prepare($connection, "CALL get_user_by_id(?)");
mysqli_stmt_bind_param($stmt, "i", $userid);
mysqli_stmt_execute($stmt);

In the above sample code, the $stmt variable stores the results of the query. The mysqli_prepare function is used to prepare query statements, where? is a placeholder representing the parameter value specified later. The actual parameter value can be specified through the mysqli_stmt_bind_param() function. The first parameter "i" is a format specifier, indicating an integer type, and the subsequent parameter $userid is the parameter value to be passed. Finally, use the mysqli_stmt_execute() function to execute the query.

Step 3: Obtain the query results

To obtain the query results of the stored procedure, you need to use the mysqli_stmt_store_result() function to save the results in the cache area. Then use the mysqli_stmt_bind_result() function to bind the result into a variable for use in your code. The following is an example:

mysqli_stmt_store_result($stmt);
mysqli_stmt_bind_result($stmt, $col1, $col2, $col3);

while (mysqli_stmt_fetch($stmt)) {
    printf("%s %s %s
", $col1, $col2, $col3);
}

mysqli_stmt_close($stmt);
mysqli_close($connection);

In the above sample code, the mysqli_stmt_store_result() function is used to save the result in the cache area. Then use the mysqli_stmt_bind_result() function to bind the results to variables such as $col1, $col2, $col3, etc. for use in the loop. Use the mysqli_stmt_fetch() function in a loop to traverse the result set and output the query results. Finally, use the mysqli_stmt_close() function to close the query statement and use the mysqli_close() function to close the database connection.

Summary

Querying stored procedures is a way to implement specific database operations in PHP. When making a query, you first need to make sure you are connected to the database. Then, use the mysqli_prepare() function to prepare the query and the mysqli_stmt_bind_param() function to specify the parameters. Use the mysqli_stmt_execute() function to execute the query. Then, use the mysqli_stmt_store_result() function to save the result in the buffer area, and use the mysqli_stmt_bind_result() function to bind the result to a variable. Finally, use a while loop to traverse the result set and use the mysqli_stmt_fetch() function to output the query results. After completing the query, you need to use the mysqli_stmt_close() function to close the query statement and the mysqli_close() function to close the database connection.

The above is the detailed content of php query stored procedure. For more information, please follow other related articles on the PHP Chinese website!