PHP, MySQL, Online and Offline

hey guys,
could anyone throw a little light to my problem,  

creating an offline application and an on-line application, when the sales agent is on the road without access to the internet they can enter the information about the customer and the order into the system.

When they come back in the office, i want them to sync the information back to the server and download the latest copy, what is the best way of doing this without the record key duplicating, as record 1 might end up being entered by lots of people.

Many Thanks.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would start by setting them up with differing start IDs each morning for each person so that no keys are duplicates, base this beginning key on the last inserted id currently in the online database. Their local tables (offline) would then be turned into insert statements for the online database at the end of each day.  You would need to make sure you have more than enough IDs for each person, or you will end up with duplicates.
Create the PHP application and set a constant containing the address of the "Central Server".

Install Apache/MySQL locally on each agent's laptop.

When they execute the application on localhost, they see the "Readonly Live Interface" and the "Temp Insert Interface".

The Temp insert interface stores customer/order information in temp tables with temp primary keys.

When the agent gets internet access, they click their "Sync" key on their Temp insert interface and:
1) Their localhost communicates with the Central Server and sends all the temp customer and temp order information
2) Their localhost syncs its permanent records with the records of the Central Server
2.5) [You may add extra checks here to make sure you received at least all the records you sent]
3) The localhost deletes its temp data

The agent is now ready to go mobile again.

The "temp-to-permanent" transfer and the "syncing-from-central" part can either be implemented with POSTs or with the use of a Web Service.

Good luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amar BardoliwalaCommented:
Hello Benji,

I do not know if I understood your problem well.

But, one solution could be as following.

You can have synchronization_id in offline table.

Synchronization_id will empty initially.

When you enter values in online db, synchronization_id will contain primary key value of online table for that record.

So later you can retrieve data using synchronization_id for each offline record.

Hope this will help.

Thank You.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.