CODEWITHSUNDEEP
phpmysql
George
George

Reputation: 129

Check for changes in database schema and update

At our company we have a business solution which includes CMS, CRM and several other systems. These are installed in several domains, for each of our clients. The systems are still in development, so new tables and fields are added to the database. Each time we want to release a new version to our clients, i have to go through their database and insert the new fields and tables manually. Is there a way that this could be done automatically(a script maybe that detects the new fields and tables and inserts them?) We are using php and mysql. We would like to avoid backing up the clients data, dropping the database tables, running the sql query to insert all the database tables(including the new ones) and then re-inserting the customers data. Is this possible?

Upvotes: 0

Views: 400

Answers (2)

itsid
itsid

Reputation: 811

What you are looking for is

ALTER TABLE 'xyz' ADD 'new_colum' INT(10) DEFAULT '0' NOT NULL;

or if you want to get rid of a colum 

ALTER TABLE 'xyz' DROP 'new_colum';

Put all table edits into an update.php file and the either call and delete it once manually or try to select "new_colum" once and update the database when it's not present. OR what I do: "I have a settingsfield "software version" and use this as a trigger to update my tables. But since you have to install the new scripts anyways you can just call it manually.

Upvotes: 0

जलजनक
जलजनक

Reputation: 3071

Toad for MySQL

DB Extract, Compare-and-Search Utility — Lets you compare two MySQL databases, view the differences, and create the script to update the target.

Upvotes: 1

Related Questions