Best way to update the structure of a Mysql Table
There is three databases on a website that I'm working on. One is the live_db, one is the dev_deb and one is the test_db. There is also three versions of the site labeled the same (live.domain.com, dev.domain.com and www is the live). Right now when I want to update something I over write the test db and site with code from the dev site, if all is good, then I will copy the dev updates to the live site. So far this system is working pretty well and I've written some scripts which tell me which scripts are out of date and it's really made updating a synch! However, I still have to manually alter the table structure and I'm starting to think there must be a way easier way to do this.
I know that I could drop tables altogether and then do an import from the updated database but the strange challenge here is that on the dev_db the structure is newer, but the data is older. So sometimes it's not even possible to import either way. What I would really love to discover is some kind of handy mysql or php function that looks at the table structure of the dev_db and then removes or adds in missing columns on the test_db and then eventually the live_db. That would then pave the way for a simple data import if need be.
Right now I'm trying to launch some new inventory management features and I have about 30 columns of structure to add to various tables and I'm feeling a bit overwhelmed by it all and thinking there is probably a smarter way of looking at this problem. If you've got any ideas please let me know! Thanks a lot.