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?
From PHP to MySQL to ACCESS and back
Moderator: General Moderators
- 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
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.
You will have problems with what you describe above because you need polling on both computers -- sounds like a mess.
(#10850)
Re: From PHP to MySQL to ACCESS and back
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.
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.
- 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
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
http://www.softcoded.com/web_design/acc ... _mysql.php
(#10850)
Re: From PHP to MySQL to ACCESS and back
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.