Multi-query function

I needed a function that will take a string or a file and then split it into small SQL queries. I found few functions on Internet but they did not work as i expected. They all had following principle of work:

1. Take a string
2. Use function explode and “;” as a delimiter to create an array
3. Execute each row in array

I believe that already at first glance many of you see that it does not go so easily. The first and obvious problem is incorrectly split. If we have a string which contains “;” it will be divided in 2 parts and this will lead to errors in MySQL. Another problem is the incompatibility with the sql dump files. Suppose you have a procedure in your sql backup. In front of the procedure is the command DELIMITER that tells us that now follows new delimiter. Since none of these functions there does not have part of the code responsible for processing DELIMITER commands, SQL code will break up, and again we’ll get MySQL errors.

So I decided to make my own function. Since I’ve made it, I decided to show you how and which part of code is responsible for what.

So let’s start then.

First, we declare the function. Function will receive two arguments. First is the SQL code that must be processed and the second is a string that represents the delimiter.

function multyQuery ( $queryBlock, $delimiter = ';' ) {

}

• $inString – If we are in string it will be true, by default it is false
• $escChar – If “\”is found it will be true, by default it is false
• $sql – Variable that will contain uncompleted SQL query
• $stringChar – Character with which we entered the string
• $queryLine – Array with pa parsed SQL queries
• $sqlRows – Our code exploded by ‘\n’
• $delimiterLen – Delimiter length

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\n", $queryBlock );
	$delimiterLen = strlen ( $delimiter );
}

Now, when we have our initial variables declared, we can continue. Now we need a loop that will pass through our code that is divided into smaller sections (each new line in the code and a new row in array). To loop we use do…while command.Inside the loop we will declare a variable $sqlRow that will contain the current value of a key in a row on which the loop stopped. Since we used explode and “\n” to devide our string, now we have to return “\n” back in string. Then we declare variable $sqlRowLen that will containt $sqlRow length. That variable we will use in for loop.

This is what we have fro now.

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\n", $queryBlock );
	$delimiterLen = strlen ( $delimiter );

	do {
		sqlRow = current ( $sqlRows ) . "\n";
		$sqlRowLen = strlen ( $sqlRow );

	} while ( next( $sqlRows ) !== false );
}

Now we’ll create our loop. Loop goes from 0 and goes to value of $sqlRowLen (row length). First thing we’ll do in loop is tho check if current character is comment. If it is, we skip it and go to th next character. It ist’s not then we save our character in $znak variable.

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\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 );
		}

	} while ( next( $sqlRows ) !== false );
}

Now we do some parsing. First we check if our character is “ or ‘. It that’s true then we are entering or exiting string. If we are in string,  we check if previous character was “\”  (escape character). If it’s not, and current character equals to character with which we entered the string then we are exiting string. Varijablu $inString mijenjamu u false. If we are not in string then we are entering in string. Variable $inString is true and variable $stringChar is our current character ($znak).

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\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;
				}
			}
		}

	} while ( next( $sqlRows ) !== false );
}

Now we check if our charavter is “\”. If that's true, then next character is escaped. We also check if last character is alse “\”. If that's true then those two characters negate each other and $escChar is false. But if condition is true then $echChar has opposite value then it is now (true is false and false is true).

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\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;
			}
		}

	} while ( next( $sqlRows ) !== false );
}

Now we just have to return our $queryLine that has all SQL queries that where in $queryBlock.

function multyQuery ( $queryBlock, $delimiter = ';' ) {
	$inString = false;
	$escChar = false;
	$sql = '';
	$stringChar = '';
	$queryLine = array();
	$sqlRows = split ( "\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;
}

That would be our final function. She returns array with parsed queries and now you can go throught then and execute each one. Leave a comment if you have problems or you've discovered a bug (I tested it with various MySQL dump files and they where all successuful).

• 21.05.2010 -- Multi-Language Site (6)
  It's been a while since I last posted something because I was very busy. So let's do something usefu...
• 13.04.2009 -- 5 useful functions (1)
  I would like to share with you a couple of functions that use a lot and I consider them quite useful...
• 27.07.2010 -- Upload Images With MySQL (0)
  [tinytoc level="1"]Intro[/tinytoc] Ok, let's continue with tutorial requests. This tutorial will ...
• 07.04.2010 -- Edit XML (15)
  I had an idea about creating tutorial that will cover manipulating XML in PHP. The idea was to creat...
• 02.01.2010 -- Enable E-mail In PHP – Win (0)
  This will be a quick tutorial that will show you how to enable e-mail function in PHP on Windows....
• 16.10.2009 -- PHP DomDocument Tutorial (15)
  This will be a quick tutorial that will show you how to use PHP's DOMDocument to parse your XML so y...
• 25.09.2009 -- PHP Confirm Registration Link (6)
  This will be quick tutorial on how you can implement confirm registration link into your PHP applica...