Database class - Table exists?-CodePudding

I am using the following awesome, easy & lightweight database class: https://codeshack.io/super-fast-php-mysql-database-class/
My problem is I do not know how I can figure out if a table in the database exists or not. I have the following PHP Code:

function addSts($database, $brow, $vers, $pag, $lang) {
  $tablename = "sts" . $pag;
  $stsinsert = $database->query('INSERT INTO ' . $tablename . '(id, browser, version, language, date) VALUES (NULL, ?, ?, ?, current_timestamp())', $brow, $vers, $lang);
  if ($stsinsert->affectedRows()) {
    echo "TABLE EXISTS";
    $database->close();
  }
  else {
    echo "TABLE DOES NOT EXISTS -> CREATE TABLE";
    $pagecreation = $database->query('CREATE TABLE ' . $tablename . ' (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `browser` VARCHAR(20) NOT NULL, `version` VARCHAR(10) NOT NULL,  `language` VARCHAR(5) NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (`id`))');
    if ($pagecreation) {
      addSts($brow, $vers, $pag, $lang);
    }
  }
}

It always throws the following error: Unable to prepare MySQL statement (check your syntax) - Table 'testdb.ststest' doesn't exist
So and here we have the salad. It throws the error and does not go further to the if-else part. SO every time the table does not exist the program stops working.

Hope somebody can help me out.
Thanks in advance.

CodePudding user response：

If you can, use the information_schema DB and query TABLES tables

select * from tables where TABLE_SCHEMA like '<database name>'
e.g. select * from tables where TABLE_SCHEMA like 'mydbdev'

the simply iterate through the results OR

select * from tables where TABLE_SCHEMA like '<database name>' AND TABLE_NAME like '<table name>';

and count the rows (should be 0 if not present or 1 if it is).

CodePudding user response：

As @Barmar mentioned in the comments, you can use try/catch statements to do this.

function addSts($database, $brow, $vers, $pag, $lang) {

    $tablename = "sts" . $pag;

    try {
        // try to insert first
        $stsinsert = $database->query('INSERT INTO ' . $tablename . '(id, browser, version, language, date) VALUES (NULL, ?, ?, ?, current_timestamp())', $brow, $vers, $lang);

        if ($stsinsert->affectedRows()) {
            echo "TABLE EXISTS";
            $database->close();
        }
    }
    catch (\Exception $e){
        echo "TABLE DOES NOT EXISTS -> CREATE TABLE";

        $pagecreation = $database->query('CREATE TABLE ' . $tablename . ' (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `browser` VARCHAR(20) NOT NULL, `version` VARCHAR(10) NOT NULL,  `language` VARCHAR(5) NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (`id`))');

        if ($pagecreation) {
            // call the function to insert data
            addSts($brow, $vers, $pag, $lang);
        }
    }
}