Help with cloning entries accross databases
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Help with cloning entries accross databases
This is for all you PHP/SQL fundies.
I need a PHP function that will update the entries in database B with entries from database A.
The tables are already in place.
PLEASE PLEASE PLEASE HELP!
I need a PHP function that will update the entries in database B with entries from database A.
The tables are already in place.
PLEASE PLEASE PLEASE HELP!
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Help with cloning entries accross databases
Are the database structures exactly the same?
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
Yes. The tables I want the entries cloned in, are EXACTLY the same.
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: Help with cloning entries accross databases
I'd personally do this at the database layer (I've had experience with a Windows Service that used to sync data between a client's intranet database and live site database, and it was NOT pretty) - perhaps some form of replication?
Kia kaha, Kia māia, Kia manawanui.
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
this is exactly what I need the function for. Why was it not pretty?Ron2K wrote:I've had experience with a Windows Service that used to sync data between a client's intranet database and live site database, and it was NOT pretty
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
Re: Help with cloning entries accross databases
There's a few things (amongst others) that your code would need to consider:
![Wink ;)](./images/smilies/icon_wink.gif)
The DBAs on this forum would be able to give you better advice with respect to replication - my knowledge of it is very shaky. (I've only experimented with it once, in a test environment - and that was five years ago.)
- How do you keep track of what you've synced and what you haven't synced?
- If an item is changed in both locations (inbetween syncs), how do you merge the changes?
- How do you handle any form of communications failure?
![Wink ;)](./images/smilies/icon_wink.gif)
The DBAs on this forum would be able to give you better advice with respect to replication - my knowledge of it is very shaky. (I've only experimented with it once, in a test environment - and that was five years ago.)
Kia kaha, Kia māia, Kia manawanui.
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
BAsically I see it as a cron job that runs once a night, compares and updatesHow do you keep track of what you've synced and what you haven't synced?
Its a 1 way update.If an item is changed in both locations (inbetween syncs), how do you merge the changes?
Best part, the databases sit on the same serverHow do you handle any form of communications failure?[/list]
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
Re: Help with cloning entries accross databases
Something for you guys to laugh at:
Being an ignorant noob, I'd try and use one of those action recording programs and have it record me importing each database into Excel and running a compare and replace in there (maybe with an if function or something), exporting it afterwards. Then set the sequence to run after hours.
Being an ignorant noob, I'd try and use one of those action recording programs and have it record me importing each database into Excel and running a compare and replace in there (maybe with an if function or something), exporting it afterwards. Then set the sequence to run after hours.
"It is the mark of an educated mind to be able to entertain a thought without accepting it." - Aristotle
Intel i5 2500; AsRock Z77 Extreme 4; Asus GTX580; 4x 2GB DDR3 1333; Intel 520 240GB SSD + 2x WD 3TB + 2TB Samsung; Samsung 22X DVD/RW; 23" LG W2343T-PF; Huntkey 700W
Intel i5 2500; AsRock Z77 Extreme 4; Asus GTX580; 4x 2GB DDR3 1333; Intel 520 240GB SSD + 2x WD 3TB + 2TB Samsung; Samsung 22X DVD/RW; 23" LG W2343T-PF; Huntkey 700W
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
bump
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
-
- Resident Drama Llama
- Posts: 967
- Joined: 05 Mar 2008, 02:00
- Contact:
Re: Help with cloning entries accross databases
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
........................................................................................................................................................................................................
Last edited by -Prometheus- on 04 Apr 2011, 03:48, edited 1 time in total.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Help with cloning entries accross databases
+1 Prometheus (AKA the thief who stole fire - may your liver regrow for eternity).... The questions posed by Prometheus is valid. However if you want to do it for whatever scaly reason you have one of three options..... 1) Triggers - Which personally I won't do. 2) SQL data replication - probably the best depending on how quickly you need the data to be accessible. 3) execute the DML query twice but each time on a different database.
But still I would like to know why you want to replicate the data to an identical database on the same machine. If it was on another server you could justify it for a data cent re or something.
But still I would like to know why you want to replicate the data to an identical database on the same machine. If it was on another server you could justify it for a data cent re or something.
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
Ok. I have created 2 sites. An intranet site and a website. They both run of the Joomla cms with the sobi2 cck component for storing certain information. Now all the information will be entered on the intranet site, but the client wants some of it shared on the website. I have explored all the options and replicating the information is the best and easiest one I could come up with.
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Help with cloning entries accross databases
Errr how about security rights on the same site ?
One website... 2 roles... have internal users assigned to one role and external ones to another. Or better yet don't register external people and then just don't give them access to sensitive information.
One website... 2 roles... have internal users assigned to one role and external ones to another. Or better yet don't register external people and then just don't give them access to sensitive information.
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Help with cloning entries accross databases
I have a PHP script which will move all tables and their contents from one database to another - PM me your mail address if you want and I can mail it to you, but be warned that although the script works on the DB's it was written for, I did not write it and cannot help you edit the code to suit your requirements...
What is wrong with a job that creates a mysqldump of the tables you want, gzips it and copies it to the other server where it can then be restored?
I would avoid using replication if your DB is mySQL, it's not the most reliable out there... it's idea of replication is to send every insert update and delete that happens on the master to the slave as a separate text file that gets executed sequentially... so rudimentary it hurts.
What is wrong with a job that creates a mysqldump of the tables you want, gzips it and copies it to the other server where it can then be restored?
I would avoid using replication if your DB is mySQL, it's not the most reliable out there... it's idea of replication is to send every insert update and delete that happens on the master to the slave as a separate text file that gets executed sequentially... so rudimentary it hurts.
![Image](http://img808.imageshack.us/img808/1797/facezu.gif)
-
- Registered User
- Posts: 4754
- Joined: 06 Aug 2003, 02:00
- Processor: PHENOM II 945
- Motherboard: Asus M4A78
- Graphics card: HIS ICEQ 4850 1GB
- Memory: 4GB CORSAIR XMS II 1066
- Location: , location, location!
Re: Help with cloning entries accross databases
Ruad...unfortunately the client wanted 2 sites. The intranet site is actually very complicated and the website is very simple, with only a small section needing to be shared.
Thanks jammin. You have pm
Thanks jammin. You have pm
"Every normal man must be tempted at times to spit on his hands, hoist that black flag, and begin slitting throats."
- H. L. Mancken
- H. L. Mancken