PHP Calling Multiple MySQL Stored Procedures

This is a script I created to call MySQL Stored Procedures, detect any errors and warnings, and email its status. 

It starts with the $mysqlOptions array to setup your database connection. Just fill it in with your database connection options.

The ProcessStoredProcedure function is called with the name of your database and each table, plus any paramenters. Just call it and pass those values, and the function will output any errors and/or warnings it might create.

Once finished, a status email is sent out, with the start and end time of each procedure call.

And finally, if there were any errors and/or warnings, another email is sent out with those errors and warnings listed.

Here's the code with comments:

        
// MySQL Connection Parameters
$mysqlOptions = array();
$mysqlOptions["socket"] = "[YourSocketDirectoryFilename]";
$mysqlOptions["port"] = [YourPort];
$mysqlOptions["host"] = "[YourHostName]";
$mysqlOptions["db"] = "[YourdefaultDB]";
$mysqlOptions["user"] = "[YourUsername]";
$mysqlOptions["password"] = "[YourPassword]";
 
	$error_messages = "";
	$procedures_run = "";
 
     // Create connection to MySQL    
     $mysqli = new mysqli($mysqlOptions["host"], $mysqlOptions["user"], $mysqlOptions["password"], $mysqlOptions["db"], $mysqlOptions["port"], $mysqlOptions["socket"]);
 
	// Pass the database, table name and any parameters
	function ProcessStoredProcedure($db, $name, $params='')
	{
 		// $procedures_run is just a list of the procedure $name's run, to include in a status email
 		// Access the mysql connection string created before calling the procedure
 		// If $error_messages is populated, it is used later on in an email to the admin		 
 		global $procedures_run, $mysqli, $error_messages;
 
 		// Output date and time this procedure started
 		printf("$name: " . date("F j, Y, g:i a") . "\n");
 
 		// Variable appending each procedure run, to use in a status email
 		$procedures_run = $procedures_run . "<br>" . "$name Start: " . date("F j, Y, g:i a");
 
 		// Running our stored procedure, plus any optional parameters
 		$mysqli->multi_query("call $db.$name($params);");
 
 		// Loops through the results of one or more queries run within the stored procedure
 		do {
 			$mysqli->use_result();
 			echo "Okay\n";
 		} while ($mysqli->more_results() && $mysqli->next_result());
 		
 		// Output any errors
 		if ($mysqli->errno) {
 			echo "Stopped while retrieving result : ".$mysqli->error;
 		}
 		
 		// Output any errors from each queries results
 		while ($mysqli->more_results() && $mysqli->next_result());
 		printf("Errors:" . $mysqli->error . "\n");
 		if ($mysqli->error <> "") $error_messages = $error_messages . "$name " . $mysqli->error . "<br>";
 		
 		// Also output any warnings
 		if ($mysqli->warning_count)
 		{
 			printf($mysqli->warning_count . " Warnings Found\n");
 			$error_messages = $error_messages . "$name Warnings" . $mysqli->warning_count . "<br>";
 		}
 		else
 		{
 			printf("No Warnings Found\n");
 		}
 
 		// The end and start time will tell us how long each procedure took to run
 		printf("End: " . date("F j, Y, g:i a") . "\n");
 		print("\n");
	}
 
	// Call each MySQL procedure, by passing the database, tablename and any procedure parameters. (Don't include parenthases in procedure name)
	ProcessStoredProcedure("[yourDB]","[YourProcedureName]");
	//ProcessStoredProcedure("[yourDB]","[YourProcedureName]","NULL"); // An optional comma separated list of values in the 3rd argument.
	//ProcessStoredProcedure("[yourDB]","[YourProcedureName]");
 	
 
	$mysqli->close();
 
// Status email
	$mail_to = "your@email.com";
	$mail_subject = "Your Title: " . gethostname();
	$mail_headers = 'From: your@email.com' . "\r\n" .
					'X-Mailer: PHP/' . phpversion();
	$mail_headers .= 'MIME-Version: 1.0' . "\r\n";
	$mail_headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
 
	$mail_message = "<H2>Your Procedures Have Run</H2><br>";
	$mail_message .= $procedures_run;
 
	mail($mail_to, $mail_subject, $mail_message, $mail_headers);
 
 
// Email any errors	
if ($error_messages <> "")
{
 	printf("Mail Errors\n");
 
 	$mail_to = "your@email.com";
 	$mail_subject = "Your Procedures Errors";
 	$mail_headers = 'From: your@email.com.com' . "\r\n" .
 					'X-Mailer: PHP/' . phpversion();
 	$mail_headers .= 'MIME-Version: 1.0' . "\r\n";
 	$mail_headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
 
 	$mail_message = "<H2>Your Procedures Errors</H2><br>";
 	$mail_message .= $error_messages;
 	
 	mail($mail_to, $mail_subject, $mail_message, $mail_headers);
}

Comments

New Comment