Databases on web sites – some basic questions

Topic

New Reply

I have been doing some research over the weekend and feel as if I have a 5000 piece jigsaw puzzle in my head and no idea of how to put it together, but I do know what picture I want …

I want to develop a database of all our joggers’ race results that is searchable by name, race etc and I want it on our club website.

I’m OK with HTML and CSS and static sites, but I want to go one step further. I have not been able to get answers to some of my, seemingly, basic questions so I thought I’d post here (‘cos I can’t hear you when you laugh at me).

My hosting is Linux based so I am assuming that I need to use MySql for the database structure. I am allowed a number of databases with my hosting. Do I need to download MySql to my machine, make up the database and then upload it? And if so, which version of MySql do I download?

I will be adding race results on a weekly basis, so do I update my copy and then upload again?

Is there a basic step by step procedure for this sort of thing that someone can point me to?

Reply | Quote

Replies

My hosting is Linux based so I am assuming that I need to use MySql for the database structure. I am allowed a number of databases with my hosting. Do I need to download MySql to my machine, make up the database and then upload it? And if so, which version of MySql do I download?

It depends. Some hosts already have MySQL on their servers and offer you a control panel to set up your database. In the case of shared hosting accounts with 1&1 Internet, for example, MySQL databases are hosted on a completely different server, and you hook into them from your pages (e.g., PHP pages) using information provided by the 1&1. In the case of other hosts, you may have an application control panel from which you can install MySQL by pushing a button. Either way, the database will live online permanently, and you will input data over the web. Perhaps you might be able to import an XLS or CSV file that matches the layout of the database so you don’t have to enter the data manually. I’m not sure as I’ve never had to do it.

There are other databases you can use on a Linux server. Lots. One popular one is SQLite, which does not require a separate database server (or even a database management system running on the same server). SQLite databases live on your web server and are readily accessible from PHP. See Appropriate Uses For SQLite. SQLite has an offline browser you can use to edit your databases, so you could populate and update them location and then upload them. See SourceForge.net: SQLite Database Browser. (I found a 3 part tutorial on using SQLite with PHP; as I hardly know any PHP, it is not clear to me how well this covers the types of operations your application might need to perform: Introduction, Unbuffered Queries, Additional Methods.)

Let’s say, hypothetically, that the above is too complex or too much of a hassle, and you have a budget for this project (big assumption!). You could take a look at some subscription-based online databases that make the whole process a lot easier. Examples: QuickBase and TrackVia.

Reply | Quote

Ok …

I do have a cPanel control centre and that lists MySQL databases. I will have to have a closer look at that and see what it can tell me about setting up my database.

The raw data that I have is currently supplied to me as .xls files so I shouldn’t have any trouble converting and / or populating.

And budget?? What’s that? Half joking. This is for a running group that relies on people donating time to get things done. So in those terms, the budget is only limited by my enthusiasm. I will still investigate those options you mentioned. Thank you.

Johanna

Reply | Quote

Johanna,

You may wish to consider installing XAMPP on your PC. This is free and will allow you to develop and test your PHP code and your MySQL database locally before you upload it to your server.

Even though there may be a web-based utility (such as PHPMyAdmin) on your server, I have found SQLyog very useful. This will allow you easily to view and update your MySQL database both locally and on the server, and to upload your tables or your data to the database on the server. You can download the Community Edition of SQLyog for free; this will probably give you all of the functionality you need, at least at first. I had to buy the single-user non-commercial Enterprise version (USD79) to allow me to access the database on my server, since my provider does not allow direct access; the HTTP Tunneller in the Enterprise version gets around that.

In my view, although these tools may at first appear complex, they are not difficult to use, and indeed greatly simplify development of the code and tables.

Good luck!

Chris

Reply | Quote

Thank you Chris,

That has given me some more to work on and think about.

Johanna

Reply | Quote

Ok …

I do have a cPanel control centre and that lists MySQL databases. I will have to have a closer look at that and see what it can tell me about setting up my database.

The raw data that I have is currently supplied to me as .xls files so I shouldn’t have any trouble converting and / or populating.

And budget?? What’s that? Half joking. This is for a running group that relies on people donating time to get things done. So in those terms, the budget is only limited by my enthusiasm. I will still investigate those options you mentioned. Thank you.

Johanna

The cPanel should give you direct access to your databases too, either via phpMyAdmin [which is good] or webmin [which is less good] or perhaps something else; these usually have an upload from CSV option, so you can use Excel to save your xls files to CSV and then upload without needing a local copy of MySQL. If you do want a local copy though, I also would recommend XAMPP.

Reply | Quote