0

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.

1

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() ;
  • Thanks. Would there be an equivalent through chaining queries? – Lodder Oct 26 '15 at 17:16
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
0

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.

  • 3 years ago when I was probably learning how to access a variable outside a loop. Terrible I know – Lodder Aug 4 '18 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.