Multiple Queries with Mysqli

[Bottyz]

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

[MarPlo]

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.

[mikosiko]

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

 

[Bottyz]

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.