How to check if specific table names exist in a database?

Question

All I want to do is check if a table exists or not and I can't figure it out.

I've tried:

for ($r = 0; $r < count($tableArray); $r++) {
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query = "select * from `#__".$tableArray[$r]."` LIMIT 1";
    $db->setQuery($query);

    if ($db->setQuery($query) !== False) {
        $results = $db->loadAssocList();
        echo "<br>".$tableArray[$r]."table found";
    } else {
        echo "<br>".$tableArray[$r]."table NOT found";
    }
}

I've also tried to get an array of Joomla table names that I can iterate through.

Array('table1','table2','table3' etc

I can do this with SHOW TABLES, but this produces a large array of arrays each with one entry.

---

Solution I used:

I'm including what I did in the end; both answers were helpful.

I did this to get the array of the tables:

$db = JFactory::getDbo();
$results = $db->setQuery('SHOW TABLES')->loadColumn();

and then I used in_array() to find if the table I was looking for was there:

$prefix = $db->getPrefix();
for ($r = 0; $r < count($tableArray); $r++) {
    if (in_array($prefix.$tableArray[$r], $results)) {
        echo "<br>Found ".$tableArray[$r];
    }else{
        echo "<br>Missing ".$tableArray[$r];
    }
}

$tableArray is the array holding the table names to be checked.

Answer 1

When using SHOW TABLES load results with loadColumn() to receive a simple array with table names as values.

print_r(JFactory::getDbo()->setQuery('SHOW TABLES')->loadColumn());

Output:

Array
(
    [0] => tbl_assets
    [1] => tbl_associations
    [2] => tbl_banner_clients
    [3] => tbl_banner_tracks
    [4] => tbl_banners
...
)

Answer 2

What is likely to be tripping you up is that your $tableArray doesn't have prefixes. This is evident in your query because you prepend the tablename with #__ in the FROM clause. The resultset will be delivering the rendered prefix with the tablename, so your input array values will not match the result set values.

*Important: Always try to avoid iterated database queries as a matter of best practice. Performing multiple queries unnecessarily will result in slow page loads not to mention burden your system.

I've tested the following to be successful on my localhost. I've written this to be completely dynamic (no hardcoding the db name or table prefix).

If you don't want to filter the table names in the result set, you can use this:

$db = JFactory::getDbo();
$results = $db->setQuery('SHOW TABLES')->loadColumn();

$prefix = $db->getPrefix();
foreach ($tableArray as $t) {
    echo "<br>" , (in_array($prefix.$t, $results) ? "Found " : "Missing ") , $t;
}

Otherwise, you can filter the result set by extending the query logic.

Code:

$tableArray = ["banners", "content", "cucumbers"];
try {
    $db = JFactory::getDbo();
    $config = JFactory::getConfig();
    $dbname = $config->get('db');
    $prefix = $db->getPrefix();
    foreach ($tableArray as $t) {
        $q_tablenames[] = $db->q($prefix.$t);  // prefix and quote-wrap for the query
    }
    $query = $db->getQuery(true)
        ->select("SUBSTRING(table_name, 7)")
        ->from("information_schema.tables")
        ->where(["table_schema = " . $db->q($dbname), "table_name IN (" . implode(',', $q_tablenames) . ")"]);
    // echo $query->dump();    // never show to public
    $db->setQuery($query);
    $found = $db->loadColumn();
    foreach ($tableArray as $tablename) {
        echo "<div>$tablename table " , (in_array($tablename, $found) ? "" : "not ") , "found</div>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');    // never show actual error to public
}

Output:

banners table found
content table found
cucumbers table not found

---

The reason that I am using a more verbose query is because I don't actually need to extract every table name from the database -- I only need the ones that exist in the array. This is more about "direct coding intentions" rather than micro-optimization (which I won't bother to benchmark).

The IN data is prefixed because the $tableArray values are not pre-fitted with prefixes. The SELECT trims off the prefixes for simple comparisons in the foreach() loop.

---

Here is an alternative approach using SHOW TABLES which achieves the same result:

$tableArray = ["banners", "content", "cucumbers"];
try {
    $db = JFactory::getDbo();
    $config = JFactory::getConfig();
    $dbname = $config->get('db');
    $prefix = $db->getPrefix();
    foreach ($tableArray as $t) {
        $q_tablenames[] = $db->q($prefix . $t);  // prefix and quote-wrap for the query
    }
    $db->setQuery("SHOW TABLES FROM " . $db->qn($dbname) . " WHERE " . $db->qn("Tables_in_$dbname") . " IN (" . implode(',', $q_tablenames) . ")");
    $found = $db->loadColumn();
    foreach ($tableArray as $tablename) {
        echo "<div>$tablename table " , (in_array($prefix.$tablename, $found) ? "" : "not ") , "found</div>";
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');    // never show to public
}