Should I move database from Access to MySQL?

Anon

As part of my job, I am the webmaster and database administrator for my department. My department is a small support group (18 people) in a company of 1200 people.

My site on the company Intranet serves basically for two purposes. The first is to speed up the work of the department by making needed information easy to find. The second is to allow other groups in the company to track the progress of their requests as they go through our department.

Currently these two purposes are accomplished by storing info in an Access database and then dynamically displaying on a Win 98 machine using PWS. The static content for the site is displayed on the company Intranet Apache server and then there are links from there to my Win 98 server. When I began with the database and website two years ago, I did not expect it to evolve into what it is today. It is still not a major site, but it does get 20 to 50 hits per day, and it is relied on by the department as an information resource and for creating management reports, etc.

The result is that I spend a lot more time programming than I did originally as I add functions to the site. I am looking for ways to make my life easier as the administrator. One of these ways was moving to PHP from ASP about six months ago. PHP integrated easily with my existing PWS/ASP architecture, and I have been slowly converting legacy ASP code. The result is that I can offer better functionality to my users with less time commitment on my part.

I would like some advice on the costs and benefits of moving my database to MySQL. My impression from reading about MySQL is that it would make dynamic database access from PHP easier to program. My initial thought was that I would try out MySQL for Windows to see if I liked it and then consider moving my whole database to the Unix world (Solaris) and the Apache server. However, my first impressions of MySQL is that it is very user-unfriendly. I'm not afraid of command line interfaces, but I miss the GUI environment of Access. In fact I find it very hard to even begin constructing a complicated database or porting my existing database from Access.

Am I going about this the wrong way or should I even be attempting the move to MySQL at all? I would appreciate any input. I can offer more specifics about my database if needed but I feel like I'm getting long-winded already.

Thanks in advance.

Anon

Do not worry about the command line interface it is very easy to learn and quick to use. If you want a powerful "gui-like" environment try, phpMyAdmin, available at http://www.phpwizard.com/

phpMyAdmin is one of the simplest interfaces I have used. Just this week, I began working with its import feature. I was amazed, it imported my text file into MySQL the first try and later that afternoon, I was viewing and editing the dbase from my web page. Beats the hassle of a spreadsheet that I was using the day before.

One recommendation, you can still access your Access dbases using ODBC and begin to design you site around PHP and the as you become comfortable with PHP you can create and design your MySQL database. The porting from that point on should be minimal.

I am still new to PHP/MySQL, but have enjoyed every experience with both. This are just my thoughts and I hope someone more experienced has some positive answers for your question.

Anon

I started doing exactly this just today. I originally had a program written in Delphi, but I was having problems with sharing database access on a network (I didn't have the time to program a client/server based program, I was just accessing the database files over a network share). So I started looking at other options, and I thought about a web-based application, based on my little experience with ColdFusion. I decided to look at PHP, since it is free, and I can use the same code on both Windows and Linux.

After I re-created my entire delphi application (which took me about 2 months) in 2 weeks (with no previous PHP experience) I was hooked.. I started with MySQL, but I had the same problem, didn't like the idea of doing it text-based, so I went to ODBC/Access.

Since then, I have started working for a company as a PHP programmer, and am forced to use MySQL since we're running a Linux server. With phpMyAdmin (www.phpwizard.net), I get a very nice graphical interface that I can access from anywhere.

Today, I installed it at home, and started porting my access database to MySQL. It took me about 30 minutes to do the 15 or so tables. I don't have any real data on my development system, but I can get text files from access pretty easily, and I've imported text files into MySQL before, and it works pretty well.

My biggest problem was converting my odbc code to mysql code (odbc_pconnect -> mysql_pconnect).. the longest one was odbc_exec($cid, $query) to mysql_db_query($databse, $query, $cid), and any calls like:

$result = odbc_exec($cid, "select..");
$var = odbc_result($result, "field");

which had to be changed to:

$result = mysql_db_query($db, "select..", $cid);
$row = mysql_fetch_array($result);
$var = $row["field"];

One of the other great things about MySQL is that I can get structure dumps (phpMyAdmin helps with that) that can be used to create the tables. My program is modular - modules can be added on and automatically recognized, but since I was using Access, I had to manually import tables into the Access database using MS Access. Now I can import the tables into MySQL using the command line (ie from an install program) without any user intervention.

It's a switch that is definatly worth the effort, since now I have the multi-platform option again (which was one of my original reasons for switching) and MySQL is faster and cheaper to implement than Access.

ttyl, greg

Anon

Hi,

I think DB on Access is not good choice.
Access is very slow than SQL servers and is not reliability.
MySQL is cool and fast SQL server but transaction data processing is not supported.
ORACLE ist best and fastest SQL server with many options and functions.
If you can, choose Linux + ORACLE + Apache.

whatever Linux = free
ORACLE 8i 8.1.6 Enterprise for Linux = free
Apache = free
PHP = free

This is best and chapepest option for Intranet based applications.

Anon

I appreciate all the advice.

While speed and reliability are important, they are not my main concern, since I am only getting 20-50 hits/day.

My primary concerns are functionality and ease of programming and administration. By functionality I am thinking not only of the database itself but also of its integration with the website--the ability to run queries and edit records from the web. Ease of administration is what originally led me to Access and ASP. Since I was already familiar with Access and Visual Basic, the learning curve was fairly easy and I was able to produce a workable product in a short time. But as my website grew, I found that I was limited by Access/ASP in adding functionality and that I had to go to great lengths to program ways around the obstacles, thus offsetting the original time savings.

Going through the learning curve on PHP was well worth it just for the added functionality (e.g. regular expressions, reading in a file from a URL, context-sensitive variables). Also, I like the general structure and syntax of PHP better than ASP. I am willing to go through the same learning curve on MySQL if the payoff is worth it. Any pointers offered on specific advantages of MySQL would be helpful. Also, am I losing any of the benefits by trying it out on Windows and PWS first?

Thanks to Tony and Greg for suggesting PHPMyAdmin. It sounds like it might be what I want.

Anon

I had no problems during my brief experience with PHP on IIS. I have never used PWS but from other readings, it sounds like you will have any problems. The installation of IIS was much more, should I say, "ambiguous" than a Linux install but that might be my own experience. But I don't foresee any problems using PWS. PHP and MySQL will be a good experience.

Anon

whatever Linux = free
ORACLE 8i 8.1.6 Enterprise for Linux = free
Apache = free
PHP = free

Oracle free? I found on their site a price tag of $200.

Am I wrong?

Alain.

Anon

Go to http://www.oracle.com click on downloads, if you haven't registered with Oracle do so, then at the downloads page select, 8i Enterprise Edition

Then you will be able to download the Oracle*i Enterprise Edition for Linux, approx 285 meg download.

Anon

Iam registred at Oracle, yes you can download Oracle but it is only for trying.
You can not use it oficially
Is it true?

Rafael

Anon

The downloads section has both the Enterprise Edition and the Standard edition available. It looks like the Standard is free but the Enterprise is try and buy.