PHP Prepare method not working when called twice?

Question

I use the preparation method as follows:

$db= new mysqli("localhost","***","***","***"); if ($db->connect_error) { die('Connection Error'); } $id = 1; if($stmt = $db->prepare('SELECT name FROM table WHERE id = ? ')) { $stmt->bind_param('i', $id); $stmt->execute(); // $stmt->close(); echo "Success<br>"; } else { echo "Something broke :/<br>"; } $id =2; if($stmt = $db->prepare('SELECT name FROM table WHERE id = ? ')) { $stmt->bind_param('i', $id); $stmt->execute(); echo "Success<br>"; } else { echo "Something broke :/<br>"; $error = $db->errno . ' ' . $db->error; echo $error; } 

If I execute the script, I get

Success

Something broke:/

0

How can I find out why the preparation method failed after it was called a second time? For some reason, $db->errno returns 0, which indicates that nothing happened. However, the preparation methods fail and return false, so I cannot check $stmt->error;

I accidentally found out that when I delete the first call to $stmt->execute() , the preparation method call works fine again (on the first and second calls). What am I missing here?

Edit

As Maxim Tkach suggested, if I uncomment

 // $stmt->close(); 

then i get

Success

Success

But why is that? I have never read anywhere that it is very important to close an expression about preparation.

This is from the PHP manual:

Closes a prepared statement. mysqli_stmt_close () also frees the statement. If the current statement is awaiting or not reading the results, this function cancels them, so the next request can be executed.

I don’t see what they say that it is important to close it in order to fulfill the second expression about preparation. Did I miss something?

Answer 1

You need to read the following: Prepared reports

Completed implementation of the application consists of two stages: preparation and execution. At the preparation stage, the application template is sent to the database server. The server performs syntax checking and initializes the server internal resources for later use.

and

Each trained operator takes server resources. Applications should be closed immediately after use. If this is not done explicitly, the statement will be closed when the instruction handle is freed by PHP.

After reading the article, I came to the conclusion: You cannot prepare a request without closing the previous one. Always be prepared to close the request after execution .

And your code should be rewritten as follows:

 $db= new mysqli("localhost","***","***","***"); if ($db->connect_error) { die('Connection Error'); } $id = 1; if($stmt = $db->prepare('SELECT name FROM table WHERE id = ? ')) { $stmt->bind_param('i', $id); $stmt->execute(); echo "Success<br>"; } else { echo "Something broke :/<br>"; } $id = 2; $stmt->bind_param('i', $id); $stmt->execute(); $stmt->close(); 

PS If you add the return value of the bind and execute check, it will be fine

Answer 2

You need to read the error $ stmt->;

To read, you need to rewrite the code, as in the example below.

 ... $stmt = new mysqli_stmt($db); if($stmt->prepare('SELECT name FROM table WHERE id = ? ')) { $stmt->bind_param('i', $id); $stmt->execute(); echo "Success<br>"; } else { var_dump($stmt->error); } ...