Jump to content

multi_query with MySQL COUNT(*)


Go to solution Solved by DavidAM,

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/280672-multi_query-with-mysql-count/
Share on other sites

  • Solution

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

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 by rwhite35
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.