Jump to content

Database clarity

phillipv

By phillipv
in PHP Coding Help

 Share

Recommended Posts

phillipv Newbie

phillipv

Posted
Posted

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

Notice 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 events

Any 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;
	}
	
}
    
?>
Link to comment
https://forums.phpfreaks.com/topic/298595-database-clarity/
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

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?

  • Like 2
Link to comment
https://forums.phpfreaks.com/topic/298595-database-clarity/#findComment-1523285
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.

×
 Share
Go to topic listing
×
×
  • 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.