Database structure setting help

[planethax]

Ok, so I am making (attempting) to make a simple car racing league/pool

I have some of it started, but now I am st the point when I need to figure out the best structure for the DB before I go further.

What I have so far

	// Report all PHP errors (see changelog)
error_reporting(E_ALL);
include_once('db_conn.php');
$sql = "SELECT * FROM `nascar_standings` ORDER BY driver ASC LIMIT 65";
$rs = mysql_query($sql);
$user = "frank";
$i = 0;
$sel_text = "";
$column = "</tr><tr>";
$sel_text .= "<form name='frmChk' action='testsel1.php' method='post'>";
while($row = mysql_fetch_assoc($rs)){
  $label = $row['driver'];
  $sel_text .= "<td><input type='checkbox' name='chks[]'> $label </td>";
    if (++$i % 4 == 0)
    {
        $sel_text .= $column;
    }
}
$sel_text .= "</tr><tr><td colspan =4 valign = center><input type='submit' value='Validate & Submit' name='validate' onclick='if(!checkCount()) return false;'></form></td></tr></table>";

$sel_text .= "<script language='javascript'>
  function checkCount(){
   var count=0;
   var inputs=document.frmChk.getElementsByTagName('input');
   for(var i=0; i<inputs.length-1; i++){
     if(inputs[i].type == 'checkbox' && inputs[i].checked == true)
        count++;
   }
    if(count != 7){
        alert('You must select 7 Drivers');
        return false;
     }
     return true; 
 }
</script>";
$tpl -> AssignArray(array('selteam' => $sel_text));	

So, in the DB I pull all the drivers(approx 65) and print them to screen with checkboxes and have the user pick 7

Now, I guess I will need a new entry for each user with their 7 picked drivers.

Then each week, they will pick 4 of their 7 drivers, then those 4 drivers will earn whatever point for that week. plus a running tally for each week.

I am really stumped here on how to initially set up the DB

Any guidance on this would be awsome.
Thanx.

[planethax]

No answers on this one.
I think its because I am trying to make something simple into something too complex lol.

Just trying to find the best way to setup DB before I get to involved and realize it wont work and have to restart.

So, I think that you can't make a db 2 or 3 teered, this would be an option

Table for all DRIVERS (all drivers with their accumilated points)
Table for all USERS (all users with 7 drivers and their accumilated points? )
Table for week 1 ( All users with the 4 drivers they picked for that week with their accumilated points? )
Table for week 2 ( All users with the 4 drivers they picked for that week with their accumilated points? )
week 3 4 5 etc

Does this seem correct?
Anyone forsee any issues?
Anyone think of a better way?

[rincewind456]

I'd tend to go like this

Table Name: 	drivers
		driverId	(unique)
		firstName
		lastName

Table Name: 	players
		playerId	(unique)
		firstName
		lastName

Table Name: 	weeklyDriverScores
		weekNumber
		driverId
		pointScored

Table Name: 	totalDriverScores
		driverId
		totalPointScored

Table Name: 	playerDriver
		playerId
		driverId

and then just join the tables as needed for the results.

[planethax]

Well 8 days later lol, finally got the first step done.
What I am finding the mosst difficult is integrating into my current site which uses .php & .tpl

Ok, so now my page goes to db, gets the drivers and validates that user has picked 7 exactly,
Now how do I do next step?
I now need to enter the user (also give user a unique ID) and their seven drivers into the db in a table called userdriver10

Here is my page so far

<?php

$tpl = new template;
$tpl -> Load("!theme/{$GLOBALS["THEME"]}/templates/fantasy/fselteam.tpl");
$tpl -> GetObjects();


if (isset($_SESSION["id"])) $tpl->Zone("userStatus", "user");

	else $tpl->Zone("userStatus", "guest");

if (isset($GLOBALS["LOGIN_FAIL_TYPE"])) {
	if ($GLOBALS["LOGIN_FAIL_TYPE"] == "e.password") $loginError = $GLOBALS["OBJ"]["loginError.password"];
	elseif ($GLOBALS["LOGIN_FAIL_TYPE"] == "e.user") $loginError = $GLOBALS["OBJ"]["loginError.username"];
	elseif ($GLOBALS["LOGIN_FAIL_TYPE"] == "e.bruteforce") $loginError = $GLOBALS["OBJ"]["loginError.bruteforce"];
	elseif ($GLOBALS["LOGIN_FAIL_TYPE"] == "e.active") $loginError = $GLOBALS["OBJ"]["loginError.active"];
}

$tpl -> AssignArray(array(
	"login.failMessage" => (isset($loginError)?$loginError:NULL)
));
// Report all PHP errors (see changelog)
// error_reporting(E_ALL);
include_once('db_conn.php');
$sql = "SELECT * FROM `nascar_standings` ORDER BY driver ASC LIMIT 65";
$rs = mysql_query($sql);
$user = "frank";
$i = 0;
$sel_text = "";
$column = "</tr><tr>";
while($row = mysql_fetch_assoc($rs)){
  $label = $row['driver'];
  $sel_text .= "<td><input type='checkbox' name='chks[]'> $label </td>";
    if (++$i % 4 == 0)
    {
        $sel_text .= $column;
    }
}
$sel_text .= "</tr><tr><td colspan =4 valign = center><input type='submit' value='Validate & Submit' name='validate' onclick='if(!checkCount()) return false;'></td></tr></table></form>";

$sel_text .= "<script type='text/javascript'>

function checkCount(){
	var mincheck = 7;
	var testform = document.getElementById('frmChk');
	var items = testform.getElementsByTagName('input');
	var count = 0;

	for (var i=0; i < items.length-1; i++)
	{
		if (items[i].type == 'checkbox' && items[i].checked)
		{
			count++;
		}
	}

	if(count != mincheck)
	{
		alert('You must select ' + mincheck + ' Drivers');
		return false;
	}
	return true; 
}

</script>";
$tpl -> AssignArray(array('selteam' => $sel_text));		
$tpl -> Flush();

?>

and on the .tpl side I have
<form id='frmChk' action='testsel1.php' method='post' onsubmit='if (!checkCount()) return false;'>

I have action='testsel1.php' there just to test, but I imagine that is where I will need to input back into db?