Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
    
The syntax is the same as general MySQL syntax, therefore this question is not specific to WordPress. Google the correct syntax for an ALTER TABLE command –  bungeshea Jan 8 '13 at 9:11
1  
The MySQL query is correct. The problem is on dbDelta front. Thus, it is a wordpress query. I have tried the command in MySQL console by myself before posting it here. –  mehulved Jan 8 '13 at 9:33

1 Answer 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.

share|improve this answer
    
dbDelta() does support adding and updating columns. See: wordpress.stackexchange.com/q/76926/19726 –  bungeshea Jan 8 '13 at 9:37
    
I never said it didn't, I just said the way he went about it was incorrect. Use a create statement for the table you want, not an ALTER statement, and dbDelta will create the columns for you and modify the table until it matches what the SQL says –  Tom J Nowell Jan 8 '13 at 9:45
    
Okay, I get it. I never realized that you could do this with dbDelta()! +1 –  bungeshea Jan 8 '13 at 9:47
    
So, I updated the SQL statement to add the price column but got DB error that the table already exists. Here's the error message WordPress database error: [Table 'wp_booking_packages' already exists] CREATE TABLE wp_booking_packages( id mediumint(9) NOT NULL AUTO_INCREMENT, name text NOT NULL, description text NOT NULL, price decimal(14,2) NOT NULL, city1 text NOT NULL, city2 text NOT NULL, PRIMARY KEY (id) ) and I checked in MySQL console that the column still doesn't exist. –  mehulved Jan 8 '13 at 9:53
    
I've updated my answer with more details. If you can post any future code using a gist so I can see it in its entirety? Comments tend to ruin any formatting and newlines, which could make the difference between working and not working dbDelta here –  Tom J Nowell Jan 8 '13 at 10:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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