0

I am using PHP and have a constant declaring the table column details.

I currently $sql = "CREATE TABLE ' . $table_name . ' ' . TABLE_COLUMNS; and odbc exec it, where TABLE_COLUMNS is a DEFINE() (constant literal string).

This is more of an SQL question than coding. I don't want to change any code other than the DEFINE; I just want (if possible) one SQL statement which creates the table if it does not exist, or updates it if it does, inserting columns as necessary, with either blank, or zer0 or some meaningful in the already extant rows (which must remain).

Can that be done, or do I have to write some code which knows what the columns were before and what the new ones are now? (and what about deleting columns?)

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    I have a class that can be used like this: $table=$db->register_table(Z_TABLE_USERS_SETTINGS); $table->add_column('user_id'); $table->add_column('var_id'); $table->add_column('value')->type('varchar'); $table->add_index(array('user_id','var_id'),'unique','uni_user_var'); $table->register(); It will automatically change what needed to be changed in DB if you change something here. Unfortunately it is uses some other external classes. Do you want to look at it? – XzKto May 31 '11 at 09:11
  • 1
    You should look into using database migrations to avoid such fiddling with schemas. A decent PHP package is [ruckusing](https://github.com/ruckus/ruckusing-migrations). – deceze May 31 '11 at 09:18
  • +1 to both. @ XzKto, yes, please post your class as an answer. Thanks – Mawg says reinstate Monica May 31 '11 at 12:08

1 Answers1

1

You won't get there with a single define. You should try to create the table. If that fails, try to create each of the columns. Not that adding columns one by one can be very time consuming on large tables, because MySQL is actually recreating the whole table and copying all the data.

It is better to get the current structure of the table, parse it, and add the required columns.

Please check this similar question.

Community
  • 1
  • 1
GolezTrol
  • 114,394
  • 18
  • 182
  • 210