Check if table exist using PDO

Question

Hello all decided to begin learning PDO. However i'm having trouble creating a function to check if a table exists in my db.

Each individual item has there own table in which the table name is ($id).

<?php
include_once('config.php');
include_once('simple_html_dom.php');

$html = file_get_html('http://localhost:8888/CDOnline%20Online.html');

foreach($html->find('div.product-stamp-inner') as $content) {   

$detail['itemid'] = $content->find('a',0)->href;
$detail['itemid'] = substr($detail['itemid'], 40, 6);
if (strpos($detail['itemid'], "?") !== false) {
$detail['itemid'] = substr($detail['itemid'], 0, 5);
}
$id = $detail['itemid'];

tableExists($dbh, $id);
}

function tableExists($dbh, $id)
{
//check if table exists
}

$dbh = null;
?>

I've tried to scour the forums in search of an answer but came up empty handed. The only thing that got me close to my answer was:

function tableExists($dbh, $id)
{
$results = $dbh->query("SHOW TABLE LIKE `$id`");
if(count($results)>0){echo 'table exists';}
}

But that just says all tables exists, when half of the tables don't exist.

Edit:The table should exist if there is 1 or more rows.

Answer 1

You are using backtics around $id. Change it to single quotes. Like this:

"SHOW TABLES LIKE '$id'"

Further note that the statement count($results)>0 will not work. You'll have to use $results->rowCount() instead.

---

Fxing both errors will give you the following function:

function tableExists($dbh, $id)
{
    $results = $dbh->query("SHOW TABLES LIKE '$id'");
    if(!$results) {
        die(print_r($dbh->errorInfo(), TRUE));
    }
    if($results->rowCount()>0){echo 'table exists';}
}

Answer 2

SHOW TABLES... is MySQL dialect and will fail under pretty much any other DB engine. This is certainly not the definitive answer to portable table detection.

The closest to portable you could get would be to check whether SELECT * FROM $table yields an error, or similar methods discussed here

If your need is simply to create a table if it does not exist, you can use:

CREATE TABLE IF NOT EXISTS $table ($field, ...)

You might want to name your tables in lower case to work around this bug if your DB is located on a Windows MySQL server, though.