PHP Calling Multiple MySQL Stored Procedures

Posted Oct 08, 2014

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);
}

Discussions

New Comment














Latest Discussions

  • Carlos: Thank you!
    May 16, 2014 | Replies (0)
  • Jessica: Great info, thanks!
    May 15, 2014 | Replies (1)
  • Walter: Thanks, you totally saved me a bunch of searching around just now. I was setting up a percona xtradb...
    Feb 25, 2014 | Replies (0)
  • Avi: I've found the reason of the problem in my case. It's a bug as described here :http://bugs.mysql.com/bug.php?id=68892Shortly:...
    Dec 12, 2013 | Replies (0)
  • Avi: I've also met that behavior twice on 5.6.12 slaves. However, i didn't find the root cause of that....
    Dec 12, 2013 | Replies (0)