How to add values from multiple MySQL databases

Question

I'm trying to add the count of items from multiple mysql databases (I haven't got to the adding part yet) to print the total number of records on my homepage.

I've grab the 'count' code basics from a couple sites I Googled, but something is not quite right. If I run the query below by itself in phpMyAdmin, it works fine (I get 36 results from this 1 database), but when I run it in my php page, via the below code, it only returns a count of 1.

Any idea what I'm messing up? Thanks.

$connection="localhost";
$username="myusername";
$password="mypassword";
$database1="mydb1";
$database2="mydb2";

$db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel1 = mysql_select_db($database1, $db1);
$query1 = "SELECT count(postID) FROM my_table";
$result1 = mysql_query($query1, $db1);

$db2 = mysql_connect($connection,$username,$password) or die(mysql_error());
$sel2 = mysql_select_db($database2, $db2);
$query2 = "SELECT count(postID) FROM my_table";
$result2 = mysql_query($query2, $db2) or die(mysql_error());

$total_rows = mysql_num_rows($result2);
print $total_rows; 

Answer 1

Since these are on the same database host (localhost) you can just get them in one query. This method uses a subquery which UNIONs together the query from each database, then does an aggregate SUM() to add them together.

SELECT SUM(postcounts) AS total
FROM (
  SELECT COUNT(postId) AS postcounts FROM mydb1.my_table
  UNION ALL 
  SELECT COUNT(postId) AS postcounts FROM mydb2.my_table
) allposts

The reason your mysql_num_rows() returns a count of 1 is because each of your queries only returns one row - the aggregate COUNT(postIdD). If you wanted to retrieve the actual count, you need to fetch the row.

Supposing the query above was stored in $result, the value you need is aliased as total:

if ($result) {
  $row = mysql_fetch_assoc($result);
  echo $row['total'];
}

The whole thing looks like:

$db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
mysql_select_db($database1, $db1);
$query = "    
    SELECT SUM(postcounts) AS total
    FROM (
      SELECT COUNT(postId) AS postcounts FROM mydb1.my_table
      UNION ALL 
      SELECT COUNT(postId) AS postcounts FROM mydb2.my_table
    ) allposts ";

$result = mysql_query($query, $db1);

// Fetch the resultant row
if ($result) {
  $row = mysql_fetch_assoc($result);
  echo $row['total'];
}