CODEWITHSUNDEEP
phpmysqlpdo
Dog
Dog

Reputation: 339

Check if table exist using PDO

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.

Upvotes: 2

Views: 12075

Answers (6)

Faysal Kabir
Faysal Kabir

Reputation: 16

Just use good old row count. There must be a result if table exist.

$sql ="SELECT count(*) FROM [table name]";
$sql_result = $this->db_mssql->prepare($sql);
$sql_result->execute();
$sql_result->setFetchMode(PDO::FETCH_ASSOC);
$my_res = $sql_result->fetchAll();
if($my_res){
   echo 'table exists';
}

Upvotes: -1

rhodesit
rhodesit

Reputation: 41

This seems like the simplest way to do this. You pass this function an array with the pdo object you have already created.

$db['pdo']=new PDO('mysqlconnectiongoeshere');
$db['table']='tabletobechecked';

function is_table($db){
   $is_table=$db['pdo']->query("SHOW TABLES LIKE '".$db['table']."'");
   return$is_table->rowCount();
}

This way if the table exists this function returns a '1' and if it doesn't exist this function returns a '0';

So you can use it like this:

if(is_table($db)){
  echo"The table".$db['table']." exists.";
}else{
  echo"The table".$db['table']." does not exist.";
}

Upvotes: 0

eddy147
eddy147

Reputation: 4983

private function doesTableExist($table)
{
    try {
        $this->db->query("DESC $table");
    } catch (Exception $e) {
         return false;
    }
    return true;
}

Upvotes: -1

Esoterica
Esoterica

Reputation: 133

This is posted simply if anyone comes looking for this question. Even though its been answered a bit.

if ($con->query(
                   "SHOW TABLES LIKE '" . $table . "'"
               )->rowCount() > 0
        or die("No table set")
   ){

With this I just push the else condition into or. And for my needs I only simply need die. Though you can set or to other things. Some might prefer the if / else if / else. Which is then to remove or and then supply if / else if / else.

Or if I was to redo the function:

function tableExists($dbh, $id){

    if ($dbh->query(
                   "SHOW TABLES LIKE '" . $id . "'"
               )->rowCount() > 0
        or die(print_r($dbh->errorInfo(), TRUE))
   ){
    echo 'table exists';
    }

}

Did not test the redo, though it should work the same as the code I have used before. Just in function.

Upvotes: 1

kuroi neko
kuroi neko

Reputation: 8641

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.

Upvotes: 2

hek2mgl
hek2mgl

Reputation: 157947

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';}
}

Upvotes: 14

Related Questions