Close statement object before starting a new with prepared statements

Question

I'm quite new at using prepared statements and is wondering if I should close the stmt_init() after each call or could I just keep it open?

$stmt = $mysqli->stmt_init();

if($stmt->prepare("SELECT player_draws, player_turn, player_passes, swapped FROM ".$prefix."_gameplayer WHERE fk_game_id = ? AND fk_player_id = ?")){

    $stmt->bind_param('ii', $currgame, $playerid);
    $stmt->execute();
    $stmt->bind_result($udraws, $uturn, $upass, $uswaps);
    $stmt->fetch();

    echo $udraws.'-'.$uturn.'-'.$upass.'-'.$uswaps.'<br>';

    // Close statement object
    $stmt->close(); 

}

$stmt = $mysqli->stmt_init();

if($stmt->prepare("SELECT player_draws, player_turn, player_passes, swapped FROM ".$prefix."_gameplayer WHERE fk_game_id = ? AND fk_player_id != ?")){

    $stmt->bind_param('ii', $currgame, $playerid);
    $stmt->execute();
    $stmt->bind_result($odraws, $oturn, $opass, $oswaps);
    $stmt->fetch();

    echo $odraws.'-'.$oturn.'-'.$opass.'-'.$oswaps.'<br>';

    // Close statement object
    $stmt->close(); 

}

Is one of them better for the database considering calls?

Thanks in advance!

Manatok · Accepted Answer · 2013-01-17T08:04:21.940

1

...A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency...

EDIT Since the queries are different each of them will need to be prepared separately but you should be able to reuse the $mysqli->stmt_init();

On a side note someone mentions this in the comments:

*if you are repeating an statement in an loop using bind_param and so on inside it for a larger operation. i thougt id would be good to clean it with stmt->close. but it broke always with an error after aprox. 250 operations . As i tried it with stmt->reset it worked for me.*

edited Jan 17 '13 at 08:04

answered Jan 17 '13 at 07:51

Manatok

5,506
3
23
32

But in the first one I want to get data from WHERE fk_player_id = ? and in the second I want WHERE fk_player_id != ?. How can I put this in the same? – Mansa Jan 17 '13 at 07:57
Sorry only saw that now, thought they were the same. You should be able to use the same $mysqli->stmt_init(); for both but you will need to re-prepare the second query. – Manatok Jan 17 '13 at 08:00
Thanks :-) You have been at great help! – Mansa Jan 17 '13 at 08:03

Close statement object before starting a new with prepared statements

1 Answers1