[Help with database design - complex relationship]

The player_points table in my version will enable you to find the winning user this week / this month / this season etc. If, as in the second version, you accumulate the points in the player record then you lose the time element and at some time you have the overhead of having to reset them all to zero (and lose any historical player performance data)

 

The second version's user_team table enables users to have more than one team if that is a requirement.

Yeah.

everything looks in order now but only one problem.... Every team will have only 10  players. So, in the table "user_team"  you provided there will be only 10 rows for particular user, right? then where should i add team_name and rank.

If i add team_name in "user_team"  then it will be repeated 10 times just like u_id because of 10 players.is this feasible?

and about rank..i cant think of any solution to it? hope you will help there...

Thanks.

[Help with database design - complex relationship]

 

Store the players' points for each player and not for each member of every user's team. That way you only have to add a record for each player getting points in a match rather than updating all the team records.

+-----------+                         +-----------+
|  user     |                         | player    |
+-----------+                         +-----------+
|  u_id     |--+                    +-| p_id      |---+
|  username |  |                    | | playername|   |
+-----------+  |                    | +-----------+   |
               |                    |                 |
               |  +-------------+   |                 |   +--------------+
               |  | user_team   |   |                 |   | player_points|
               |  +-------------+   |                 |   +--------------+
               +-<|   u_id      |   |                 +--<|  p_id        |
                  |   p_id      |>--+                     |  match_date  |
                  +-------------+                         |  points      |
                                                          +--------------+

Then you can the get the total points for each user with a simple query

 

SELECT u.username, SUM(pp.points) as total_points
FROM user u
    INNER JOIN user_team t USING (u_id)
    LEFT JOIN player_points pp USING (p_id)
    GROUP BY u.u_id
    ORDER BY total_points DESC

Thanks for reply. really Helpful.

 

Someone suggested me this solution.

 

 

user                              play

 

u_id(pri)-------- +            p_id(pri)---------------+      

username         |            playername               | 

                         |            POINTS                     |

                         |                                             |

user_team        |             player_for_teams     |

                         |                                             |

t_id(pri)    -------|----+            p_id (fk) ---------+

                         |     |

u_id (fk)    ------+    +---------t_id (fk)

 

 

And as you suggested, i added Points (in CAPS). so is this correct? 

Pls reply, thanks.

[Help with database design - complex relationship]

thanks for reply..

tables now are :

 

1)user(u_id, details);

 

2)players(p_id,details,points);

 

3)user_team(u-id,p_id,points gained)

 

but i have a doubt. for all 10 rows in the user_team table u_id should be same for a particular user. 

am i correct? or something more  should be there?  

pls help

Thanks 

[Help with database design - complex relationship]

I am working on phpmyadmin.

i want to create a system in which users will select 10 players from pool of players and gain points from those 10 players, just like 

fantasy football.

 

So my first solution was

 

tables:

 

users(name,details,team[10],points gained)  and players(name,details,points gained by individual player);

 

but i am not sure how to get that team array in users table.

so i searched for solutions, then somesone suggested about creating one more table team and some thing about foreign keys.

But still i am confused how to do this?

 

and 1 more question should i use phpmyadmin or mysql workbench to do this . right now i have both installed on system but i have never used mysql workbench.

 

pls help. Thanks

 

 

[php script bug ..pls help]

This is Login script..
I also has register script which works fine adds some fields to database including
password with just md5 encryption.
when i try to login with email and password, this script always shows incorrect password message when i 
type correct password and shows me member area when i type wrong password. 
i couldnot find any error. pls help

<?php 

// Connects to your Database 

mysql_connect("localhost", "root", "dhiraj") or die(mysql_error()); 

mysql_select_db("test") or die(mysql_error()); 




session_start();

$e_id;
$pwd;

if(!isset($_POST['submit']))
{	
?>


<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post"> 

<table border="0"> 

<tr><td colspan=2><h1>Login</h1></td></tr> 

<tr><td>Email Id:</td><td> 

<input type="text" name="email" maxlength="60"> 

</td></tr> 

<tr><td>Password:</td><td> 

<input type="password" name="pass" maxlength="50"> 

</td></tr> 

<tr><td colspan="2" align="right"> 

<input type="submit" name="submit" value="Login"> 

</td></tr> 

</table> 

</form> 

<?php
} 
else 
{
// if form has been submitted
$_SESSION['e_id']=$_POST['email'];
$_SESSION['pwd']=$_POST['pass'];



// makes sure they filled it in

if(!$_POST['email'] | !$_POST['pass']) 
{

die('You did not fill in a required field.');

}

// checks it against the database

$check = mysql_query("SELECT * FROM users WHERE u_email = '".$_POST['email']."'")or die(mysql_error());



//Gives error if user dosen't exist

$check2 = mysql_num_rows($check);

if ($check2 == 0) 
{

unset($_SESSION['e_id']);
unset($_SESSION['pwd']);
die('That user does not exist in our database. <a href=enter.php>Click Here to Register</a>');
}

while($info = mysql_fetch_array( $check )) 

{



$_POST['pass'] = md5($_POST['pass']);



//gives error if the password is wrong

if ($_POST['pass'] != $info['u_pass'])
{

die('Incorrect password, please try again.');
//this gets executed when supply correct password.

} 
else 
{ 


//then redirect them to the members area 

header("Location: members.php"); 
//this gets executed when i supply wrong password.

} 

} 

} 
?>

[security issue with form and mysql]

i have created a form. in which i want user to enter his/her full name. so how to confirm that it doesnot contain numbers or other special characters other than alphabets. i want to store it in mysql database. Pls help. Thanks