PHP help

[phillipv]

Not sure if anyone can help.. I have a PHP app connecting to a mySql database - this is what it looks like on the front-end : http://phillipvanrooyen.com/ava-table/content/players_13.php

 

The "Points" column is not adding the points correctly.  I don't have a developer that can help. I have limited experience with php and coding. I have been looking at the php source code but unable to figure out what's causing the points column to add incorrectly.  Might anyone be able to help..?

 

The above file is referencing a function: <?php GetPlayerTableRows(); ?>

 

That function is defined as....

 

// Create HTML table rows for players (based on filters)

function GetPlayerTableRows()

{

$playerPreviewCollection = Player::GetPlayerPreviews();

 

$i = 0;

foreach($playerPreviewCollection as $key => $value)

{

$playerId = $playerPreviewCollection[$key]->GetPlayerId();

$firstName = $playerPreviewCollection[$key]->GetFirstName();

$lastName = $playerPreviewCollection[$key]->GetLastName();

$name = $playerPreviewCollection[$key]->GetFullName();

$ratingName = $playerPreviewCollection[$key]->GetCurrentRatingName();

$played = $playerPreviewCollection[$key]->GetCurrentPlayed();

$points = $playerPreviewCollection[$key]->GetCurrentPoints();

$rating = $playerPreviewCollection[$key]->GetCurrentRating();

$genderId = $playerPreviewCollection[$key]->GetGenderId();

 

if ((GetRatingSelected() == 0 || GetRatingSelected() == $rating) &&

(GetGenderSelected() == 0 || GetGenderSelected() == $genderId) &&

(GetFirstNameSelected() == NULL || strcasecmp(GetFirstNameSelected(), $firstName) == 0) &&

(GetLastNameSelected() == NULL || strcasecmp(GetLastNameSelected(), $lastName) == 0))

{

$playerLink = "<a href=playerDetail.php?player=$playerId>$name</a>";

 

// Alternate rows have unique css class "alternate"

$cssRow = ($i & 1 ? "alternate" : "");

 

echo "<tr class=$cssRow>";

echo "<td>$playerLink</td>";

echo "<td>$points</td>";

echo "<td>$played</td>";

echo "<td class=last>$ratingName</td>";

echo "</tr>";

 

$i++;

}

}

}

 

?>

[requinix]

Please use a more descriptive title than "PHP help". You're posting a question here. We know you need help

And please use

 tags around your code. Makes it much easier to read that way.
 

$points = $playerPreviewCollection[$key]->GetCurrentPoints();
That's where the points are coming from so we'll need to see the code for that.
 

And can you describe what you mean by "not adding the points correctly"? How is it wrong? What is it supposed to be?

[phillipv]

Thanks for the reply. Sorry about the confusion I'll try and clarify..

 

the “Points” column on this page is not adding the points correctly?

http://phillipvanrooyen.com/ava-table/content/players_13.php

 

for instance, if I click on the link to the persons name at the top.. (Matt) on his detail page here:

 http://phillipvanrooyen.com/ava-table/content/playerDetail.php?player=107

there are six Points totals under '14 on the right that add up to 179

 

however in the “Points” column on the first page it displays as 163.. for some odd reason but should actually be displaying 179 (the total of all points earned for "14).  does this make sense?

 

the players.code.php page (I referenced in the first post) is referencing...

require_once('dataAccess.php');
require_once('utility.php');
require_once('../classes/Player.class.php');
require_once('../classes/Event.class.php');

in Players.class.php the 

GetCurrentPoints()

 function is defined as...

// Extends to include all values required to display player previews.
class PlayerPreviewData extends PlayerBaseData
{
private $genderId;
private $currentPlayed;
private $currentPoints;
private $currentRating;
private $eventsPlayedCollection; // Array of events.


protected function setGenderId($genderId) { $this->genderId = $genderId; }   
public function GetGenderId() { return $this->genderId; } 


protected function setCurrentPlayed($currentPlayed) { $this->currentPlayed = $currentPlayed; }   
public function GetCurrentPlayed() { return $this->currentPlayed; } 


protected function setCurrentPoints($currentPoints) { $this->currentPoints = $currentPoints; } 
public function GetCurrentPoints() { return $this->currentPoints; } 


protected function setCurrentRating($currentRating) { $this->currentRating = $currentRating; }   
public function GetCurrentRating() { return $this->currentRating; } 


public function GetCurrentRatingName()
{
$ratings = GetRatings();
return $ratings[$this->currentRating];
}
}

[requinix]

function is defined as...

Nothing useful in there. How about the code for Player::GetPlayerPreviews()?

[phillipv]

In players.code.php there is...

 

// Create HTML table rows for players (based on filters)
function GetPlayerTableRows()
{
$playerPreviewCollection = Player::GetPlayerPreviews();


$i = 0;
foreach($playerPreviewCollection as $key => $value)
{
$playerId = $playerPreviewCollection[$key]->GetPlayerId();
$firstName =  $playerPreviewCollection[$key]->GetFirstName();
$lastName =  $playerPreviewCollection[$key]->GetLastName();
$name = $playerPreviewCollection[$key]->GetFullName();
$ratingName = $playerPreviewCollection[$key]->GetCurrentRatingName();
$played = $playerPreviewCollection[$key]->GetCurrentPlayed();
$points = $playerPreviewCollection[$key]->GetCurrentPoints();
$rating = $playerPreviewCollection[$key]->GetCurrentRating();
$genderId = $playerPreviewCollection[$key]->GetGenderId();


if ((GetRatingSelected() == 0 || GetRatingSelected() == $rating) &&
(GetGenderSelected() == 0 || GetGenderSelected() == $genderId) &&
(GetFirstNameSelected() == NULL || strcasecmp(GetFirstNameSelected(), $firstName) == 0) &&
(GetLastNameSelected() == NULL || strcasecmp(GetLastNameSelected(), $lastName) == 0))
{
$playerLink = "<a href=playerDetail.php?player=$playerId>$name</a>";


// Alternate rows have unique css class "alternate"
$cssRow = ($i & 1 ? "alternate" : "");


echo "<tr class=$cssRow>";
echo "<td>$playerLink</td>";
echo "<td>$points</td>";
echo "<td>$played</td>";
echo "<td class=last>$ratingName</td>";
echo "</tr>";


$i++;
}
}
}

 

I could zip the php files together and email if that's easier..?

[requinix]

Not there. I think Players.class.php has a class named Players and in there a method named GetPlayerPreviews. That's what I'm interested in.

 

I could zip the php files together and email if that's easier..?

Let's see how far we can get without that.

[phillipv]

This is from Players.class.php...

// Core Player logic.
class Player extends PlayerData
{ 


// Collection of all PlayerPreviewData
public static function GetPlayerPreviews()
{ 
$playerPreviewCollection = array();


$query  = "SELECT PlayerID, FirstName, LastName, GenderID, CurrentPlayed, CurrentPoints, ";


//TODO: MAKE SQL CAP RATING AT 4.
$query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating ";
$query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) ";
$query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) ";
$query .= " ELSE (CurrentUnadjustedRating + 3) ";
$query .= "END) AS CurrentRating ";


$query .= "FROM Players ";
$query .= "ORDER BY CurrentPoints DESC, CurrentRating, FirstName, LastName";
$result = GetQueryResult($query);


while($row = mysqli_fetch_array($result))
{
$pp = new PlayerPreviewData();


$pp->setPlayerId($row['PlayerID']);
$pp->setFirstName($row['FirstName']);
$pp->setLastName($row['LastName']);
$pp->setGenderId($row['GenderID']);
$pp->setCurrentPlayed($row['CurrentPlayed']);
$pp->setCurrentPoints($row['CurrentPoints']);


//$pp->setCurrentRating($row['CurrentRating']);
//$currentUnadjustedRating = $row['CurrentUnadjustedRating'];
//$currentUnadjustedRatingYear = $row['CurrentUnadjustedRatingYear'];
//$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']);
$currentRating = $row['CurrentRating'];
if ($currentRating > 4)
$currentRating = 4;
$pp->setCurrentRating($currentRating);


$playerPreviewCollection[] = $pp;
}


return $playerPreviewCollection;
}


// Return base player object (teamate).
public static function GetPlayerBaseData($playerId, $firstName, $lastName)
{
$pbd = new PlayerBaseData();


$pbd->setPlayerId($playerId);
$pbd->setFirstName($firstName);
$pbd->setLastName($lastName);


return $pbd;
}




// Get All Player Data based on Player Id.
public static function GetPlayerData($playerDataId)
{
$pd = new PlayerData();


$query  = "SELECT PlayerID, FirstName, LastName, Hometown, CurrentTown, Height, Picture, College, ";
$query .= "LocalBeach, IsAVAVolunteer, CurrentPlayed, CurrentPoints, ";


//TODO: MAKE SQL CAP RATING AT 4.
$query .= "(CASE WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating ";
$query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) ";
$query .= " WHEN Year(CurrentUnadjustedRatingDate) > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) ";
$query .= " ELSE (CurrentUnadjustedRating + 3) ";
$query .= "END) AS CurrentRating ";
// $query .= "(CASE WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 2) THEN CurrentUnadjustedRating ";
// $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 3) THEN (CurrentUnadjustedRating + 1) ";
// $query .= " WHEN CurrentUnadjustedRatingDate > (Year(CURDATE()) - 4) THEN (CurrentUnadjustedRating + 2) ";
// $query .= " ELSE (CurrentUnadjustedRating + 3) ";
// $query .= "END) AS CurrentRating ";


//GET TotalEventsPlayed
$query .= ", COUNT(ResultID) AS AllTimePlayed ";


//GET TotalPoints
$query .= ", NumberOfTeams, Result ";


$query .= "FROM Players ";


$query .= "INNER JOIN Teams USING (PlayerID) ";
$query .= "INNER JOIN Results USING (TeamID) ";
$query .= "INNER JOIN Events USING (EventID) ";


$query .= "WHERE PlayerID = $playerDataId ";
$result = GetQueryResult($query);


while($row = mysqli_fetch_array($result))
{
$pd->setPlayerId($row['PlayerID']);
$pd->setFirstName($row['FirstName']);
$pd->setLastName($row['LastName']);
$pd->setHometown($row['Hometown']);
$pd->setCurrentTown($row['CurrentTown']);
$pd->setHeight($row['Height']);
$pd->setProfilePicture($row['Picture']);
$pd->setCollege($row['College']);
$pd->setLocalBeach($row['LocalBeach']);
$pd->setIsAVAVolunteer($row['IsAVAVolunteer']);
$pd->setCurrentPlayed($row['CurrentPlayed']); 
$pd->setCurrentPoints($row['CurrentPoints']); 


//$pd->setCurrentRating($row['CurrentRating']);
//$currentRating = AdjustRatingBasedOnTime($row['CurrentUnadjustedRatingYear'], $row['CurrentUnadjustedRating']);
$currentRating = $row['CurrentRating'];
if ($currentRating > 4)
$currentRating = 4;


$pd->setCurrentRating($currentRating);


$pd->setAllTimePlayed($row['AllTimePlayed']);
//$pd->setAllTimePoints($row['AllTimePoints']); 






//$pd->setAllTimeRating($currentRating);
//$allTimeRating = CalculateRatingEarned($row['NumberOfTeams'], $row['Result']);
// $pd->setAllTimeRating($allTimeRating);


}


$pd->eventsPlayedCollection = Player::GetEventsPlayedComplete($playerDataId);


foreach($pd->eventsPlayedCollection as $key => $value)
{
$eventDate = $pd->eventsPlayedCollection[$key]->eventDate;
$numberOfTeams = $pd->eventsPlayedCollection[$key]->numberOfTeams;
$finish = $pd->eventsPlayedCollection[$key]->finish;
$allTimePoints += $pd->eventsPlayedCollection[$key]->points;


// Calculate Best Rating
//$rating = CalculateRatingEarned($numberOfTeams, $finish);
$genderId = $pd->eventsPlayedCollection[$key]->genderId;
$divisionId = $pd->eventsPlayedCollection[$key]->divisionId;
//$rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish);
$rating = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $finish);


if ($allTimeRating == 0)
$allTimeRating = $rating;
elseif ($rating < $allTimeRating)
$allTimeRating = $rating;




// Calculate Best Finish
if (IsDateInCurrentSeason($eventDate))
{
if ($currentBestFinish == 0)
{
$currentBestFinish = $finish;
}
elseif ($finish < $currentBestFinish)
{
$currentBestFinish = $finish; 
}
}


if ($allTimeBestFinish == 0)
{
$allTimeBestFinish = $finish;
}
elseif ($finish < $allTimeBestFinish)
{
$allTimeBestFinish = $finish;
}




// Calculate Number of Open Wins.
if(IsDateInCurrentSeason($eventDate))
{
if ($finish == 1)
$currentOpenWins++;
}


if ($finish == 1)
$allTimeOpenWins++;
}


$pd->setAllTimePoints($allTimePoints); 




$pd->setAllTimeRating($allTimeRating);




$pd->setCurrentBestFinish($currentBestFinish); 
$pd->setAllTimeBestFinish($allTimeBestFinish);
$pd->setCurrentOpenWins($currentOpenWins);
$pd->setAllTimeOpenWins($allTimeOpenWins);


return $pd;
}

[requinix]

You have points being tracked in two different locations and they've started disagreeing with each other: Players.CurrentPoints (163) and the individual points per game (179).

 

That's a 16 point difference, which happens to be the number of points scored on September 6th. Coincidence? I don't know. Do any other players have a similar discrepancy?

[phillipv]

Thanks for that insight.  All players I've checked have the same discrepancy -the amount earned on Sept 6th.  Bryan Frost for instance has a 19 points less than he should have (19 being the amount earned on Sept 6th)  How does the code know which dates to include in the 

$pp->setCurrentPoints($row['CurrentPoints']);

.. is that how this could be fixed?

[requinix]

It doesn't know the dates: CurrentPoints is just a number and it seems it wasn't updated properly when those September 6th figures were entered into the database or whatever. Were there any other dates after that? The question goes to whether updating stopped working entirely or whether it was just that one date that had problems.

[Barand]

Which is a case-study in why you should not stored derived data (like totals) in tables as it can easily get out of sync. You should always query the original data to get the total. Just my 0.02 worth.

[phillipv]

No other dates were added after Sep 6th. Those were just added recently (last month). The rest were all added mid last year. 

[requinix]

I'm alright with you PMing me a link to download the code. I'll be looking to see how and when that CurrentPoints gets updated, then for a reason why that might not have happened. But I'm also working so it won't be very quick.

[phillipv]

There's logic added somewhere to limit the amount of tournaments counted to 5.

[requinix]

There's only one place in your code that updates the CurrentPoints and that's in the import script.

		if (strtotime($date) > strtotime('2009-09-20'))		

		{

			echo "-------------------------------DATE FOUND-------------------------------";



			// PLAYER ONE

			$query = "SELECT CurrentPoints From Players WHERE PlayerID = $playerIdOne";

			$result = $mysqli->query($query);

			$resultRow = $result->fetch_array(MYSQLI_BOTH);

			

			$currentPoints = $resultRow['CurrentPoints'];

			//$pointsEarned = CalculatePoints($numberOfTeams, $finish);

			$pointsEarned = CalculatePoints($genderId, $divisionId, $numberOfTeams, $finish);

			$currentPoints += $pointsEarned;

			

			$query = "UPDATE Players SET CurrentPoints = $currentPoints WHERE PlayerID = $playerIdOne";

			$result = $mysqli->query($query);

			

			// PLAYER TWO

			$query = "SELECT CurrentPoints From Players WHERE PlayerID = $playerIdTwo";

			$result = $mysqli->query($query);

			$resultRow = $result->fetch_array(MYSQLI_BOTH);

			

			$currentPoints = $resultRow['CurrentPoints'];

			//$pointsEarned = CalculatePoints($numberOfTeams, $finish);

			$pointsEarned = CalculatePoints($genderId, $divisionId, $numberOfTeams, $finish);

			$currentPoints += $pointsEarned;

			

			$query = "UPDATE Players SET CurrentPoints = $currentPoints WHERE PlayerID = $playerIdTwo";

			$result = $mysqli->query($query);

		}

(most of the interesting variables came from a row of the CSV file that the script is loading)

 

However what you sent me doesn't have any recent CSV files to look at. I assume you have them around somewhere?

Also, loading the Matt Mueller page you linked earlier gives different results than before: now it's 154 points vs 179 actual, which is a 25 point difference and points to either the June 14th or August 23rd tournament. So what has changed with the site and/or data in the last couple days?