1

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.

  • When you have 15 rep points or more, you can upvote answers that you find helpful. I see from your edit, that it was not your SHOW TABLES query that was the problem, but your php conditional that was inhibiting things -- which only my answer addresses. If you don't like my long-winded/convoluted queries, that's fine, but I would appreciate receiving an upvote for volunteering my time and delivering a comprehensive and working solution. – mickmackusa Oct 18 '18 at 23:25
  • I have edited your implemented solution because there were a few lines of code that simply were not needed. I have done this for the benefit of you and future researchers (please do not roll this edit back). – mickmackusa Oct 18 '18 at 23:40
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
...
)
  • Hey there - you put so much effort to write a good answer on the above question. This means that the question should be good too and was worthing an Up-vote. There is a button next to the question for upvoting. You should use it. Also there is another answer posted after yours. If you revisit this question - see what this answer is about. Do you think it's at all any helpful? Upvote it too. – FFrewin Oct 19 '18 at 9:38
3

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
}
  • Hey there - you put so much effort to write a good answer on the above question. This means that the question should be good too and was worthing an Up-vote. There is a button next to the question for upvoting. You should use it. Also there was already another answer on this question. Do you think it's at all any helpful? Upvote it too. – FFrewin Oct 19 '18 at 9:37
  • The other answer didn't solve the actual problem - it gave good advice about how to extract the resultset from a query that the OP had already tried. Not every question that can be answered deserves an upvote - my criteria are not that simple. – mickmackusa Oct 19 '18 at 9:48
  • I have been generous in my service toward this OP in several ways -- far more valuable than a mere points. – mickmackusa Oct 19 '18 at 9:59
  • I feel this question could have been more clear by providing sample data for the input array and the expected result. As it was posted, it was obviously not clear what the isolated issue was judging by how Sharky's answer didn't fix the issue. – mickmackusa Oct 19 '18 at 10:16
  • The accepted answer should be a comment in my opinion. – mickmackusa Oct 19 '18 at 10:29

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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