Jump to content


Photo

closing prepared statements necessary?

php prepared statements mysql

  • Please log in to reply
5 replies to this topic

#1 MFA

MFA

    Member

  • Members
  • PipPip
  • 29 posts

Posted 10 March 2013 - 02:37 PM

I've just completed my first prepared statement, converted to using them for security reasons. I've tested it and it works however I am looking for feedback (constructive criticism welcomed) regarding the code itself although I understand it 's fairly basic. Here's teh code:

 

<?php
//prepared statement example
include 'database.php';
$query = "SELECT ID FROM users WHERE email = ?";
$email = 'myemail@gmail.com';
$statement = mysqli_stmt_init($connect);

mysqli_stmt_prepare($statement, $query);
mysqli_stmt_bind_param($statement, 's', $email);
mysqli_stmt_execute($statement);
mysqli_stmt_bind_result($statement, $id);
mysqli_stmt_fetch($statement);

echo $id;
?>

 

Also, is using mysqli_stmt_close necessary? Am I correct in saying that without using this function I will not be able to create another prepared statement within that script? - because I have tried the latter and it wouldn't work unless I did close the statement.

 



#2 gizmola

gizmola

    Advanced Member

  • Administrators
  • 3,968 posts
  • LocationLos Angeles, CA USA

Posted 10 March 2013 - 03:39 PM

No it's not necessary because everything will be garbage collected at the end of the script.

#3 MFA

MFA

    Member

  • Members
  • PipPip
  • 29 posts

Posted 10 March 2013 - 05:21 PM

No it's not necessary because everything will be garbage collected at the end of the script.


Okay, but how come I can't use another prepared statement unless I use mysqli_stmt_close($etc); after the first one?



#4 jcbones

jcbones

    Advanced Member

  • Gurus
  • 2,439 posts
  • LocationNorth Carolina

Posted 10 March 2013 - 06:07 PM

Are you getting "out of sync" errors?

IF so, you need to do one of 3 things:

1. store the result:

2. finish using the results before you make another database call:

3. close the result:



#5 MFA

MFA

    Member

  • Members
  • PipPip
  • 29 posts

Posted 10 March 2013 - 07:52 PM

I'm not sure what "out of sync" errors are. I don't get error messages if that's what you mean.This is my code at the moment.

<?php

//prepared statement example

include 'database.php';

$query = "SELECT ID FROM users WHERE email = ?";

$email = 'example@googlemail.com';

$statement = mysqli_stmt_init($condbmembers);

mysqli_stmt_prepare($statement, $query);

mysqli_stmt_bind_param($statement, 's', $email);

mysqli_stmt_execute($statement);

mysqli_stmt_bind_result($statement, $id);

mysqli_stmt_fetch($statement);

echo $id . "</br>"; // THIS WORKS!!


$querytwo = "SELECT fname FROM users WHERE ID = ?";

$uid = '4';


$statementone = mysqli_stmt_init($condbmembers);

mysqli_stmt_prepare($statementone, $querytwo);

mysqli_stmt_bind_param($statementone, 'i', $uid);

mysqli_stmt_execute($statementone);

mysqli_stmt_bind_result($statementone, $fname);

mysqli_stmt_fetch($statementone);

echo $fname; //THIS DOESN'T UNLESS I INSERT mysqli_stmt_close($statement); FOLLOWING echo $id . "</br>";



?>



#6 gizmola

gizmola

    Advanced Member

  • Administrators
  • 3,968 posts
  • LocationLos Angeles, CA USA

Posted 08 April 2013 - 02:03 PM

Yes, if you need to run multiple statements in a script you have to close each one in turn, before you can run the next one.

To quote the manual:

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com