1

If I do not close the stmt at the end of this code snippet:

    $stmt = $mysqli->prepare("SELECT typeId FROM types WHERE name = 'author'");
    $stmt->execute();
    $stmt->bind_result($typeId);
    $stmt->fetch();

(with this:)

    $stmt->close();

Then the following prepared statement fails with error code 0 and no error:

    $stmt = $mysqli->prepare("INSERT INTO typeTracker (reserved_id, typeId) VALUES (NULL, ?)");
    $stmt->bind_param("i", $typeId);

Why? Is it because I called the bind_result and fetch functions in the first block of code? I don't typically have to close statements before preparing new ones.

Thanks.

randy
  • 156
  • 9

1 Answers1

1

If $stmt gets replaced or falls out of scope it gets destroyed. You can close it as a formality, but normally it gets taken care of anyway.

Calling close() frees up any associated resources immediately. If you don't do that you'll need to wait for the garbage collector to take care of it.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • The two blocks of code were literally right as you see them, though. I don't see how `$stmt` could have fallen out of scope or gotten destroyed? Thanks for the answer, though! – randy Jan 22 '19 at 20:11
  • If that script finishes, it goes out of scope. If that variable gets replaced with another statement it goes out of scope. In both cases it gets automatically destroyed. – tadman Jan 22 '19 at 20:23
  • Both blocks of code were within the same script. One right above the other, as shown in the example. And the `$stmt` variable was never replaced with another statement. – randy Jan 22 '19 at 21:34
  • Basically, if I don't explicitly close the statement variable before reusing it to prepare the second prepared statement below... I get an error saying bind_param cannot work with a boolean (aka `$stmt` = false) – randy Jan 22 '19 at 21:40
  • When you say `$stmt = $mysqli->prepare(...)` the second time you're not "reusing it", you're assigning a brand new value to `$stmt` and the old is thrown into the garbage. If you're getting an error that's a sign you should [enable exceptions](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) to make tracking down errors easier. It's possible that the MySQL connection is in the middle of something and `close()` flushes that operation, freeing up the connection for use. – tadman Jan 22 '19 at 21:53
  • Each connection has an internal state and can only do one thing at a time. If you don't completely fetch all the results it's stuck in "deliver results" mode and can't prepare queries. `fetch_all()` can clear that up by grabbing everything at once. Normally it's counter-productive to `fetch()` individual entries *unless* you're fetching more data than can fit in memory and need to stream results. – tadman Jan 22 '19 at 21:55