Jump to content

Multiple Queries with Mysqli


Bottyz

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.