multi_query with MySQL COUNT(*)

[rwhite35]

Having a tough time getting multiple select count statements to return a result.  Tried a few different way, but just looking to tally individual table rows.  Any thoughts?

$res = array();  //output array
$query = "SELECT COUNT(*) AS count FROM tbl_1"; //assign count to an alias (column), but tried it without also
$query .= "SELECT COUNT(*) AS count FROM tbl_2";

if($mysqli->multi_query($query)){
 do {
   if ($result = $mysqli->store_result()) {
    $row = $result->fetch(); //tried this with fetch_row() and while loop
    $res[] = $row['count'];
   } else {
    echo "store result returned false.";
   }
   $result->free();
  } while ($mysqli->more_results() && $mysqli->next_result());
}		
$mysqli->close();
print_r($res);

Thanks in advance.

[DavidAM]

When query() or multi_query() fails (returns false), you should have a look at the error message from the database server:

echo $mysqli->error. This will explain the problem.

 

In the code you posted, you need a semi-colon between the two queries:

$res = array(); //output array
$query = "SELECT COUNT(*) AS count FROM tbl_1;"; // Need a semicolon between statements in a multi-query
$query .= "SELECT COUNT(*) AS count FROM tbl_2";
 
if($mysqli->multi_query($query)){
	do {
		if ($result = $mysqli->store_result()) {
			$row = $result->fetch(); //tried this with fetch_row() and while loop
			$res[] = $row['count'];
		} else {
			echo "store result returned false.";
		}
		$result->free();
	} while ($mysqli->more_results() && $mysqli->next_result());
} else {
	// Why did it fail?
	echo "Query Failed: " . $mysqli->error . ' -- ' . $query;
}
$mysqli->close();
print_r($res);

[rwhite35]

Thanks, ";" was it.  Here's the working code then.

$res = array();
$query = "SELECT COUNT(*) FROM tbl_1;"; //requires the ";"  to separate query statements.
$query .= "SELECT COUNT(*) FROM tbl_2";

if($mysqli->multi_query($query)){
 do {
	if ($result = $mysqli->store_result()) {
	  while($row = $result->fetch_row()){
	    $res[] = $row[0];
	  }
	} else {
	  	echo "store result returned false.";
	}
	$result->free();
  } while ($mysqli->more_results() && $mysqli->next_result());
} else {
   echo $mysqli->error;
}		
$mysqli->close();
print_r($res);

Edited July 31, 2013 by rwhite35