Mysqli and multiple stored procedure calls?

[random1]

I have created this method within my database.php class.

 

I'm using MySQL on my localhost PC.

 

The code:

 

public function executeQuery($sql, $type = '')
{
	if($sql == '')
	{
		return false;
	}

	// Connection Variable
	$connection = $this->m_Connection;

	// Execute SQL using Connection
	(!empty($sql) && !empty($connection)) ?
	$result = $connection->multi_query($sql) : $result =  '';

	// Check if connection worked and no error was thrown
	if(!empty($connection) && mysqli_connect_errno() != 0 || $result == '')
	{
		// Unsuccessful
		$errorMessage = '**** mysqli error ' . mysqli_connect_errno() .
		': ' . mysqli_connect_error();
		error_log($errorMessage);
		throw new DatabaseBadSqlStatementException($errorMessage);
		return false;
	}
	// No errors and connection active
	else
	{
		// Successful
		$this->setDatabaseQueryCount($this->getDatabaseQueryCount() + 1);
		mysqli_next_result($connection);
		return true;
	}
}

 

Should the line:

 

if(!empty($connection) && mysqli_connect_errno() != 0 || $result = '')

 

instead be:

 

if(!empty($connection) && mysqli_connect_errno() != 0 || $result == '')

 

?

 

The particular stored procedure SQL I'm passing to this function works in Heidi SQL, a mysql editor, but not in my PHP. Other procedure calls work fine with it.

 

Any ideas to fix it?

[random1]

when I try it with the second line I get:

 

Fatal error: Uncaught exception 'DatabaseBadSqlStatementException' with message '**** mysqli error 0: ' in ...\database.php:462 Stack trace: #0

 

It gives the error as 0 but no error message.

 

The SQL I'm passing is:

 

CALL `procedure_visit_insert`(12, 1, '6f7999c8a70ff5e52ebdc17b46b2386c9fea97198c31906d6d6f757ab7bf7c3244e3ef60401562b5e6ea6158fb58186dc29364f4edbeb80c1dc4dedbbcb7499c', 'Firefox', '3.6.6', 'Windows 7', '6.1', 1, 82, ;

[Mchl]

Try displaying mysqli_error not mysqli_connect_error()

[random1]

From that I get the error:

 

Commands out of sync; you can't run this command now

[Mchl]

You need to call mysqli_store_result to fetch entire result from previous query before you can fetch a result from the following one.

[random1]

Where in my code would I need to add:

 

mysqli_store_result($connection);

[Mchl]

If I understand correctly, this function should look something like this:

 

public function executeQuery($sql, $type = '') {
  if(empty($sql))) {
    return false;
  }

// Connection Variable
  $connection = $this->m_Connection;

// Execute SQL using Connection

  if(!$connection->multi_query($sql)) {
    
  }
  
  do {
if($connection->errno != 0) {
  throw new DatabaseBadSqlStatementException(
    '**** mysqli error '.PHP_EOL.
    $connection->error.PHP_EOL.
    $sql,
    $connection->errno
  );
}

if($result = $connection->store_result()) {
  $this->setDatabaseQueryCount($this->getDatabaseQueryCount() + 1); 
  //do something with result
  $result->free();
}

  } while ($connection->next->result());

  return true;

}

[random1]

I ended up with:

 

public function executeQuery($sql, $type = '')
{

	if(empty($sql))
	{
		return false;
	}

	// Connection Variable
	$connection = $this->m_Connection;

	mysqli_next_result($connection);

	// Execute SQL using Connection
	if(!$connection->multi_query($sql))
	{
		//
	}

	do
	{
		if($connection->errno != 0)
		{
			/*throw new DatabaseBadSqlStatementException(
			'**** mysqli error ' .PHP_EOL.
			$connection->error.PHP_EOL.$sql,
			$connection->errno);*/
			return false;
		}
		if($result = $connection->store_result())
		{
			$this->setDatabaseQueryCount($this->getDatabaseQueryCount() + 1);
			//do something with result
			$result->free();
		}
	}
	while(mysqli_next_result($connection));
	return true;
}

 

This seems to working fine and I'll do more testing to get it just right Thanks

[Mchl]

Just noticed that this:

 

if(!$connection->multi_query($sql)) {
  //
}

 

should in fact be

 

$connection->multi_query($sql);

[random1]

Thank you so much Mchl