compare 2 mysql tables are identical and if not make them

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
terry1738
Forum Newbie
Posts: 3
Joined: Wed Feb 03, 2010 7:28 pm

compare 2 mysql tables are identical and if not make them

Post by terry1738 »

Hi I have a bit of an unusual request, I am developing an accounts program which we use in our business while under development (unfortunatly).
I have a table in mysql that stores our customer details called "users". When a customer cancels there account we copy the data from "users" to "deleted_users" using a simple "select * from users insert into deleted_users" mysql statement and this works great AS LONG AS the columns in each table are identical.
The problem is because the program is still in development I may occasionally add a column in "users" and forget to add it in "deleted_users" then the customers details are lost.
So what I want is a code snippet (if anybody has one) that checks my "users" table and my "deleted_users" table and makes sure they are identical (column wise) if not it makes "deleted_users" identical to "users".
I have hunted around for a while and I have come up with a solution that I could pursue by getting each column from the "users" table in a while statement then see if that column is in "deleted_users" and if not add it however that method seems excessively long winded and I was hoping there may be an easier way before I spend a lot of time on this problem.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: compare 2 mysql tables are identical and if not make them

Post by Eran »

Why not having a 'status' column in the users table that would indicate if users are deleted or not?
terry1738
Forum Newbie
Posts: 3
Joined: Wed Feb 03, 2010 7:28 pm

Re: compare 2 mysql tables are identical and if not make them

Post by terry1738 »

Thanks yes I agree, That would have been an option 6 years ago but the previous programmer decided to do it like this and I dont know without even more work what depends on this feature such as reestablishing the client when they return and how it fits into the billing if any monies are owing when the client cancels.
Just thought I might be able to save myself some work if someone else had run up against a similar issue.
Currently there are in excess of 11000 clients in the deleted_users table. So its a matter of take the easiest path
edit: Strangly the table does have a status column which was used for deleted and is now used for disabled
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: compare 2 mysql tables are identical and if not make them

Post by Eran »

Personally I would move the deleted_users rows back to users with a different status value than disabled. That would be the normalized approach to storing such data, especially if deleted users can return to active status.
If that's not an option, the ezComponents library has a component for comparing database schemas and applying the results.
http://www.ezcomponents.org/docs/tutori ... se-schemas
terry1738
Forum Newbie
Posts: 3
Joined: Wed Feb 03, 2010 7:28 pm

Re: compare 2 mysql tables are identical and if not make them

Post by terry1738 »

Thanks so much for directing me Thats a great resource I knew nothing about, I can see already how I can implement what I want.
Also I take your point about the status column, it will take quiet a deal of work to implement but I see it as an important part of any rewrite.
Thanks again for taking the time. Much appreciated
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: compare 2 mysql tables are identical and if not make them

Post by Benjamin »

INSERT INTO [table] [fields] SELECT [fields] FROM [table2] WHERE NOT EXISTS (SELECT [field] FROM [table2] WHERE [field] = [field])

I'm sure you could work a little magic with MySQL.
Post Reply