Help with database structure

loukas

Hi all !

Im having a really hard time trying to create this database structure for a project im working on. If anyone can give me any tip it would be awesome.

Its a database for a laptimes ranking.

The elements are:
Tracks -> 20
Racers -> a lot
Laptimes
Classes -> 2 (10 tracks are from 1 class and the other 10 from another class)

The tables I have now are:
Laptimes ( id, racer_id, track_id, class_id, laptime )
Tracks (id, track_name)
Racers (id, racer_name)
Class (id, class_name)

My problem is I have no clue how to retrieve those records. For example if I wanted to show the ranking by class ordered by total time, showing the Class name, rider name and his total time.

I dont even know if the structure I created is the best option... I would really apreciate any help.

thanks

bbreslauer

If the tracks are always in the same class, then you can get rid of one table. Your design seems to be pretty good, but here's how I would fix that:

Laptimes ( id, racer_id, track_id, laptime )
Tracks (id, track_name, class_name)
Racers (id, racer_name)

If you want to be able to record multiple races, then you'll want to add a "race" table, and put that id into laptimes as well.

What you want to look for are 1-to-1 relationships, 1-to-many relationships, and many-to-many relationships. If you've got a 1-to-1, then you can use one table to capture that information (if I'm correct above, there's only one class_name for every track_name). If you've got a 1-to-many relationship, then you need to use 2 tables. And if you've got a many-to-many relationship, then you need to use 3 tables (You have many tracks, and each racer can be on multiple tracks, so you need laptimes to combine both of those things).

My problem is I have no clue how to retrieve those records. For example if I wanted to show the ranking by class ordered by total time, showing the Class name, rider name and his total time.

Laptimes ( id, racer_id, track_id, laptime )
Tracks (id, track_name, class_name)
Racers (id, racer_name)

You can do that with a simple SELECT query combined with an array to order the data:

SELECT Laptimes.laptime,Tracks.class_name,Racers.racer_name,Racers.id
FROM Laptimes, Tracks,Racers
WHERE Laptimes.racer_id=Racers.id
AND Laptimes.track_id=Tracks.id

From there, you would need to use a PHP loop to count up the total time for each racer (by ID, in case two racers have the same name) and then display that in order.

loukas

Thank you so much for helping. Now I'm able to retrieve the data with no problem but I'm having some problems in the php part.

Im assigning all the retrieved data for a multi-dimensional array like this:

The sql command:
SELECT Laptimes.racer_id, Laptimes.class_id, Laptimes.time, Racers.id, Racers.name
FROM Laptimes, Racers
WHERE Laptimes.racer_id = Racers.id
AND Laptimes.class_id = 1
ORDER BY Racers.id ASC

Then using a while loop im assigning:

$i = 0;
$temp_id = 0;
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
if ($temp_id == $row['racer_id']) {

$v8_ranking[ $row['name'] ][ $i ] = $row['time'];
} else {
$i = 0;
$temp_id = $row['racer_id'];
$v8_ranking[ $row['name'] ][ $i ] = $row['time'];
}
$i++;
}

Lets say i have this data in the db
id name time class
2 John 100 1
2 John 120 1
2 John 50 1
3 Carl 130 1
3 Carl 110 1

Using the code above the array will be like:
$v8_ranking[John][0] = 100
[John][1] = 120
[John][2] = 50
[Carl][0] = 130
[Carl][1] = 110

My problem is: How do I show the results ordered by totaltime (check below)

john 270
carl 240

thanks again for your help

bbreslauer

I'm not sure what you're using $temp_id for, and you should be using the racer's id in the multidim array, not their name, in case you've got multiple people with the same name. You could store their name in $v8_ranking['id'][0] and the total time in $v8_ranking['id'][1], for instance.

This should work:

$v8_ranking = array();
$result = mysql_query($sql);
for ($i=0;$i<($row=mysql_fetch_assoc($result));$i++) {
    $racerid = $row['id'];
    if (array_key_exists($racerid, $v8_ranking)) {
        $v8_ranking[$racerid][1] += $row['time'];
    } else {
        $v8_ranking[$racerid][0] = $row['name'];
        $v8_ranking[$racerid][1] = $row['time'];
    }
}

loukas

wow ! your code is much simpler and do the same job 😃 thanks.

using my code I ended up with an array [name][totaltime] so it was very easy to sort using (asort) and to loop thru it to show results using a simple while(list($k,$v) = each($array)).

Now using your code i get a [id][0] = name [id][1] = totaltime. I tried using for each loop but i cant show the results. Actually im using this code and its working but im quite sure this is the wrong way of doing it. Can you show me a cleaner way of looping thru the array ? thanks

foreach ($v8_ranking as $v1) {
	for ($i = 0; $i < count($v1); $i += 2) {
		echo $v1[$i].' '.$v1[$i+1].'<br>';
	}
}

bbreslauer

Your inside for loop is meaningless, or rather, it's screwing up your results. Because you are using a foreach loop, you're taking the $v8_ranking array and putting each row into $v1 one at a time. Here's how you can display it:

foreach ($v8_ranking as $v1) {
    echo "ID: ". $v1[0] ." and total time: ". $v1[1] ."<br />";
}

If you want to sort this before outputting it, I'm not sure if there's a PHP function for doing that.

This might work, though I'm not sure, as I haven't done a lot of array sorting in the past.

function cmp($a, $b) {
    if ($a > $b) {
       return(1);
    } else if ($a == $b) {
        return(0);
    } else {
        return(-1);
    }
}

uasort($v8_ranking[], "cmp");

But again, I'm not sure if that would. You could always manually sort it when you're outputting it, if you've got nothing else you need it sorted for. You'd just need to use a loop to go through every entry, and use a temp variable to be set to the index of the best time. Not elegant, but it would work.

loukas

I tried the sort using that function but it doesnt work. It sorts all rows but the last so, last row is always not sorted. The only solution I found was to remove the ID from array making it only [name][time].

When the user is logged in and checking ranking, I want it to, for every row, check if the user online matches the name in the row so his name gets bold or highlighted in ranking. At first I wanted to use the ID for that (this is why I prefer your array [ID][0]=name [ID][1]=time) but as now I went back to the basic array Ill have to use the name for that. Do you think using the name as the matching factor will affect performance ?

thanks

bbreslauer

If two people have the same name, then this can screw up. To identify people, you should always use a unique ID#.

Sorry about the sorting thing, as I said, I haven't had all that much experience sorting arrays.

loukas

Names are also unique just like ID so that wont be a problem. My only concern is if using names might slow down the whole thing because Im quite sure ID is a much faster way to compare data.

Anyways without your help I wouldnt be able to get this thing to work so thanks a lot !