Alter table SQL command in wordpress

1 Views

Due to some changes in the DB of my plugins, I need to alter a table to add some columns to it, but even though the function is running, the table isn’t altered. Here’s the function that I’ve written 

    $sql = "ALTER TABLE `wp_tnt_videos` ADD `date_created` INT NOT NULL DEFAULT '0' AFTER `video_order`, ADD `date_modified` INT NOT NULL DEFAULT '0' AFTER  `date_created`, ADD `user_id` INT NOT NULL DEFAULT '0' AFTER `date_modified`;";

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

I read this: http://hungred.com/how-to/wordpress-dbdelta-function/
And tried to check sql command carefully. I also run that sql command in mysql and it work!!
So, please help me! 😐

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Your answer is in the Codex:

    • You must put each field on its own line in your SQL statement.
    • You must have two spaces between the words PRIMARY KEY and the definition
      of your primary key.
    • You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
    • You must not use any apostrophes or backticks around field names.

    http://codex.wordpress.org/Creating_Tables_with_Plugins#Adding_an_Upgrade_Function

    For one, you’ve got backticks around field names. That rule probably applies to table names too but I am not sure.

    Additionally, you are trying to use dbDelta as if you were making a direct query to the database. It doesn’t work that way. You need to give it the complete desired table structure using CREATE TABLE as if you are creating new every time and …

    The dbDelta function examines 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 (see
    wp-admin/upgrade-schema.php for more examples of how to use dbDelta).

    http://codex.wordpress.org/Creating_Tables_with_Plugins#Adding_an_Upgrade_Function

    You don’t give it ALTER TABLE commands or feed it just the parts you want to change. It is not a general purpose database alteration tool in that way. And, as already mentioned, it is very picky. Format your SQL exactly like in the examples, line breaks included.

    I would add that you need to watch this function very carefully. It fails silently and seems to not what to do certain things, like delete indexes– at least I have had a lot of trouble with that. I am not sure if it will move columns around to insert new ones or just add to the end.