Jump to content

for loop help


Lucky2710

Recommended Posts

Alright heres the scripts...

$query = "SELECT Game_ID, Pick FROM Cfb WHERE User_ID= 1";
$result = mysql_query($query);

$query1 = "SELECT Game_ID, Pick FROM Cfb WHERE User_ID= 61";
$result1 = mysql_query($query1);

$affected = mysql_num_rows($result);

for($i=0; $i< $affected; $i++) {
$scores =mysql_fetch_assoc($result);
$scores1 =mysql_fetch_assoc($result1);

$final = array_diff_assoc($scores,$scores);
$user = array_diff_assoc($scores, $scores1);

$f = count($final);
$u = count($user);

if ($f == $u){
$points = +1;
}else{
$points = +0;
}
echo $points;
echo '<br />';
echo '<hr>';
}

 

The script pulls information out of the mysql table and compares it to each users input. Then it either gives the user a point or not! I need someway to add up all the $points values. to decide how many points that they get. (Currently for testing purposes i have put 12 rows of data in my table for a user and for what it is compared to.) (So 24 rows total)

 

Eventually this will all go into a function and User_ID at the top will be set to a variable and the script will run for all the users not just user id 61!

 

my table is set up like this  (Ignore "Week" for now)

 

+---+----------+------------+-------+------+

| ID | User_ID | Game_ID | Week | Pick |

+---+----------+------------+-------+------+

 

ID is auto incrementing

and the rest is pretty self explanatory

 

Link to comment
Share on other sites

You can generally do processing like this directly in a query.

 

I would -

 

1) Select rows where the user's pick and game_id matches the master record,

2) Then GROUP BY the user_id, and

3) Use COUNT() in the SELECT list to give you the number of rows in each group. This would tell you how many matching rows each user has.

 

This will work no matter how many users you select, from a single one to all of them (simply don't put the user_id as a condition in the WHERE clause.)

Link to comment
Share on other sites

To be more specific on how my idea goes.

 

I have 1 user that can make picks after polls close. I pick the actual out come with that user.

 

Then i will run this script to give everybody there scores.

This script is supposed to compare that particular users picks with everyone else's and if they got them right them them there score. and for each correct pick a user gets 1 point.

Link to comment
Share on other sites

This is the basic query that does what you are asking (user_id = 1 is the master record) -

$query = "SELECT user.user_id,user.game_id,COUNT(*) as Total FROM Cfb as master JOIN Cfb as user ON master.game_id = user.game_id AND master.pick = user.pick AND master.user_id = 1 GROUP BY user.game_id, user.user_id";

 

This gets all the results for all the games and all the users.  As is, it would return results like this -

 

user_id  game_id  Total

1  1  12

11  1  6

61  1  12

1  2  12

11  2  6

61  2  11

 

user_id 1 is the master, so he of course matches all 12 in both games. user_id 11 got 6 correct in both games. user_id 61 got 12 correct in game 1 and 11 correct in game 2.

 

If you put in a WHERE clause (goes in before the GROUP BY clause) you can determine what you want to operate on. If you add WHERE user.game_id = 2, it will return only the results for game 2 -

 

user_id  game_id  Total

1  2  12

11  2  6

61  2  11

 

If you change the WHERE clause to user.user_id = 61, it will return only the results for user 61 -

 

user_id  game_id  Total

61  1  12

61  2  11

 

And of course any other valid usage (WHERE user.game_id = 2 AND user.user_id = 61) -

user_id  game_id  Total

61  2  11

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.