Count rows from multiple database tables

Question

On a project of mine, I've recently moved from native PDO to the Joomla Database Framework package.

Before, I prepared the following statement to count the number of rows from multiple tables (simplified version):

SHOW TABLES LIKE '%password%'

SELECT SUM(rowcount) 
FROM ( 
    SELECT COUNT(1) AS rowcount 
        FROM password-table-one
        UNION ALL SELECT COUNT(1) 
        FROM password-table-two
    ) AS counts

How would I go about doing this using the Joomla Database Framework package (or less confusingly, just a standard Joomla database query)?

I could potentially use COUNT() to count the number of ID's from each table, but seeing as the full query contains 10 tables with 30+ rows (and growing), I believe this could be a performance impact.

Answer 1

I think the cleanest looking / most direct way would be to go through the information_schema.

$db = JFactory::getDBO();
try {
    /* SELECT table_name, table_rows
     * FROM information_schema.tables
     * WHERE table_name LIKE '%password%'
     */
    $query = $db->getQuery(true)
                ->select("table_name, table_rows")
                ->from("information_schema.tables")
                ->where("table_name LIKE '%password%'");
    $db->setQuery($query);
    $db->execute();
    $resultset = $db->loadAssocList();
    if (!$resultset) {
        echo "No Tables Containing 'password' Found";
    } else {
        echo "<table>";
            echo "<tr><th>Table Name</th><th>Row Count</th></tr>";
            foreach ($resultset as $row) {
                echo "<tr><td>{$row['table_name']}</td><td>{$row['table_rows']}</td></tr>";
            }
        echo "</table>";
    }
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage();  // never show the actual error message to the public
}

Output (with my fabricated test tables):

----------------------------------
|   Table Name   |   Row Count   |
|----------------|---------------|
|  a_passwords_1 |       3       |
|----------------|---------------|
|  a_passwords_2 |       5       |
|----------------|---------------|
|  a_passwords_3 |       0       |
----------------------------------

While this is 100% accurate for my database, I should include a link that offers a bit more detail about the fineprint and performance comparisons between a few different approaches.

Also, I want to state that when the raw query was executed via phpMyAdmin, many warning flooded the screen, but none of that mess was presented when called from php/Joomla.

---

Of course, if you are only interested in the aggregate count, this will be more direct:

$query = $db->getQuery(true)
            ->select("SUM(table_rows)")
            ->from("information_schema.tables")
            ->where("table_name LIKE '%password%'");
$db->setQuery($query);
$db->execute();
echo $db->loadResult();

Output:

8

Answer 2

You can use SQL_CALC_FOUND_ROWS

first part you include SQL_CALC_FOUND_ROWS in your query followed by the columns you want to retrieve or * for all columns. Inmediatly after runing the query you have to retrieve SQL_CALC_FOUND_ROWS.

$db = JFactory::getDBO(); 
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM #__your table";
$db->setQuery( $query );
$rows = $db->loadObjectList();  

$db->setQuery('SELECT FOUND_ROWS();');
$count  = $db->loadResult() ;

Answer 3

From my understanding, there is no Joomla function that counts the rows in a table.

It's safe to say I do not understand joins when writing queries, therefore I wrote the following instead:

$count = array();

$query = $db->getQuery(true);
$db->setQuery("SHOW TABLES LIKE '%password%'");

$tables = $db->loadObjectList();

foreach ($tables as $table)
{
    $query = $db->getQuery(true);

    $query->select('COUNT(id)')
          ->from($db->qn($table));

    $db->setQuery($query);

    $count[] = $db->loadResult();
}

return array_sum($count);

This does the following:

1. Gets all of the table names that match password and returns an object
2. Runs a foreach loop querying all the tables and counting the number of ID's
3. Each foreach loop result gets added to the $count array.
4. Returns the sum of all the array values

If anyone has a better approach, I'm all ears.