Bottyz Posted October 6, 2011 Share Posted October 6, 2011 Hi all, I would like to perform two queries in a mysqli statement before closing the connection. I'm using prepared statements and I've read on the php.net you can perform a multi-query using mysqli->multi_query. My problem is that I'm only finding useful examples on how to select and fetch results not using prepared statements. I'm a bit lost with how to do this to update two tables. Currently i'm using two seperate connections to do the queries but want to combine these together: Query one (updates their password and resets number of logins to force password change on next login): //reset logins to force the user to change password upon next login $loggedin = '0'; // connect to db for mysqli require('../db/db.php'); // updates password and number of logins $insert_stmt = $mysqli->stmt_init(); if ($insert_stmt->prepare("UPDATE users SET their_password=?, loggedin=? WHERE their_username=?")) { $insert_stmt->bind_param('sss', $their_password, $time, $user_email); $insert_stmt->execute(); $insert_stmt->close(); } // if query errors sends an email if ($mysqli->error) { try { throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno); } catch(Exception $e ) { $mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >"; $mess .= nl2br($e->getTraceAsString()); $contact_email = "webmaster@website.com"; $message_sub = "Mysqli Forgotten Password Query Error [uPASSWORD01]"; $hdrs = "From: " . $contact_email . "\r\n"; $hdrs .= "Reply-To: ". $contact_email . "\r\n"; $hdrs .= "MIME-Version: 1.0\r\n"; $hdrs .= "Content-Type: text/html; charset=UTF-8\r\n"; mail($contact_email, $message_sub, $mess, $hdrs); } header("refresh: 10; forgotpass.php"); die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.'); exit(); } $mysqli->close(); Query two (updates a reset log - to keep track on our user password resets): // change status $resetstatus = "Successful Reset"; // connect to db for mysqli require('../db/db.php'); // inserts a new record $null = NULL; $insert_stmt = $mysqli->stmt_init(); if ($insert_stmt->prepare("INSERT INTO passwordresets VALUES (?, ?, ?, ?, ?)")) { $insert_stmt->bind_param('issss', $null, $user_email, $time, $userip, $resetstatus); $insert_stmt->execute(); $insert_stmt->close(); } // if query errors sends an email if ($mysqli->error) { try { throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno); } catch(Exception $e ) { $mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >"; $mess .= nl2br($e->getTraceAsString()); $contact_email = "webmaster@website.com"; $message_sub = "Mysqli Forgotten Password Query Error [uARESETLOG01]"; $hdrs = "From: " . $contact_email . "\r\n"; $hdrs .= "Reply-To: ". $contact_email . "\r\n"; $hdrs .= "MIME-Version: 1.0\r\n"; $hdrs .= "Content-Type: text/html; charset=UTF-8\r\n"; mail($contact_email, $message_sub, $mess, $hdrs); } header("refresh: 10; forgotpass.php"); die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.'); exit(); } $mysqli->close(); Any ideas would be greatly appreciated. Thank you Quote Link to comment Share on other sites More sharing options...
MarPlo Posted October 10, 2011 Share Posted October 10, 2011 Hi, I think if you want to execute different query types (like update, and insert) with prepare(), you need a prepare() for each type, because this method prepares a certain sql statement. Try make two different prepare() at the same connection, than apply execute() to each one. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted October 10, 2011 Share Posted October 10, 2011 multi_query and prepared statements are not compatible.... prepared statement MUST consist of only a single SQL statement.. as stated on the manual http://www.php.net/manual/en/mysqli.prepare.php Quote Link to comment Share on other sites More sharing options...
Bottyz Posted October 10, 2011 Author Share Posted October 10, 2011 multi_query and prepared statements are not compatible.... prepared statement MUST consist of only a single SQL statement.. as stated on the manual http://www.php.net/manual/en/mysqli.prepare.php Hi mikosiko, I figured that one out only a couple of days ago when sifting through the php manual. I forgot to update everyone here. But thanks for the confirmation! I just call each myslqi with a new connection using a pre-set dbconnect file. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.