PHP MySQL database not creating new table

Question

So I want to create a new table in my database and I can't seem to get it working. It's a wordpress plugin so I don't know if that's what could be messing it up.

if ($wpdb->get_var('SHOW TABLES LIKE "' . $wpdb->prefix . DB_PROFILE_TABLE . '"') != $wpdb->prefix . DB_PROFILE_TABLE)
        {
            $sql = 'CREATE TABLE ' . $wpdb->prefix . DB_PROFILE_TABLE . ' (
                    id BIGINT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
                    fname VARCHAR(100) NOT NULL,
                    sname VARCHAR(100),
                    city VARCHAR(100),
                    country VARCHAR(100)
                    CHARSET=utf8; ';
                dbDelta($sql);
        }

Beginner at php and mySql.

Is there any particular reason why you accepted Piyush's answer over mine? Both are the same, but I posted first? — mdadm
– mdadm, Commented Nov 24, 2014 at 3:31
@mdadm Sorry. I would accept both as the answers but I'm unable to. Do you want me to change it to your one? — cala
– cala, Commented Nov 25, 2014 at 1:34
no worries, I was just curious. Piyush has slightly lower points than myself, so let him keep it :) Did you manage to get it working? — mdadm
– mdadm, Commented Nov 25, 2014 at 2:20
@mdadm ok, thanks for your help. No not yet, I'm still trying to get my head around it. I'm looking at examples from wordpress codex but it still isn't creating creating the tables for me after I refresh it. — cala
– cala, Commented Nov 25, 2014 at 18:55

HaveNoDisplayName · Accepted Answer · 2014-11-24 02:25:20Z

1

You are missing parenthesis...

Can you try in this way

global $wpdb;

$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  name tinytext NOT NULL,
  text text NOT NULL,
  url varchar(55) DEFAULT '' NOT NULL,
  UNIQUE KEY id (id)
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

These are the rules to create table using wordpress plugin

1) You must put each field on its own line in your SQL statement.

2) You must have two spaces between the words PRIMARY KEY and the definition of your primary key.

3) You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.

4) You must not use any apostrophes or backticks around field names. Field types must be all lowercase.

5) SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.

Refer http://codex.wordpress.org/Creating_Tables_with_Plugins

answered Nov 24, 2014 at 2:25

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cala Over a year ago

Ok, I've tried it this and still nothing has been created in my database. Should I just create it manually in phpmyadmin?

HaveNoDisplayName Over a year ago

is your plugin correctly configured? Refer link in answer

mdadm · Accepted Answer · 2014-11-24 02:41:03Z

1

Looks like you are missing a closing parenthesis after country VARCHAR(100). You may also need to remove the semi-colon as well.

$sql = 'CREATE TABLE ' . $wpdb->prefix . DB_PROFILE_TABLE . ' (
    id BIGINT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(100) NOT NULL,
    sname VARCHAR(100),
    city VARCHAR(100),
    country VARCHAR(100))
    CHARSET=utf8';
dbDelta($sql);

edited Nov 24, 2014 at 2:41

answered Nov 24, 2014 at 2:23

mdadm

1,3631 gold badge12 silver badges9 bronze badges

3 Comments

cala Over a year ago

Changed this and its still not working. Thanks for the help though!

mdadm Over a year ago

Try taking out the semi colon for giggles.

mdadm Over a year ago

Can you try printing out the results of mysql_error() if that doesn't solve it?

Collectives™ on Stack Overflow

PHP MySQL database not creating new table

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related