# Whats the best way to link a database?



## zynizen (Nov 21, 2006)

Hello.

I'm somewhat a newcomer to database scripting for the web. I've developed many websites using plain html/css/basic javascript and I can manipulate pretty much any code if I have samples in front of me.. 

my question is this: I'm going to be re-designing my company website, which currently is using a microsoft access .mdb file for the database and linking to it from asp code.

which is the best way to convert this database to lets say, file maker pro, or something I can use a little easier to work with in terms of layout and design in a program like dreamweaver or something else?

I want to be able to redesign and give the website a fresh new modern business day look and still keep the database functions there. I have until Feb.20/07 for completion so, I would just like some input on what route to take for anyone who can give me some solid advice.

I appreciate all responses very much.
Thanks.


----------



## mdnky (Nov 21, 2006)

For online use your best bet is MySQL for a DB and PHP for any scripting needed.  Filemaker (and its associated components) is expensive in comparison and not as well documented or widely used.

Conversion from the Microsoft Access DB isn't horribly hard, but it won't be easy either.  There are programs out there to assist with such things, but they won't do everything.

I'd suggest getting a copy of the MySQL Handbook and maybe other books on it, then reading up.  

(Some links...commercial and non, but worth reading for clues or potential sources of help and apps.)
http://www.kitebird.com/articles/access-migrate.html
http://dbconvert.com/pr/access2mysql.pdf
http://www.download.com/MySQL-to-Access/3000-2065_4-10464363.html


----------



## zynizen (Nov 22, 2006)

thanks!  actually, how long would it take to basically take a website developed in asp using mdb database, and redesign it to php using mySQL ?

like i mentioned above, I have a project due Feb 19/07 lots of time, but I do not know php, I've had some database classes in school related to database structure and such but no real programming knowledge of it.

would this sort of thing be easily understood from a few books, like teach yourself mySQL, and PHP in 30 days?

thanks again


----------



## webMechanic (Dec 6, 2006)

Can I suggest you take a look at Ruby on Rails(RoR) if you have a significant amount of dynamic database interaction in your app. 

This is new(hot) technology(open source so it costs nothing), but coming from a client/server background I can tell you it will abstract many of the database(db) access/update details and let you concentrate on the user view/interface. This is a 'framework' technology meaning that it basically creates a functioning, skeleton application 'out-of-the-box', and you program/configure the details to meet your user requirements. It is based on the MVC(model/view/controller) pattern so the framework partitions the code in these domains making for very easy web app design/code organization/req'ts definition.

So with a broad brush, you would take the Access datafiles and define these tables (assuming they are modeled appropraiately) in a SQL database. MySql integrates VERY well with RoR, and if you are building the app on the Mac, I would suggest looking at FrontBase(also free) as it is widely used on Macs and is a very user-friendly SQL db. Then you would import the Access data into your new SQL tables. This gives you the MODEL piece of the MVC framework. From here you would deploy RoR, incorporating your tables in Rails, and the framework would build (using the scaffolding option) an app that provides database CRUD(create,update,delete) functionality for any/all(you decide) your tables. Your web design/graphics/functionality/navigation work starts here.

Take a look at http://www.radrails.org/ and wiki.radrails.org for more details. I realize you have a short deadline, but database access can be difficult in a web setting and this technology can get you over this hurdle.
If interested further, I suggest you take a look at "Agile Web Development with Rails" by D Thomas on Amazon - it shows how fast a db based website can be built and is an excellent guide.

Best of luck.


----------



## webMechanic (Dec 6, 2006)

Here is another link that I did not have at my fingertips in my earlier post.

http://www-128.ibm.com/developerworks/opensource/library/os-ecl-radrails/

Ruby on Rails(RoR) does not have a GUI IDE toolset until just recently. That is what this link demos. So if you are a big Mac user, developing an application in a scripting mode as opposed to using a graphical toolset, can be daunting. But with this new development (a plugin for Eclipse), this limitation is eliminated.

However, I should mention that the open-source Eclipse IDE is a huge and powerful technology, and, as such comes with a substantial learning curve (I use it to develop WebObjects applications in Java so I know from experience). But although I cannot speak from experience, this new RoR plugin/framework should greatly simplify the RoR app development effort by putting a graphical face to the RoR tools. And like you said if you have experience coding Javascript/HTML/DOM/CSS, the Eclipse IDE should NOT be a major obstacle. At least I found that to be the case.

chiao


----------



## zynizen (Dec 6, 2006)

thanks for the information! I really appreciate it. So, actually my main obstacle is simply coding a page, getting a table in there, and having a search field, or a selection box retrieve the information in that database and display it on the screen below the selection box.

I know this sounds easy, but I have no experience whatsoever with php/mysql, I do know code, but, im afraid its been too long since Ive ever had to actually do something like this.  

What are your thoughts?


----------



## webMechanic (Dec 6, 2006)

You need to do a little analysis on the existing system and Access database. If this app is going to be mostly a read-only situation drawing from a few tables, then it would make sense using something like PHP/mySql. I don't have any experience here other than some cursory reading, but I believe it would very straight forward to use PHP as the server-side scripting tool to handle a POST, retrieve a dataset, and return the request to the browser that you would bind to a list object.

As a matter of fact if this is strictly a 'read/retrieve' only application, you should do a search on web-based 'content managment systems' that you can buy/use to customize for your circumstances. There are 'portal' type apps that I am familiar with that categorize and deliver any media type - stock quotes, merchandise listings/graphics, and even video clips in the technology I work in. 

On the other hand, if this app requires many tables (+15) with relationships, and there is inserts/updates to these tables, then this will get complex in a hurry and you best be quite expert with PHP.  In this case I would look into a framework like I mentioned (RoR) as it is designed to handle exactly these problems. Even RoR, I dare say, would enable you to create the scenario you described in 1 day.

So in sum, I would try to get a app/framework that provides a core functionality that you require and customize it going forward. Try to leverage off a proven application, rather that you 're-inventing the wheel'.

Hope this helps.


----------



## zynizen (Dec 6, 2006)

just another question related to this article, how would I ACTUALLY make a mySQL database? is it much the same as making a microsoft access database using ACCESS or do I need to do some different type of programming?

thanks


----------



## webMechanic (Dec 8, 2006)

I can tell by your question that you have a substantial amount of learning to do before you design/build this application. I don't mean this as a 'put-down', but rather to indicate that SQL databases are a whole different 'ballgame' that personal DBs such as Access. And will require a substantial learning curve before you should do much in the way of app development.

So what I would do is get a book on SQL programming and this will get you started. I have a book - 'Beginning PHP5 & MySql - From Novice to Professional' by Gilmore, which I thought was very good.

Now to answer you question, the creation of a database is very simple. You simple use the admin tool, and do a File\Create Database which sets up a dataspace for all your tables. This is where the know-how comes in - creating the tables, and then programming the table/data access from you app.

Hope this helps


----------



## Natobasso (Dec 8, 2006)

Why not just stick with asp?


----------



## webMechanic (Dec 8, 2006)

Asp would be fine also - it can perform all the server side coding just as well. I only mentioned PHP because it was mentioned as before and does integrate well with MySql.

But crucial next step is the design/selection of the datastore for the new app.


----------



## Natobasso (Dec 8, 2006)

And that's perfectly okay, I was just observing the workload increasing for the poor poster of this thread. 

By "design/selection" do you mean graphic design? Check out the site I manage (it was actually "designed" in powerpoint, ugh, by someone other than myself) which is based on an asp architecture: http://www.geniusproducts.com

We have an admin tool to update user defineable pages (very handy!) and I poke around on the webserver via ftp to update the css and asp code as needed using Dreamweaver.

Seems like he doesn't have to recode his site in PHP to get it to this point.


----------



## webMechanic (Dec 9, 2006)

Not talking graphic design here. I'm referring to database design - a rigid discipline that procedes the creation of any SQL database. It simply involves determining the user/cllient data needs (thru extensive dialogue), and then modeling the data (Entity Relationship Modeling) resulting in the actual tables created in the database. This step is generally considered the most crucial in terms of serving user info needs and from a technical coding standpoint (in terms of how easy data can be accessed/maintained).

I don't know what's the impetus for switching to another database (MySql was suggested as a possibility), but that decision should NOT be taken casually, as the price/effort involved is high. The drivers here should be performance - can current and future data needs be served acceptably, and, perhaps standardization (SQL technology is universal availing best price/performance). One can't argue this - see http://www.mysql.com/news-and-events/success-stories/yahoo_finance.html - open souce MySql powers Yahoo ! Finance.

If it were me, I would be dis-inclined to switch databases in this time frame assuming the data can continue to be served responsively. And concentrate on the key user req'ts.

If this is not the case, and you want to go the SQL route, go with Ruby on Rails(RoR). It handles the SQL database access/updates for you(yes - you don't have to code SQL), provides a great architecture for you that can scale to very large volumes, and does not limit your your site design efforts. To use a very apt computer programming cliche to help productivity - DRY > Don't Repeat Yourself. Others have worked this problem and there's a free solution for the taking.

Hope this helps


----------

