How to interact with a foreign key in database?

Updating MS Access Database with MySQL database, and vice versa

• Ahoy, This summer I will be working on my Senior Project, in which I will be developing a database driven website to help automate the registration of volunteers for Magnolia Music and Events. I will be using PHP and MySQL to develop this software. However, the folks at MagMusic are hooked on Microsoft Access, and use it for their database needs. They would like my system to interact with Access in some way. I am looking for some solution to this problem, or some advice on which direction I should go. With some googling, I've found some products that claim to convert to and fro, but I would need something built into my website, so that the people at Magnolia Music and Events could easily update their Access database, and update the online MySQL database with new information. The online database would recieve information from hundreds of potential volunteers, so up until a festival, the MySQL database would be changing a lot. After a music festival, more info would have to be entered into the database, regarding the volunteers' performance, and other relevant info. This could either be done by updating an Access database and uploading it to the MySQL database, or through web forms. So, what are my options? If there are any algorithms or free PHP scripts, that would be helpful. Just some general advice from an experienced web database programmer would be greatly appreciated. I'm very available for clarification.

• Answer:

  Hi, Wow, I?m pretty sure this is the first question I?ve taken on where I have way too much to give you. Heh.. so I?ll put your own words back to you first, I?m open for clarification if you need it. I do this type of thing all day long, so I might speak ?above? you a bit, but I?ll try not to. Again, if you need to, just ask for clarification. Alright. First off, I think PHP is probably the most documented language out there on the web , especially with regards to connecting to MySQL. Perhaps Perl might be a squeak ahead, on a given day if someone?s main document serer is down, but that?s about it. Also, you have at your fingers a huge assortment of libraries to use to save you a great deal of time. The one I will point you too is Pear. Or the Pear System. http://www.pear.org PHP has a great collection of functions to work with MySQL directly, http://www.php.net/manual/en/ref.mysql.php In the Pear DB library you will find classes that access MySQL with exactly the same functions you would just ?use? anyway.. for example you might find functions that will look like these: function db_num_rows( $qid ) { return mysql_num_rows( $qid ); } function db_fetch_row( $cur ) { return mysql_fetch_row( $cur ); } function db_fetch_assoc( $cur ) { return mysql_fetch_assoc( $cur ); } function db_fetch_array( $cur ) { return mysql_fetch_array( $cur ); } As you can see, all these functions are doing basically is changing the name of the PHP native function. So why am I pointing you to Pear? Really, or at least because of, the type of thing you are facing right now. Trust me, it won?t be the last time some one wants to change a core object on you. If we wrote the whole program using the mysql_functions in our code, then our code is sprinkled with calls we will have to hunt down and alter, by hand, causing many many bugs, when someone wants our program, but needs it to work on .. say, Oracle or DB2. The SQL is basically the same, but the calls are all different. But, if we have all these in a single file, and have called these included functions throughout our program using the db_functions instead, all we have to do is make a file which does the same thing, using Oracle calls. We also now have a new library so if someone askes us for the same thing, we have both. Pear, has already done this for you with several databases. The DB library in Pear talks to just about every database out there, and a few that aren?t out there any longer. So definitely check that out. On top of that, its all object calls, which makes the coding much faster (at least I think it is, your milage may vary there). Now, about this MsAccess. thing. You are in luck there as well. We will use ODBC for that, or in this case MyODBC, I?m not much of an Access fan, but I have to say this set up is pretty cool, and I?ve used it myself several times. http://www.mysql.com/products/connector/odbc/ What we do with this is set it up on the clients computer, and then, using Access, make Data Links to the MySQL database. Now, both Web users, and Office users can use the same set of live data, at the same time. No downloading, uploading, or any of that other nonsense. For them, they are working exactly as they are use to working and your code continues with no changes at all. Since they probably know what information they want, and probably already have al the data files, the best way to go is have them export the database schema and use that to create your MySQL database. That way they can use any forms and report they have already created. The database schema is simply an SQL statement set that creates the database for you. The last time I setup the ODBC (in fact, every time) I found it to be a bit tricky. I had to read the instructions several times (even though it is very well documented) and play with it. Be prepared to be a bit frustrated by the time you get it to work. Once you have it though, its as solid as a rock. Its just getting the settings right. As soon as you have that, making the data links to the data tables is simple and from there the users in the office will never know they are not working with Access directly. Database security. When you set your permissions on the MySQL server for this setup, you are going to have to be very specific. For your website your grant line is grant all on mydatabase.* to myuser@localhost identified by ?mypassword?; for the ODBC connections, you need their IP address. This will probably not be the address of their machine, but the address of the router between them and the Internet. This line will look like grant all on mydatabase.* to [email protected] identified by *.my_odbc_password? You can do this with as many machine IP addresses as you need to, but make sure you do each seperatly, and each having its own username and password set. This username and password set is used by the ODBC to connect, so the user actually using the database, will never need to know it. So, make the passwords as cryptic as you like (the more the better). This is a nice setup, because even if someone happens to find out the username and password, they still have to be accessing the data base from that IP address. Make sure you keep these written down some where incase of problems, so you can use the MySQL logs to help find out where those problems are coming from. Most PHP installs have Pear installed by default. So no worries there. Another PHP package you might want to look into since you have a bit of time before starting, is the Smarty Template Library. This is a very cool system. I just started using this last year, and man I wish I had it for the previous five. I weep at the time I could have saved and the hassle of changing simple things for users. A template library separates the design of the website, from the code of the website. http://smarty.php.net What this means to you as the programmer is when they, the customer, wants to change something, such as moving the menu bar from the side to the top, or adding a menu bar to the top, or changing the page flows or changing the way the links look like, or anything. You change the template and you are done. No coding changes. If they, the customer, know how to create pages in Dreamweaver, instead of having to go through all the Dreamweaver files, altering links and adding code headers, you just let them, and even give them the set of template keys they can use. They can change it to their hearts content. Won?t bother your code at all. They wont? even see it. Smarty is just one of several hundred template libraries out there. Again, we?ve seen this coming just like we saw the database changes coming. But, Smarty has a few more advantages to it that others don?t do as well (in my opinion anyway). First, the templates are compiled. What this means is they are much faster on the web than non-compiled. PHP, like Perl is a Scripting Language. C++ would be a compiled language. So, when a user calls your script on the webserver, the server has to get your file, compile your file, and then show your file. Smarty, using a good template engine, takes out this middle man. The setup is a bit weird, and it takes some time to go through the documentation and some tutorials on their website to get the hang of it but once you do, it really starts saving time. And, my clients love it. Okay, I think I?ve given you plenty to start with. If you think I might have left something out, just let me know. thanks, webadept-ga