1

I'm using a CMS that installs some tables then to verify everything installed correctly attempts to count the number of tables in the database. (Note the goal is to count the tables themselves and not the rows in the tables)

The query that's used is "SHOW TABLES" the results of this is saved to "$result". Like so:

$link->query("SHOW TABLES")

Then farther down the code there's a count on $result like so:

if ($result->rowCount() > 0) {
    $r = $result->fetchAll(PDO::FETCH_ASSOC);
}else{
    $r = false;
    return $r;
}

When I test the result I'm getting 0 returned. (There are 14 tables in the DB) Is rowCount the proper method to apply when using SHOW TABLES? From what I read rowCount only counts affected rows. Since SHOW TABLES doesn't affect anything it won't count anything.

Is my assumption correct or is there something else going on here?

Monk
  • 43
  • 2
  • Just curious - don't you know the number already? I can't imagine the reason to get it dynamically. – Your Common Sense Jan 31 '13 at 16:44
  • See http://stackoverflow.com/questions/64894/select-data-from-show-tables-mysql-query for getting a list of tables via a regular select. – fvu Jan 31 '13 at 16:44
  • similar to: http://stackoverflow.com/questions/11378375/alias-to-show-tables-mysql-result – xQbert Jan 31 '13 at 16:48
  • Common Sense - Not sure, this isn't my code base I'm just trying to trouble shoot it. I can only assume that the author wants the flexibility to add more tables in the future as the CMS expands. Thanks fvu and xQbert, those links look promising. – Monk Jan 31 '13 at 16:51
  • Your assumption is correct; it should be used for update / delete statements. – Ja͢ck Jan 31 '13 at 17:06

2 Answers2

0

Try 

$count = $link->query('show tables')->fetch(PDO::FETCH_NUM);
Mattt
  • 1,780
  • 14
  • 15
0

Another way with information_schema:

$sql = "
SELECT COUNT( * ) AS 'Tables'
FROM information_schema.TABLES
WHERE table_schema = 'YOUR_DB_NAME'
GROUP BY table_schema
";

$count = $link->query($sql)->fetchColumn();

The other way with SHOW TABLES is:

$count = count($link->query("SHOW TABLES")->fetchAll());
bitWorking
  • 12,485
  • 1
  • 32
  • 38