phillipv Posted October 14, 2015 Share Posted October 14, 2015 Hi, I'm not a developer and have rudimentary knowledge of PHP/SQL. I was hoping there was a simple solution to this problem that someone might be able to help with...On this page: http://phillipvanrooyen.com/ava-table/content/players_13.phpNotice next to the top name it (incorrectly) shows he played "4" in the "#played '15" column. However, click on his name "Franklin" and you'll see on the next page it shows (correctly) that he actually played "6" (if you add up all the rows under '15)... that's correct.I'm confused as to what the code is selecting here..Any idea how to fix it so that the .../players_13.php page actually shows:1. The correct amount of "played" events.. in this case "6" not "4"2. Update the "Points '15" column to show the correct points for all 6 eventsAny help is much appreciated... there are these files at play here for the front-end display..http://phillipvanrooyen.com/ava-table/php_help/php-help-ava.gif More information about this issue is here: http://www.phphelp.com/forum/general-php-help/php-confusion/ Players.class.php looks like this... and I suspect the method GetPlayerData() (on code lines 215 - 242) calls for a column in the database that has the erroneous data. <?php //------------------------------------------------------------------ // File: Player.class.php // Author: // Date Created: 2010-03-25 // // Player Management //------------------------------------------------------------------ // Lightweight Object representing player name & id. class PlayerBaseData { private $playerId; private $firstName; private $lastName; protected function setPlayerId($playerId) { $this->playerId = $playerId; } public function GetPlayerId() { return $this->playerId; } protected function setFirstName($firstName) { $this->firstName = $firstName; } public function GetFirstName() { return $this->firstName; } protected function setLastName($lastName) { $this->lastName = $lastName; } public function GetLastName() { return $this->lastName; } public function GetFullName() { return sprintf('%s %s', $this->GetFirstName(), $this->GetLastName()); } } // 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]; } } // Extends to inclue all remaining player data (Player Details). class PlayerData extends PlayerPreviewData { private $profilePicture; private $hometown; private $currentTown; private $height; private $localBeach; private $college; private $isAVAVolunteer; private $currentBestFinish; private $allTimePlayed; private $allTimePoints; private $allTimeRating; private $allTimeBestFinish; private $currentOpenWins; private $allTimeOpenWins; protected function setProfilePicture($profilePicture) { $this->profilePicture = $profilePicture; } public function GetProfilePicture() { return $this->profilePicture; } protected function setHometown($hometown) { $this->hometown = $hometown; } public function GetHometown() { return $this->hometown; } protected function setCurrentTown($currentTown) { $this->currentTown = $currentTown; } public function GetCurrentTown() { return $this->currentTown; } protected function setHeight($height) { $this->height = $height; } public function GetHeight() { return $this->height; } protected function setLocalBeach($localBeach) { $this->localBeach = $localBeach; } public function GetLocalBeach() { return $this->localBeach; } protected function setCollege($college) { $this->college = $college; } public function GetCollege() { return $this->college; } protected function setIsAVAVolunteer($isAVAVolunteer) { $this->isAVAVolunteer = $isAVAVolunteer; } public function GetIsAVAVolunteer() { return $this->isAVAVolunteer; } protected function setCurrentBestFinish($currentBestFinish) { $this->currentBestFinish = $currentBestFinish; } public function GetCurrentBestFinish() { return $this->currentBestFinish; } protected function setAllTimePlayed($allTimePlayed) { $this->allTimePlayed = $allTimePlayed; } public function GetAllTimePlayed() { return $this->allTimePlayed; } protected function setAllTimePoints($allTimePoints) { $this->allTimePoints = $allTimePoints; } public function GetAllTimePoints() { return $this->allTimePoints; } protected function setAllTimeRating($allTimeRating) { $this->allTimeRating = $allTimeRating; } public function GetAllTimeRating() { return $this->allTimeRating; } protected function setAllTimeBestFinish($allTimeBestFinish) { $this->allTimeBestFinish = $allTimeBestFinish; } public function GetAllTimeBestFinish() { return $this->allTimeBestFinish; } protected function setCurrentOpenWins($currentOpenWins) { $this->currentOpenWins = $currentOpenWins; } public function GetCurrentOpenWins() { return $this->currentOpenWins; } protected function setAllTimeOpenWins($allTimeOpenWins) { $this->allTimeOpenWins = $allTimeOpenWins; } public function GetAllTimeOpenWins() { return $this->allTimeOpenWins; } public function GetLocalBeachName() { $beachNames = GetBeaches(); return $beachNames[$this->localBeach]; } public function GetAllTimeRatingName() { $ratings = GetRatings(); return $ratings[$this->allTimeRating]; } public function GetCurrentBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->currentBestFinish); } public function GetAllTimeBestFinishOrdinal() { return ConvertIntegerToOrdinal($this->allTimeBestFinish); } } // 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 CurrentRating ASC, CurrentPoints DESC, 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; } // // Get Collection of all played events. // private static function GetEventsPlayed($playerId) // { // $eventsPlayedCollection = array(); // // $query = "SELECT StartDate, Result, NumberOfTeams FROM Players "; // $query .= "INNER JOIN Teams T USING (PlayerID) "; // $query .= "INNER JOIN Results R USING (TeamID) "; // $query .= "INNER JOIN Events E USING (EventID) "; // $query .= "INNER JOIN Divisions D USING (DivisionID) "; // $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; // $query .= "WHERE P.PlayerID = $playerId"; // $result = GetQueryResult($query); // // while($row = mysqli_fetch_array($result)) // { // $eventDate = $row['StartDate']; // $results = $row['Result']; // $numberOfTeams = $row['NumberOfTeams']; // //$points = CalculatePoints($numberOfTeams, $results); ////$points = CalculatePoints($numberOfTeams); // $event = PlayerEvent::GetPlayerEvent($eventDate, $results, $points, $numberOfTeams); // $eventsPlayedCollection[] = $event; // } // // return $eventsPlayedCollection; // } // Get Collection of all played events. private function GetEventsPlayedComplete($playerId) { $eventsPlayedCompleteCollection = array(); //$result = GetEventsPlayed($playerId); //$query = "SELECT * FROM Players P "; //$query = "SELECT TOUR.StartDate, R.Result, E.NumberOfTeams, E.DivisionID, E.GenderID, E.EventID, D.Division, T.TeamID, Tour.TournamentID "; $query = "SELECT PlayerID, TOUR.StartDate, E.StartDate, Result, NumberOfTeams, DivisionID, E.GenderID, EventID, Division, TeamID, Year(E.StartDate) AS Year "; $query .= "FROM Players P "; $query .= "INNER JOIN Teams T USING (PlayerID) "; $query .= "INNER JOIN Results R USING (TeamID) "; $query .= "INNER JOIN Events E USING (EventID) "; $query .= "INNER JOIN Divisions D USING (DivisionID) "; $query .= "INNER JOIN Tournaments TOUR USING (TournamentID) "; $query .= "WHERE P.PlayerID = $playerId "; //$query .= " ORDER BY Year DESC"; //$query .= " ORDER BY TOUR.StartDate DESC"; $query .= "ORDER BY Year DESC, E.GenderID, E.DivisionID"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $eventDate = $row['StartDate']; $results = $row['Result']; $numberOfTeams = $row['NumberOfTeams']; $divisionId = $row['DivisionID']; $genderId = $row['GenderID']; //$points = CalculatePoints($numberOfTeams, $results); $points = CalculatePoints($genderId, $divisionId, $numberOfTeams, $results); // Additional fields required $eventId = $row['EventID']; $division = $row['Division']; $partner = Player::GetTeamate($row['TeamID'], $playerId); //$genderId = $row['GenderID']; $tournamentId = $row['TournamentID']; // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId); //$ratingEarned = CalculateRatingEarned($numberOfTeams, $results); $ratingEarned = CalculateRatingEarned($genderId, $divisionId, $numberOfTeams, $results); // $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $division, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $event = PlayerEvent::GetPlayerEventComplete($tournamentId, $eventId, $eventDate, $divisionId, $partner, $results, $points, $numberOfTeams, $genderId, $ratingEarned); $eventsPlayedCompleteCollection[] = $event; } return $eventsPlayedCompleteCollection; } // TODO: Refactor when we're ready to allow > 2 person teams. private function GetTeamate($teamId, $playerId) { $query = "SELECT PlayerID, FirstName, LastName "; $query .= "FROM Players INNER JOIN Teams USING(PlayerID) "; $query .= "WHERE TeamID = $teamId AND PlayerID <> $playerId"; $result = GetQueryResult($query); while($row = mysqli_fetch_array($result)) { $partner = Player::GetPlayerBaseData($row['PlayerID'], $row['FirstName'], $row['LastName']); } return $partner; } } ?> Quote Link to comment https://forums.phpfreaks.com/topic/298595-database-clarity/ Share on other sites More sharing options...
Psycho Posted October 14, 2015 Share Posted October 14, 2015 This forum is for people to get help with code they have written. Copy/pasting a ton of code and asking us to try and decipher it is a lot to ask from people providing support for free. Can you show the specific code for retrieving/displaying the content on the players_13 page? 2 Quote Link to comment https://forums.phpfreaks.com/topic/298595-database-clarity/#findComment-1523285 Share on other sites More sharing options...
phillipv Posted October 14, 2015 Author Share Posted October 14, 2015 I'm willing to pay someone if I'm sure they can fix the problem. Trying to give as much information as possible. I might have found someone. Quote Link to comment https://forums.phpfreaks.com/topic/298595-database-clarity/#findComment-1523301 Share on other sites More sharing options...
Psycho Posted October 19, 2015 Share Posted October 19, 2015 There is a freelance forum if you want to find someone to do work for you at a price Quote Link to comment https://forums.phpfreaks.com/topic/298595-database-clarity/#findComment-1523669 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.