REWARD FOR HELP: Mirroring Tables

tsaikkala

I have a website with over 35,000 users.

I have a table (called xoops_users) that holds registration details in a mySQL DB (called broadie_xoops).

I want to setup another site (thus another D😎, but don't want the users to have to register again. (the other sites db name is broadie_prog, and the table name is xoops2_users)

Is there any way for mySQL or with the help of PHP, to mirror one table in a database, with another?

As a reward, i'm able to offer free php hosting with the inclusion of a mySQL database.

dkeats

Hi,
I am new to PHP/mySQL, but experience with other environments suggests the following questions may need to be answered first:
1. Are the databases on the same server?
2. Can one be considered the master, or will updates be needed to be made to both and then they must be kept in synch?
cheers
derek

jayant

install PHPMyAdmin at both places.
export data of first db
then import it at the second place

simple enough 🙂

ps: when do i get my free hosting 😃

tsaikkala

Both databases are on the same server.
And i can't export then import with phpmyadmin because i would have to do that every day so that the user data gets sent over. I was after a way of doing it automatically with sql in a php query that gets executed with crontabs or something every hour.

TheIceman5

why dont u just use the same table?

drawmack

you can hook both sites into the same database.

It's either that or a complex cron job that will dump the new entries from both tables into sql files and then update both databases from the other database's sql file.

superwormy

I would name the tables teh exact same things in both databases.

Then, instead of using mysql_query(), I'd write my own wrapper function, say dbl_mysql_query().

Everytime I needed to do an insert or an update that needs to be done to BOTH databases, I'd use the wrapper function instead of mysql_query ().

The wrapper function would simply make two different mysql_query() calls, one to database one, and one to database two, thus updating both databases with one function call.

Alternatively, you could write a dump and load script in your language of choice, and crontab it to run every hour.

drawmack

I think dump and load is the better alternative otherwise an error writting to one of the tables could cause the databases to get out of sync which would be a major headache.

jayant

Option 1:
Execute the following 2 querries:
DELETE FROM broadie_prog.xoops2_users;
INSERT INTO broadie_prog.xoops2_users SELECT * FROM broadie_xoops.xoops_users;

first will empty the xoops2 users table, the second will insert all records.

Option 2:
just do the option 1 once.
and change the script.
where ever you have INSERT or an UPDATE query in for xoops_users

get it to also execute the same query for
broadie_prog.xoops2_users

simple 🙂