Comparing tables against each other .. HELP

iceman42

So I dont bore anyone with the long drwan out reason for doing this, I am trying to move content between two databases. Same table setup, basically its a stage and production type of setup. I want to take the content from stage and move it to production, but I cant simply overwrite all the tables, some have information that needs to stay on production. So my thought was to compare the number of records on prod and stage, and say there are 2 on prod and 5 on stage, then those last 3 get promoted to prod, but I wanted to compare the id, which is the key field to compare the number, the ids will match between stage and prod. Then after the new content is moved I will update the first two record and turn them offline, or make their status the same as stage.

HELP, any thoughts, this is the toughest part for me.

thanks

Aeiri

Why don't you just have one table and add a column that keeps track of whether its Stage or Production? (I.E. 1=Stage, 2=Production)

jordy

there are some fancy ways in php to do this but keeping the same records in two different db's will cause lots of trouble. A constraint (rule) that must remain true for a database to preserve integrity.

if you have for example a name field in the database.. db a on record 1 contains the name Joe.. db b on the (identical) record 1 contains the name Doe.. the data gets useless because nobody can tell what is the correct way without sorting it by hand. So make sure you store all records that are identical only once.

For more information read some stuff about database normalisation. I can assure you that this will make your life a LOT easier.

Second.. if you have all information in just one database (with one or more tables) you can perform a lot of tasks with just plain SQL. That also will make your life a lot easier.

iceman42

the idea for 1-stage and 2-production would cause me to have to go and rework a lot of code and sql statments, as well as they archive their articles so that wouldnt work without a lot of reworking.

I guess maybe i should have gotten into the whole theory behind what I wanted.

here is what I was looking for , I have to tables and I need to compare the two, we'll call them stage and prod. if prod is always going to have equal number or less rows then stage, and I need to only move in the extra rows from stage to prod, how do I do that, how do I check x field and see if the id matches that on stage, cause row 1 will always match row 1 on stage.

jordy

if row one always matches row two that means the DB is not normalized till at least the Boyce and Scott step.. The only good database is a normalized database this will keep the database at a minimum size and as fool proof as can be. I can give you all the plus parts in this post but i advice you to take a look at the reffers below.

reffer:
http://www.phpbuilder.com/columns/barry20000731.php3
http://www.swynk.com/friends/faour/databasedesign1.asp

Very good book:
Fundamentals of Database Systems (written by Ramez A. Elmasri, Shamkant B. Navathe)

iceman42

nonononononon ... these are seperate databases, the tables are the same and the records are the same, well stage database will always have more.

database 1 - table 1 to compare database 2 - table 1

database 1 = 2 rows
database 2 = 5 rows

need to move the 3 extra rows to database 1 and then change the status of the 2 old rows in database 1.