From PHP to MySQL to ACCESS and back

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

From PHP to MySQL to ACCESS and back

Post by rhecker »

We are buiding a web application that books reservations. An ACCESS database on a local computer cotains the reservation data.

1. When someone tries to register for a room, the PHP application populates the MySQL database.
2. Meanwhile, the ACCESS database "watches" the MySQL table for changes, when there is a reservation, ACCESS returns to the MySQL table a confirmation of availability.
3. Meanwhile, the PHP script is in a loop, checking the MySQL table for an update to the confirmation status. When the status changes or the loop times out, the response is sent to the browser.

If the above description seems like the most idiotic process on earth, then, well, that's why I'm here. What we need is a way to display to the applicant the response from the ACCESS database. Has anyone dealt with a similar situation and found an elegant solution? Any ideas?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: From PHP to MySQL to ACCESS and back

Post by Christopher »

Why not just move the "reservation data" to the MySQL database and build an admin interface for doing anything currently being done on the "local computer" ? Or if you must have a database on the "local computer" then switch to MySQL and do replication between the two database.

You will have problems with what you describe above because you need polling on both computers -- sounds like a mess.
(#10850)
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: From PHP to MySQL to ACCESS and back

Post by rhecker »

The problem is that I work with PHP/MySQL and the person who manages the reservations has a lot of investment in ACCESS. ACCESS has all the logic for processing the reservations and it's also used when reservations are taken over the phone, etc.

It isn't as simple as moving the reservation data to MySQL because a lot of VBA programming has gone into building the ACCESS system, which calculates costs and availability based on a wide range of variables.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: From PHP to MySQL to ACCESS and back

Post by Christopher »

Then you will be making the mess worse, but it can be done. I would recommend making the Access database the master. You will need to use ODBC. Here is an article that might get you started:

http://www.softcoded.com/web_design/acc ... _mysql.php
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: From PHP to MySQL to ACCESS and back

Post by califdon »

As someone who has taught and worked with both Access and MySQL, both online and standalone, for many years, I agree 100% with Christopher. It comes down to this: you can either explain to the person who manages the Access database that coupling it with an online MySQL database is simply impractical and offer to duplicate all the logic and capabilities of the present Access database in a single master MySQL database, or you can spend months of frustrating work which, I guarantee you, will lead to failure. I am not saying that it's impossible, I'm saying that it is such a huge, complicated project that one developer is unlikely to ever complete it successfully, and if he did, it would never be worth the cost.
Post Reply