16

I read the wordpress codex and professional wordpress. It seems both use something like

if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

to determine if the table exists. Is there any reason why CREATE TABLE IF NOT EXISTS ( ... ) is not used? It will check and create the table in 1 query, won't it be better? Or am I missing something?

11

If you use "IF NOT EXISTS" then the dbdelta script will not upgrade your database with delta's appeared after the initial creation of the database.

(assuming you want to re-use the same sql script)

at least... that is what i think

5

DISCLAIMER : I'm not a WordPress Guru, only a MySQL DBA

If you want to user a different query, try this

SELECT COUNT(1) FROM information_schema.tables WHERE table_schema='dbname' AND table_name='tbname';

It will either return 0 (if table does not exist) or 1 (if table does exist)

5

Try this one:

global $wpdb;
$table_name = $wpdb->base_prefix.'custom_prices';
$query = $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) );

if ( ! $wpdb->get_var( $query ) == $table_name ) {
    // go go
}
1
  • 1
    Please edit your answer, and add an explanation: why could that solve the problem? – fuxia Apr 12 '19 at 15:47
4

TL;DR

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
global $wpdb;
$tablename = 'myTable'; 
$main_sql_create = "CREATE TABLE $tablename";    
maybe_create_table( $wpdb->prefix . $tablename, $main_sql_create );

WordPress now provides the maybe_create_table function - see https://developer.wordpress.org/reference/functions/maybe_create_table/

Before calling this function you have to manually include the upgrade.php file, otherwise you will get a fatal error like follows:

PHP Fatal error: Uncaught Error: Call to undefined function maybe_create_table()

1
  • Welcome to WordPress Development! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. – Glorfindel Dec 3 '20 at 7:01
1

Not sure how or why this works, but I can do:

if (in_array('snippets', $wpdb->tables)) {
  // do something if wp_snippets exists
}
2
  • 2
    this only works for core tables as I find while testing – Manchumahara Mar 7 '19 at 8:02
  • it worked for me for detecting Code Snippets plugin's table. – iSWORD Mar 7 '19 at 10:32
0

Use get_var function from wpdb class with an exception handling:

try {
    $wpdb->hide_errors();
    $wpdb->get_var( 'SELECT COUNT(*) FROM ' . $wpdb->prefix . 'translator' );
    $wpdb->show_errors();
} catch (Exception $e) {
    error_log($e);
} finally {
    translator_create_db();
}

Reference: SELECT a Variable

0

I understand this is really old question, but maybe some one find it usefull. Small calss that handle function on new DB, version of table and create if does not exists.

class ACCESS_TBDB {
 private $wpdb = null;
 private $accessTable = 'table_prefix'; // just name of your table prefix
 private $accessTableVer = '1.0.7'; // table version
 private $accessTableOptName = 'access_db_ver'; // name for option for db version

public function __construct()
{
    global $wpdb;
    //I does not like to call global $wpdb in every function in the class
    $this->wpdb = $wpdb;
    $tablename = $this->accessTable;
    //set our table prefix
    $this->wpdb->access_table = $this->wpdb->prefix . $tablename;

    // every time check if db need to be created or not
    $this->checkAccessDatabase();
}

private function createDBSQL(){
    // create db sql for table
    $charset = $this->wpdb->get_charset_collate();
    $sql = "
    CREATE TABLE {$this->wpdb->access_table} (
        id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        name TINYTEXT,
        type VARCHAR(10),
        value SMALLINT DEFAULT 0 NOT NULL,
        created_at DATETIME DEFAULT NULL,
        PRIMARY KEY (id)
    ) $charset;
    ";

    return $sql;
}

private function checkAccessDatabase(){

    $version = $this->accessTableVer;

    //handle DB versions
    $db_version = get_option( $this->accessTableOptName , '0.0.0');
    
    //Without we will face error on dbDelta and maybe_create_table
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    $dbCreateSql = $this->createDBSQL();
    
    // check if maybe DB need upgrade
    if (version_compare($version, $db_version, '>')) {
        
        // if it contain old dev/legacy versions
        if (version_compare($db_version, '1.0.0', '<')) {
            $this->wpdb->query("DROP TABLE IF EXISTS {$this->wpdb->access_table};");
        }

        dbDelta($dbCreateSql);

        update_option( $this->accessTableOptName , $version);

    }else{
        //https://developer.wordpress.org/reference/functions/maybe_create_table/
        // just check DB if exist in case it was deleted because of reasons ... :)
        $recreate = maybe_create_table( $this->wpdb->access_table, $dbCreateSql );
        if(!$recreate){
            update_option( $this->accessTableOptName , $version);
        }
    }
    
 }
    ... other functions ...
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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