Comparing 2 Numbers

[Kerant]

Hi there,

Following on from a previous post, I am developing a football predictions website in which users predict scores for upcoming games, then they are ranked in a table depending on how accurately they guessed the score.

I have got to the point where the admin can add fixtures to the website, and users can predict the scores for these fixtures (both of which are then stored in the database).

The bit I am stuck on now is comparing the users (predicted) score against the actual score (result).

Both sets of numbers are stored in the database, for example:

prediction_table

score_1: 2
score_2: 1

result_table

score_1: 3
score_2: 1

In this example you can see that the user has predicted the score 2-1, but the actual result was 3-1.

I am trying to figure out how to pull these values from the database to compare them, then a variable will be used to store the points for a user (for example 30 points for guessing the correct score).

I have looked into using arrays and maybe using a function such as array_diff to compare them, but I need to be able to compare how close the user score is to the actual score (for example whether they were 1 or 2 goals wrong). I've tried searching this forum but there is nothing that could help that I can find.

Sorry for the long post, but could anybody suggest a starting point for me please? Or maybe some useful functions that could be used.

Thanks very much. 🙂

[sneakyimp]

You should probably think through your point scheme for these predictions before you start writing code. They way I've seen this handled most often is a 'point spread' where the actual score is not important, it's just the difference between the two that matters.

The way I would probably go about this is writing a function to use on all the predictions:

function get_prediction_points($actual_1, $actual_2, $predicted_1, $predicted_2) {
  if ( ($actual_1 == $predicted_1) && ($actual_2 == $predicted_2) ) {
    return 30;
  } else {
    return 0;
  }
}

You can add more logic as you see fit. You could also use it in a loop to evaluate a whole series of predictions.

[Kerant]

Thanks for your reply, sneakyimp.

I do have a pointing system:

50 Points for exact score
30 Points for correct result (outcome was correct but score was wrong)
0 Points for score and result incorrectly predicted

========

I need to be able to loop through roughly 10 fixtures for each of the users that have predicted, so it would be a loop inside a loop... which is where I'm getting confused.

I've been trying for days now to try to get something working, but I'm getting nowhere at all.

Here is part of my code which is attempting to do the calculations (with num being the number of users who have predicted scores):

		for($i = 1; $i <= $num; $i++)
		{
			$getPredictions = mysql_fetch_array($result3);

			$predictionid = $getPredictions['id'];
			$score1 = $getPredictions['score_1'];
			$score2 = $getPredictions['score_2'];
			$userid = $getPredictions['user_id'];
			$fixtureid = $getPredictions['fixture_id'];

		if (($homeResult == $score1) && ($awayResult == $score2)) {
			$tempscore[$i] = $tempscore[$i] + 30;
		} // predicting score matches resulting score
		else 
		{
			$tempscore[$i] = '0';
		} // predicting score does not match resulting score

	} // end for loop

Does anybody have an ideas please?

It's a bit of a confusing problem that is hard to explain, so I appreciate all replies.

Thanks a lot.

Edit - that calculation works fine if theres just 1 fixture, but any more than that and the $tempscore variable gets over written each time, and only stores the last fixture's score, setting the previous ones to 0.

[sneakyimp]

It's not really clear from your post exactly what problem you are having. Regardless, your code looks confused. You are using mysql_fetch_array each time you go through your loop to fetch predictions and yet your loop is looping from 1 to $num. This suggests to me that you absolutely must be certain that $num and $result3 have exactly the same number of records every time.

Looks to me like you need to either use a loop based on what comes back from $result3 or you need to fetch all the results from $result3 first, making sure that whatever comes out of $result3 has numeric indexes which correlate to the $num loop you have.

[Kerant]

sneakyimp;10903184 wrote:

It's not really clear from your post exactly what problem you are having.

Sorry it's difficult to explain. I'll use an example:

• There are 10 fixtures in a database table

• 20 users see those 10 fixtures and predict a score for each one of those fixtures (so 10x20)

• The admin then enters the results of those fixtures, and now the calculations are being done

• For each user, the 10 scores (that they predicted) need to be compared against the actual result, and an amount of points need to be given for each of their correct/incorrect scores.

• This process then needs to be repeated for each of the users (which is why I need 2 loops)

sneakyimp;10903184 wrote:

This suggests to me that you absolutely must be certain that $num and $result3 have exactly the same number of records every time.

The $num is the number of users who have predicted scores for fixtures, so this tells us how many times to repeat the loop (in order to calculate each of their scores)

Part of the calculation will ultimately have to involve a database insert query, in order to store the points that a user has gained (the total number of points they got from the 10 fixtures they predicted).

I hope this has explained it a bit clearer.

As I have said in a previous post; I am just currently testing the calculations with one user. If one fixtures' scores are predicted by that one user, then the calculation works fine (30 points for correct, 0 for incorrect). But if that user has predicted more than one fixture, then the score is overwritten each time a fixture is calculated (for example: 0 0 0 30 (based on 4 fixtures)).

Thanks for your help, really is very appreciated.

[sneakyimp]

You should fetch all of the fixtures first and put them in an array:

$fixtures = array();
// run query here
$result3 = mysql_query('select * from fixtures') or die('fixture query failed'); // your query might be different
while($row = mysql_fetch_assoc($result3)) {
  $fixtures[] = $row;
}
mysql_free_result($result3);
// $fixtures should now contain all your fixtures

once you have the fixtures in an array, you should loop through each user to determine their score. It will be a nested loop

// assume $users is an array of your users)
for($u = 0; $u< sizeof($users); $u++) {
  $user = $users[$u];
  reset($fixtures);
  foreach($fixtures as $fixture) {
    // calculate score with $user and $fixture
  }
}

[Kerant]

Thanks again for your help.

I've tried implementing your suggestions but I am having the following difficulty...

The user predictions are all stored in one table, so for one set of predictions (10, for example) there could be 20 users' predictions for those 10 fixtures (meaning 200 rows).

The results table will only have the results in for those 10 fixtures, so only 10 rows will need to be retrieved from the that table.

The problem is arising while trying to compare the predictions vs the results, because the loop isn't working correctly due to 10 results being compared against 20 predictions (using 2 sets of user predictions as testing criteria at the moment).

Nesting the foreach loop inside the for loop is causing each single prediction to be compared against the 10 results, then it is repeated for every prediction in the table, and I don't know how to fix it.

Just spent literally 4 hours trying and haven't got very far at all, so if you could help solve it I would be very grateful please!

Here is my code in it's current state:

	for($i = 0; $i < sizeof($predictions); $i++) {
		$prediction = $predictions[$i];

	reset($results);

	foreach($results as $result) {

		if ($prediction['score_1'] == $result['score_1'] && $prediction['score_2'] == $result['score_2'])
		{

			echo "correct score";
			echo "<br />";

		} // predicting score does not match resulting score

		else
		{
			echo "incorrect score";
			echo "<br />";
		}
	  }
	}

Which outputs something along the lines of... (snippet)

correct score
incorrect score
incorrect score
incorrect score
incorrect score
incorrect score
incorrect score
correct score
incorrect score
incorrect score

🙁

Thanks a lot.

[sneakyimp]

not knowing the structure of your database, this is difficult to diagnose. Also, your code uses variables with names that are not meaningful to me like '$result'.

Obviously, when you fetch predictions and scores you have to be able to check the right prediction against the right game. I'm guessing that each prediction record and each score record has something like a game id right? You are going to have to figure this out for yourself at some point.

[Kerant]

sneakyimp;10903338 wrote:

not knowing the structure of your database, this is difficult to diagnose. Also, your code uses variables with names that are not meaningful to me like '$result'.

Obviously, when you fetch predictions and scores you have to be able to check the right prediction against the right game. I'm guessing that each prediction record and each score record has something like a game id right? You are going to have to figure this out for yourself at some point.

$result refers to the resulting score of the fixture -- sorry I should have realised this might have caused a problem.

Each prediction and result (resulting score) has a fixture_id in to identify the unique fixture.

[sneakyimp]

OK so it sounds like a good idea to me if you would index your scores by fixture_id. Again, I'm not sure what the structure or names of your database tables are, but it might be something like this:

$fixtures = array();
// run query here
$result3 = mysql_query('select * from fixtures') or die('fixture query failed'); // your query might be different
while($row = mysql_fetch_assoc($result3)) {
  $fixture_id = $row['fixture_id'];
  $fixtures[$fixture_id] = $row;
}
mysql_free_result($result3);
// $fixtures should now contain all your fixtures 

then your code might look something like this:

    for($i = 0; $i < sizeof($predictions); $i++) {
        $prediction = $predictions[$i];

    $fixture_id = $prediction['fixture_id'];
    $result = $fixtures[$fixture_id];

    if ($prediction['score_1'] == $result['score_1'] && $prediction['score_2'] == $result['score_2'])
    {

        echo "correct score";
        echo "<br />";

    } // predicting score does not match resulting score

    else
    {
        echo "incorrect score";
        echo "<br />";
    }
}

[Kerant]

That's fantastic!

Thank you so much sneakyimp for sticking by me with this one.

It's working after slightly modifying the code you suggested, so that's perfect.

Thanks again, you've been a massive help.