Database restructuring

[neljan]

Hello

I could really do with some help on implementing a multidimensional database table and how the data is inserted/selected.

Currently I have a table which has 37 columns per user. 5 columns store information about the user and the other 32 columns are for storing times (as in stopwatch times, not time/date). It's look a bit like this:

[CODE]userID | nickname | clantag | course1 | course2 | course3 | etc...

21 | John | [J*] | 1.22.034 | 2.34.562 | 0.56.778 | ...[/CODE]

When a user registers with the site, the table row is created in this table with the relevant userID (user have same ID throughout site) and the table columns are then updated later if/when the user submits their times.

The times for each of the 32 different courses are submitted in 3 different parts (minutes, seconds and milliseconds) like so:

$course1_m = $_POST['course1_m'];
        $course1_s = str_pad((int) $_POST['course1_s'], 2, "0", STR_PAD_LEFT);
        $course1_ms = str_pad((int) $_POST['course1_ms'], 3, "0", STR_PAD_LEFT);

    $course1 = $course1_m.'.'.$course1_s.'.'.$course1_ms;

This is so that times are submitted in the correct format (1.01.001 for example) instead of 1'111"111 or 1111.111 or 101101 (etc).

What I would like to do, is have it so that if/when a user submits times via the form, the form will insert a new seperate record into the table for each course the user submits a time for only (up to 32 courses) and if the user submits a time for a course they previously submitted a time for, update the record instead.

Something like this:

[CODE]recordID | userID | courseID | coursetime | time_stamp | etc...

1 | 21 | 1 | 1.22.034 | 0000000FA1 | ...[/CODE]

So instead of having 1 table row per user, have 32 rows per user.

(I understand that I would have to create another table which defines the course names).

My problem is, I dont know how to write the PHP of:

1. If user submits a time for x amount of courses not yet submitted = create x amount of new records for relevant course/user.
2. If user submits a time for a course already submitted = update record for relevant course/user.

Your help is very much appreciated.

(please bare in mind that I'm a beginner to PHP incase you didn't notice :p)

Thanks.

[cretaceous]

you should have three tables:
one for users
one for course names
one for times

the 'times' table columns are the key to it all - and they should be:
reference id (the unique incrementing number)
user id
course id
time
time stamp of entry

then do joins to get the combinations - if there is a record for course 'x' for user '23', then update it, else add a new one

(so DO NOT add 32 empty records waiting for entry!)

that should get you on the right track at least

[neljan]

Thanks cretaceous

I already have a users table which contains userID's and other info like email address' etc. The userID is assigned when a user registers with the site.

The course name table should be like this:

[CODE]courseID | Course name

1 | racetrack1
2 | a_racetrack
3 | another_track
4 | some_track
5 | course[/CODE]

?

[cretaceous]

• looks like you need to research the 'update' sql function

you will need to check the table to see if the record exists for that user and that course - if it exists update that record - if not do an insert

are you recording only one time for any user on a given course?

also I think you should record your times in the database as the total number of milliseconds, that way you can do 'order by' operations easily
then do the maths on each result that you show on screen, to get it in your format of
minutes/seconds/milliseconds
(and conversely do the conversion to milliscionds when new figures are entered)