Req advice on handling large data tables

[bovril]

Hello,

I would really appreciate some advice on how to deal with large tables of data on my website.

The website is www.leagueofmentalmen.co.uk and the data tables are in the statistics section.

Just to give you some background:

The (not quite finished) site is based around my fantasy football league - each week I enter a pile of data into an Excel spreadsheet, and I then cut and paste the resulting tables into Dreamweaver. It gives me an end result I'm happy with, but it's very inefficient and labour intensive - especially as Dreamweaver duplicates the CSS style code, so I have to cut out the old CSS code from each page before I paste the new table.

This season's almost over now, so I'll keep doing what I'm doing for a few more weeks, but for next season I'd like to approach the whole thing from a more sensible angle. In order of priority, here's what I'm looking for:

1. I still want to manipulate the data in Excel, but I'm thinking that I should be able to export that data to somewhere on the server (a SQL database?), and each page should refer to the data, so that I just have to update the database rather than each individual page.
2. I'd also like the users to be able to sort the data themselves and view archived weeks if they want.
3. It'd be nice if the users could log into the site and the tables could highlight the data which pertains to them, although this is nothing like a priority!

The trouble is, I don't even know where to start looking, or even if I'm thinking along the right lines!

This is my first website, and I started it with no knowledge of html/css etc - what I have here is assembled from reading tutorials and studying the source code of other pages, so please explain things to me as if you're speaking to a five-year-old

Thanks in advance for your help!

Mark

 

[misson]

For DB design, check out some of the suggestions mentioned in the "sql database" thread. Once you've got a firm understanding of relational databases, it's time to study programming. You'll need to use server-side scripts (written in e.g. PHP or Perl) to interact with the database and generate HTML; see "Want to learn to program...." for programming recommendations. The specific functionality you want isn't too complex.

• You can export the Excel table as a CSV on your computer, upload it, parse it on the server using fgetcsv, then import the values into a table using PDO (read "Writing MySQL Scripts with PHP and PDO" for a PDO tutorial).
• Sorting is quite simple in SQL. As for sorting an HTML table, it's covered a little bit in the "How to sort a html table" thread, but if you search the web, you can probably find a clearer and safer approach. Basically, you have the SQL query handle the sorting. You pass which field to sort on in the query string (not by name of the field, but by some other identifier, as discussed in the thread), and use that info when constructing the SQL query, then generate the HTML table from the result as normal.
• The only tricky thing about highlighting is defining what's of interest. Once you've done that, simply add a class to any rows that match the definition of "interesting" when you're generating the HTML table. Define a style for that class to highlight them (e.g. give them a different background).

 

[bovril]

Thanks, Misson.

I think this is going to take some time to get my head around, there's a lot to take in!