Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Question Unanswered: SQL statement to sync data between 2 distinct tables

    Greetings,

    I am currently developing a PHP-based intranet system for my company... and using PEAR:DB as well. However, I am having troubles trying to create a generic SQL statement to sync all my records between 2 different tables in separate databases.

    Here is what I need to synchronize:


    MCINFO_table in Database1 --> Main table
    ====================
    Date * SN * MchNum * MchType * Location * Active
    ---------------------------------------------------
    02/26/01 * 12345 * SA23 * ASMAB339 * SC70 * D
    04/08/02 * 12345 * SA19 * ASMAB339 * SC70 * D
    02/17/03 * 12345 * SA08 * ASMAB339 * SC70 * A
    02/26/01 * 67890 * SA50 * ASMAB339 * SC50 * D
    02/17/03 * 67890 * SA34 * ASMAB339 * SC50 * A



    ENTITY_table in Database2 --> Backup table
    ===================
    Entity * EntityType * SerialNumber * Location
    --------------------------------------------
    SA08 * ASMAB339 * 12345 * SC70
    SA34 * ASMAB339 * 67890 * SC50


    To sum it up, I have a 'SYNC' button in my php page... whereby, once the user clicks on it, the system will synchronize any UPDATED or NEW data within MCINFO_table with ENTITY_table. In other words, any updated or new records within MCINFO_table (the main table) must be in-sync with ENTITY_table (the backup table).

    I will designate MCINFO_table as "M" and ENTITY_table as "E"..... And since both tables have varying fieldnames, these are the field mappings:

    M.MchNum = E.Entity
    M.MchType = E.EntityType
    M.SN = E.SerialNumber
    M.Location = E.Location

    When the SYNC button is pressed, I want the system to check EVERY ROW for any updated or new data in MCINFO_table where

    M.MchNum = E.Entity
    &
    M.Active = A
    &
    M.Date = between today's date & 2 days ago timeframe

    If these conditions are met, then perform the synchronization process with ENTITY_table.

    I've been unable to come up with the correct SQL statements for this sync process... can somebody please help me?... thank you so much :)

  2. #2
    Join Date
    Oct 2003
    Posts
    3
    First of all what version of MySQL are you using?

    To solve this with out doing a foreach/sql stmts on you every row of database1 you could do an update with a join and do them all at once instead of over time.

    ie

    update table1,table2 SET table1.a = table2.a AND table1.b = table2.b WHERE table1.key = table2.key

    To do this you must be running MySQL 4.0.4+.


    http://www.mysql.com/doc/en/UPDATE.html


    Hope that helps.
    -Jarrod Lash

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •