Totatlly new - Acess DB/Coldfusion to MySQL PHP

[raskal]

Hello all!

First off, this is my first real dive into coding so please be gentle! Ive been thrown a new project and I'm not even sure where to start, so let me give you some background.

The project is for an aftermarket parts manufacturer. They produce soultions for a wide array of makes, models and years. Currently the system they have in place is they have a Access database with the forms of make, model and Date- from year to year. so the output would be

Ford - Focus - 1998-2000

This is causing a multitude of problems as some are mid model years with different suspensions. I suggested they move to the standard YMM ( Year / Make / Model ) structure.

I am more of a designer and I use mostly linux hosts, so I suggested we migrate the Access DB to a MySQL database.

The ultimate goal is to be inserted on a page have 3 drop downs, Year, Make Model , and have them sort as they go down. So if a person chose a 2001 Then Ford, they would not see a model that was produced only in 1980. From there the results would be shown on a page that listed the parts available for that particular combination. The person could then click on the part number and be transferred to a detail page of that part number.

For an example - please see the LIVE SITE (http://www.spcalignment.com)

First off, Is this same "structure" available in SQL
Second, if so, is SQL a good match for this type of job
Thrid, how easy would it be to add new apps and create detail pages

Forth, if all above seems ok...where do I start?!


I thank you very much for your time and any suggestions.

[Old Pedant]

Ummm...

First off, Is this same "structure" available in SQL
Second, if so, is SQL a good match for this type of job

SQL is a language. Access uses SQL. MySQL uses SQL.

I assume you really meant to say "MySQL" in place of just "SQL"???

Yes, MySQL can certainly do most anything Access can do, though there are often subtle changes in syntax for certain things. (One example: Access queries specify dates as #mm-dd-yyyy# whereas MySQL uses 'yyyy-mm-dd'. But probably irrelevant to your problem, as you would just use an INTeger field for the year. No?)

I would like to point out that there's no reason you can't use the Access DB with PHP, so long as you host the PHP on a Windows server. If migrating the DB isn't a chore you are ready to tackle, this might be a reasonable compromise.

But I do have to ask... If you don't already know PHP, then why use it?? It would almost surely be *TONS* less work to modify the CF pages than to convert them all to PHP. Likely you are talking about relatively minor tweaks here and there.

The bigger problem is the DB design: You should *NOT* be using a single flat table, as appears to be the case now. Instead, you should have a many-to-many table that links each one-or-many YMM values to one-or-many product entries.

I would *definitely* tackle the DB redesign first. No matter if you opt to continue to use Access or migrate to MySQL. I will say that if your client is used to simply updating a desktop Access DB when new products/models are supported--and then presumably uploading the new DB to the server--then they are not going to be happy with you changing them to MySQL where they have to learn a whole new set of tools and interfaces. (If they use a web interface to make changes in the DB, then you could make the DB change transparent to them, but again you are letting yourself in for more work.)

Quite honestly, for the very small changes you are proposing, I would stick with CF and Access. Unless you are already expert at PHP and MySQL, the learning curve for you is going to be about the same and you will have much less work to do.

[raskal]

Thank you VERY much for the reply! I guess the real reason is as stated I'm more of a web designer and most of my applications are linux based and don't play well with IIS, I figured if i could get it into a MySQL/PHP format and run on linux we could consolidate. I sort of had this dropped in my lap, and I'm eager to learn. Im going to look into redesigning the database first like you suggested.

Thank You!

[Old Pedant]

Okay, good luck.

But *do* ask your client how they currently update the database when new/changed products are needed. Again, if it's a web interface, you will just have to transform it to PHP ("just"! ha!). But if they are used to simply using Access on their desktop to change the DB and then upload it to the site...ugh. You are going to be fighting a much bigger battle to get them to change to MySQL.

[raskal]

Okay, good luck.

But *do* ask your client how they currently update the database when new/changed products are needed. Again, if it's a web interface, you will just have to transform it to PHP ("just"! ha!). But if they are used to simply using Access on their desktop to change the DB and then upload it to the site...ugh. You are going to be fighting a much bigger battle to get them to change to MySQL.

Unfortunately they do as you say, download the Access DB, add a line, re-upload. Yay for me!:rolleyes:

Thank you again for your help!

[Old Pedant]

That's what I was afraid of.

So now you have two choices:
-- Create all the capabilities they need to add/delete/modify the contents of your MySQL database, using PHP pages.
-- Go ahead and use the Access DB. Use PHP on Windows and it can easily reach the DB.

There *ARE* some tools for PHP out there that allow you to port Access DBs to Linux. I have no idea how good they are.

Another alternative: *IF* you can convince them do do so, have them export the Access DB table(s??) to a ".csv" file and then importing the CSV file into MySQL is pretty easy. In fact, there's a builtin CSV driver in most MySQL installations.

Personally, if you really want to use PHP, I'd opt for running PHP on Windows. You could even use an Apache Web Server, if you wanted to. There's a really simple-to-use package called XAMPP that has everything you need to run PHP on Apache on Windows. (It also includes a lot more of the open source tools you might be familiar with, including MySQL, so you can use them or not as you wish.)