CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
whoisit
whoisit

Reputation: 269

fetching the data when using prepared statements with mysqli

This is the standard code I found online that etching the data when using prepared statements with mysqli:

$sql = "SELECT Title, CopyrightYear FROM Books WHERE ID=?";

if ($statement = mysqli_prepare($connection, $sql)) {

    mysqli_stmt_bindm($statement, 'i', $id);
    mysqli_stmt_execute($statement); //Question 1 
    mysqli_stmt_bind_result($statement, $title, $year);

    while (mysqli_stmt_fetch($statement)) {  //Question 2 
        echo $title . '-' . $year . '<br/>';
    }
}

I have some questions:

  1. why we don't store the result of mysqli_stmt_execute? we can use something like:

$result = mysqli_stmt_execute($statement); then we can retrieve each record from the result set?

  1. Since we have already executed the query, what does mysqli_stmt_fetch($statement) mean? don't we have executed the $statement already, shouldn't it be like this : mysqli_stmt_fetch($result)?

Upvotes: 0

Views: 1468

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 563021

The execute function doesn't return a result, but mysqli_stmt_get_result() does. Then you can fetch one row at a time from the result resource. This usage might make more sense to you:

if ($statement = mysqli_prepare($connection, $sql)) {

    mysqli_stmt_bind_param($statement, 'i', $id);
    mysqli_stmt_execute($statement);
    $result = mysqli_stmt_get_result($statement);

    while ($row = mysqli_fetch_assoc($result)) {
        echo $row['title'] . '-' . $row['year'] . '<br/>';
    }
}

One reason no result set is not returned from the execute function is that some SQL statements (like INSERT, UPDATE, DELETE, CREATE, DROP, etc.) have no result set. So the execute function only returns a boolean to indicate whether the statement ran successfully or not.

Upvotes: 2

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

why we don't store the result of mysqli_stmt_execute? we can use something like: $result = mysqli_stmt_execute($statement); then we can retrieve each record from the result set?

That's because mysqli_stmt_execute() function only executes a query that has been prepared using the mysqli_prepare() function. When executed any parameter markers which exist will automatically be replaced with the appropriate data. It doesn't return any result set, rather it just returns the status of the execute query i.e. either TRUE or FALSE.

Since we have already executed the query, what does mysqli_stmt_fetch($statement) mean? don't we have executed the $statement already, shouldn't it be like this : mysqli_stmt_fetch($result)?

Again, since mysqli_stmt_fetch() function just executes the prepared statement and doesn't return any result set, you need to bind the variables to the prepared statement for storing the result. And from the doc,

When mysqli_stmt_fetch() is called to fetch data, the MySQL client/server protocol places the data for the bound columns into the specified variables var1, ....

Upvotes: 1

Related Questions