for loop help

[Lucky2710]

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

 

[Lucky2710]

Anyone???        :-\

[PFMaBiSmAd]

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.)

[Lucky2710]

I kind of understand what your saying, but could you give me a small example to point mw in the right direction?

 

What do i need in the WHERE (thats where I lose you)

 

Thanks for any help

[Lucky2710]

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.

[PFMaBiSmAd]

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

[Lucky2710]

Ok thats about got it now which function do you recommend me to use to actually be able to do something with it?

 

Should i pull it as an array or object ect?

[Lucky2710]

But just out of curiosity, back to my first question is there a way to run a loop which determines a score and then take the scores from each (run of the loop) and combine them as one variable?

 

For code reference see first topic entry!

[RussellReal]

<?php
  $total = 0;
  foreach ($data as $v) {
    $total += $v['score'];
  }
?>