MySQL Multi Query Function: An alternative to mysqli’s multi_query()

Performing multiple queries at once can be an effective and fast process using less code without any extra calls to the database. mysqli::multi_query can help you do that by executing one or multiple queries which are concatenated by a semicolon. However, not all servers have mysqli installed and when you also sell a product that requires a database import during the installation wizard you want to make sure that even the servers without mysqli will make the import successfully.

After I did some research I found a function that met my expectations and I’d like to share it with you:

function multiQuery($queryBlock, $delimiter = ';') {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = explode ( "\n", $queryBlock );
	$delimiterLen = strlen ( $delimiter );
	do {
		$sqlRow = current ( $sqlRows ) . "\n";
		$sqlRowLen = strlen ( $sqlRow );
		for ( $i = 0; $i < $sqlRowLen; $i   ) {
			if ( ( substr ( ltrim ( $sqlRow ), $i, 2 ) === '--' || substr ( ltrim ( $sqlRow ), $i, 1 ) === '#' ) && !$inString ) {
				break;
			}
			$znak = substr ( $sqlRow, $i, 1 );
			if ( $znak === '\'' || $znak === '"' ) {
				if ( $inString ) {
					if ( !$escChar && $znak === $stringChar ) {
						$inString = false;
					}
				}
				else {
					$stringChar = $znak;
					$inString = true;
				}
			}
			if ( $znak === '\\' && substr ( $sqlRow, $i - 1, 2 ) !== '\\\\' ) {
				$escChar = !$escChar;
			}
			else {
				$escChar = false;
			}
			if ( substr ( $sqlRow, $i, $delimiterLen ) === $delimiter ) {
				if ( !$inString ) {
					$sql = trim ( $sql );
					$delimiterMatch = array();
					if ( preg_match ( '/^DELIMITER[[:space:]]*([^[:space:]] )$/i', $sql, $delimiterMatch ) ) {
						$delimiter = $delimiterMatch [1];
						$delimiterLen = strlen ( $delimiter );
					}
					else {
						$queryLine [] = $sql;
					}
					$sql = '';
					continue;
				}
			}
			$sql .= $znak;
		}
	} while ( next( $sqlRows ) !== false );

	return $queryLine;
}

What does it do?

It splits the SQL block code into multiple queries that are later executed using mysql_query(). For example you have the following queries:

SELECT CURRENT_USER();
SELECT Name FROM City ORDER BY ID LIMIT 20, 5;

The function will create an array with 2 values, each containing the query that should be executed. Example:

$multiple_queries = 'SELECT CURRENT_USER();
SELECT Name FROM City ORDER BY ID LIMIT 20, 5;';

$sql_queries = multiQuery($multiple_queries);

foreach($sql_queries as $sql_query) {
    // Execute each query
    mysql_query($sql_query) or exit('SQL: '.$sql_query.', Error: '.mysql_error());
}

Note: If you are planning to import large block of SQL code in your projects I suggest you to setup mysqli on your server and use mysqli::multi_query as it is much faster. If you have a commercial app and you need to make sure you won’t get complaints from customers that can’t run a script because they don’t have mysqli installed, you can use the alternative solution.

Source: http://www.php4every1.com/tutorials/multi-query-function/