Upload database - feature 1

[ani77]

Hello,

I am first time doing a website in PHP & Mysql.

I have to setup a website in php using mysql as backend. One of the feature that my client requires is making entries and modifications locally and then when he says 'Upload Database' it should be uploaded to his website.
A duplicate of his website would be set up locally.
For implementing this feature I have planned out the following steps -
1. Once the client finishes updating his database locally then Select all the tables from the database.
2. For each table select all the rows for which modified is set to True ( I will be having a logical field called Modified in every table which would be set to .T. if the record is modified)
3. Update the selected rows to the respective tables to the database on the online webserver.

I want to know whether I am on the right track or not.
Mysql doesn't support commit/rollback transactions.
Does PHP has this feature ?
How will I ensure that when my database is getting uploaded, either the process gets completed successfully or fails totally. No in between situation.

Any suggestions or guidelines are welcome.

Ani

 

[danielhozac]

Why not have the client connecting directly to the real database server?

Note that this reveals a lot of security issues, so this probably isn't what you want.

If this isn't possible, then how would the client be editing the tables? Through a local PHP installation or with some program? //Daniel

 

[ani77]

Hello,

Client can connect directly to the real database server.
But if he updates the tables locally and just once say 'Upload Database' he would be saving a lot of internet hours. He can change the records offline.

At the client-end PHP & Mysql would be installed locally.

thanks for ur response.

ani

 

[danielhozac]

I guess you could have the local PHP installation connect to the two MySQL servers updating the remote one with the data from the local one.

You could also use the mysqldump program to dump the whole database and recreate it at the remote server. //Daniel

 

[ani77]

Hello,

Thanks for the response.

Does the mysqldump program would be the best option,if the no. of tables and records is large ?


ani

 

[danielhozac]

Well, it would probably be faster than having PHP looping through all the tables and updating it that way. //Daniel

 

[ani77]

Hello,

How I would be calling the mysqldump prog from my phpp file??
kindly guide.

ani

 

[skiflyer]

I'd put forth a third option... depending just how much editing you're dealing with percentage wise. i.e. if he's modifying 1% of the data (as I imagine most people usually are), both of the above methods are fairly innefficient.

My approach would be that everytime you execute a MySQL call locally, you also dump that command to some sort of buffer. I'll assume a text file would be more than sufficient.

Then, when he clicks update database, you just execute those same commands on the remote server.

<-- Random babbling below -->
If you want to get fancy you can do some processing on the buffer and make sure you're not doing something akin to
$a = 1
$a = 2
$a = 5
$a = 6

and just take the last command relevant to a field, obviously being mindful of all the rules you have set in the database. Making a dependency table would be an absolute necessity if you wanted to go that route, but odds are the few seconds you save on the database won't be worth the hair-pulling you go through to implement that feature, so I would just execute all the commands verbatim from the buffer.

-Rob

 

[Hokey]

I have done something similar with one of my clients ... don't know if it will meet your needs, but here is what we did:

1) wrote the PHP/MySQL application for the web
2) wrote an MS Access database app that included the administrative features and an exact db structure of the MySQL tables. (with some additional tables only used for administration)
3) in access, we wrote a query/routine that the client could activate from the switchboard. This routine selected all the records in each table that have been modified since date X, then wrote all the MySQL SQL queries to update the changed records ... then saves the sql queries in a text file in a per determined spot on the clients local pc.
4) We then had the client visit their websites administrative pages ... one of the administrative tasks uploads the SQL queries file and runs them (mysql_query());

It seemed to work fairly well, but was written as a one way transfer (database only changed by client, and only viewed on web), but could be written to go both ways with a little more coding

 

[sleipnir214]

Is anything changing the data in the foreign (non-local) database? That makes it a lot harder -- you have two-way replication and concurrency to think about.

If not, as skiflyer said, the buffer idea is a good one if the client is updating a minority of the records of his copy of the database.

Make the buffer another table, and dump into it, in order, every SQL query you performed on his copy. Then transfer that table, and perform the updates in the same order on the foreign copy.


If he is editing a majority of the records, you could copy the files that make up the tables in the local copy, and overwrite the foreign copy with them. It's brute force, but it would work. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[ani77]

Hello,

Thanks to everyone !!

I also agree that skifyer solution sounds good.
Now only I am concerned how to ensure consistency of my remote database.

I mean when I am executing the commands on my remote server, how to ensure that they would be executed in whole or not at all ??

ani

 

[sleipnir214]

That is a problem. I don't know of any way to insure that this is true.

What you could do is have PHP backup the non-client-local database before doing any update. You should be able to copy the table files to another location without shutting down MySQL (at least on *nix).

If any query of your list fails, have PHP yell for help with lots of explanation of exactly went wrong -- someone with permissions to shutdown MySQL and revert the database to the backup. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!