dbDelta ALTER TABLE syntax?

1 Views

Due to some changes in the DB, I need to alter a table to add one column to it, but even though the function is running, the table isn’t altered.
Here’s the ALTER TABLE code that I’ve written 

$sql = "ALTER TABLE " . $packagetable . " ADD COLUMN price decimal(14,2) NOT NULL AFTER description;";
dbDelta($sql);

I couldn’t find the syntax for ALTER TABLE with dbDelta anywhere online.
EDIT: After looking at the ALTER TABLE statement in gravity forms plugin, I have updated the statement in single line.

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. You’ve used the dbDelta function incorrectly.

    The whole point of that function is you pass in a table creation SQL command.

    If the table doesn’t exist, it creates it.

    If the table exists but doesn’t match, it’s modified until it matches. This includes adding and updating columns, indexes, and other aspects.

    So what you want to do is run dbDelta, and provide it with your table creation sql, not table alteration sql.

    See here for the Codex’ take on adding an upgrade/alteration to a table using dbDelta

    But it goes further! dbDelta is a picky function, you can’t just put any SQL statement in there, it has to be formatted appropriately

    Here’s what the codex says on that same page:

    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.

    And from another:

    dbDelta Function

    Like i mention before in one of my article, dbDelta function has the
    ability to examine the current table structure, compares it to the
    desired table structure, and either adds or modifies the table as
    necessary, so it can be very handy for updates of our plugin. However,
    unlike many WordPress function, dbDelta function is the most picky and
    troublesome one. In order for dbDelta function to work, a few
    criteria will have to be met.

    1. You have to put each field on its own line in your SQL statement.
    2. You have to 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.

    Well, the above criteria’s seem easy to achieve. But wait till it hits you.

  2. To add column to some WP table in DB, you could use $wpdb for it:

    global $wpdb;

$table = $wpdb->prefix . 'my_table';
$sql = "ALTER TABLE `{$table}`
        ADD `new_column` VARCHAR(20) NULL DEFAULT NULL;";

$query_result = $wpdb->query( $sql );

    $wpdb->query() returns false if your query went wrong, according to method’s documentation. So you could write some if-else statement to do something on success or error:

    if ( $query_result === false ){
    // error occurred
} else {
    // success
}

    If you need add your new column after special column, just use AFTER in query:

    $sql = "ALTER TABLE `{$table}`
        ADD `new_column` VARCHAR(20) NULL DEFAULT NULL
        AFTER `exist_column`;";

    Also, you could change

    $sql = "ALTER TABLE `{$table}`
        MODIFY COLUMN `exist_column` VARCHAR(20) NULL DEFAULT NULL;";

    or drop column

    $sql = "ALTER TABLE `{$table}`
        MODIFY COLUMN `exist_column`;";

    Small tip: In PHP, inside double-quote ("), you could use variable in curly brackets ("foo {$var} bar") or without them ("foo $var bar") to input variable to string, instead of concatenation ("foo " . $var . " bar"). I use it in code above. Learn more about string parsing in php.