_db_lbs.php

<?php
require_once('db.inc.php');
//////////////////////////////////////////////////////////////////////////////////////////
//	Leaderboards
//////////////////////////////////////////////////////////////////////////////////////////

function SubmitLeaderboardEntryJSON( $user, $lbID, $newEntry, $validation )
{
    global $db;

    $retVal = array();
    $retVal[ 'Success' ] = TRUE;

    //	Fetch some always-needed data
    $query = "SELECT Format, ID AS LeaderboardID, GameID, Title, LowerIsBetter
			  FROM LeaderboardDef AS ld
			  WHERE ld.ID = $lbID";
    $dbResult = s_mysql_query( $query );
    SQL_ASSERT( $dbResult );
    if( $dbResult !== FALSE )
    {
        $lbData = mysqli_fetch_assoc( $dbResult );

        $lbID = $lbData[ 'LeaderboardID' ];
        $gameID = $lbData[ 'GameID' ];
        $lbTitle = $lbData[ 'Title' ];
        $lowerIsBetter = $lbData[ 'LowerIsBetter' ];
        settype( $lowerIsBetter, 'integer' );

        $scoreFormatted = GetFormattedLeaderboardEntry( $lbData[ 'Format' ], $newEntry );

        $retVal[ 'LBData' ] = $lbData;
        settype( $retVal[ 'LBData' ][ 'LeaderboardID' ], 'integer' );
        settype( $retVal[ 'LBData' ][ 'GameID' ], 'integer' );
        settype( $retVal[ 'LBData' ][ 'LowerIsBetter' ], 'integer' );

        $retVal[ 'Score' ] = $newEntry;
        settype( $retVal[ 'Score' ], 'integer' );
        $retVal[ 'ScoreFormatted' ] = $scoreFormatted;

        $scoreData = Array();
        $scoreData[ 'Score' ] = $newEntry;
        settype( $scoreData[ 'Score' ], 'integer' );
        $scoreData[ 'GameID' ] = $gameID;
        settype( $scoreData[ 'GameID' ], 'integer' );
        $scoreData[ 'ScoreFormatted' ] = $scoreFormatted;
        $scoreData[ 'LBTitle' ] = $lbTitle;
        $scoreData[ 'LBID' ] = $lbID;
        settype( $scoreData[ 'LBID' ], 'integer' );

        $comparisonOp = ( $lowerIsBetter == 1 ) ? '<' : '>';

        //	Read: IF the score VALUE provided $compares as "betterthan" the existing score, use the VALUE given, otherwise the existing Score.
        //	Also, if the score VALUE provided $compares as "betterthan" the existing score, use the VALUE of the DateSubmitted given, otherwise the existing DateSubmitted.
        $query = "INSERT INTO LeaderboardEntry
					VALUES( '$lbID', (SELECT ID FROM UserAccounts WHERE User='$user'), '$newEntry', NOW() )
				  ON DUPLICATE KEY UPDATE
					LeaderboardID=LeaderboardID,
					UserID=UserID,
					Score=IF( ( VALUES(Score) $comparisonOp Score ), VALUES(Score), Score ),
					DateSubmitted=IF( ( VALUES(Score) $comparisonOp Score ), VALUES(DateSubmitted), DateSubmitted )";

        log_sql( $query );
        $dbResult = s_mysql_query( $query );

        if( $dbResult !== FALSE )
        {
            $numRowsAffected = mysqli_affected_rows( $db );
            if( $numRowsAffected == 0 )
            {
                //	No change made!
                //	Worst case: go fetch my existing score, it was better
                $query = "SELECT Score FROM LeaderboardEntry WHERE LeaderboardID=$lbID AND UserID=(SELECT ID FROM UserAccounts WHERE User='$user')";
                $dbResult = s_mysql_query( $query );
                SQL_ASSERT( $dbResult );
                $data = mysqli_fetch_assoc( $dbResult );
                $retVal[ 'BestScore' ] = $data[ 'Score' ];
            }
            else if( $numRowsAffected == 1 )
            {
                //	(New) Entry added!
                $retVal[ 'BestScore' ] = $newEntry;
                postActivity( $user, ActivityType::NewLeaderboardEntry, $scoreData );
            }
            else //if( $numRowsAffected == 2 )
            {
                //	Improved Entry added!
                $retVal[ 'BestScore' ] = $newEntry;
                postActivity( $user, ActivityType::ImprovedLeaderboardEntry, $scoreData );
            }

            settype( $retVal[ 'BestScore' ], 'integer' );

            //	If you fall through to here, populate $dataOut with some juicy info :)
            $retVal[ 'TopEntries' ] = GetLeaderboardEntriesDataJSON( $lbID, $user, 10, 0, FALSE );
            $retVal[ 'TopEntriesFriends' ] = GetLeaderboardEntriesDataJSON( $lbID, $user, 10, 0, TRUE );
            $retVal[ 'RankInfo' ] = GetLeaderboardRankingJSON( $user, $lbID );
        }
        else
        {
            error_log( __FUNCTION__ . " broken: " . mysqli_error( $db ) );
            $retVal[ 'Success' ] = FALSE;
            $retVal[ 'Error' ] = "Cannot insert the value $newEntry into leaderboard with ID: $lbID (unknown issue)";
        }
    }
    else
    {
        error_log( __FUNCTION__ . " broken2: " . mysqli_error( $db ) );
        $retVal[ 'Success' ] = FALSE;
        $retVal[ 'Error' ] = "Cannot find the leaderboard with ID: $lbID";
    }
    return $retVal;
}

function submitLeaderboardEntry( $user, $lbID, $newEntry, $validation, &$dataOut )
{
    //	Fetch some always-needed data
    $query = "SELECT Format, ID, GameID, Title
			  FROM LeaderboardDef AS ld
			  WHERE ld.ID = $lbID";
    $dbResult = s_mysql_query( $query );
    $data = mysqli_fetch_assoc( $dbResult );

    $gameID = $data[ 'GameID' ];
    $lbTitle = $data[ 'Title' ];
    $lbID = $data[ 'ID' ];
    $scoreFormatted = GetFormattedLeaderboardEntry( $data[ 'Format' ], $newEntry );

    $scoreData = Array();
    $scoreData[ 'Score' ] = $newEntry;
    $scoreData[ 'GameID' ] = $gameID;
    $scoreData[ 'ScoreFormatted' ] = $scoreFormatted;
    $scoreData[ 'LBTitle' ] = $lbTitle;
    $scoreData[ 'LBID' ] = $lbID;

    $query = "SELECT le.Score, ld.LowerIsBetter, ld.Format FROM LeaderboardEntry AS le
			  LEFT JOIN LeaderboardDef AS ld ON ld.ID = le.LeaderboardID
			  LEFT JOIN UserAccounts AS ua ON ua.ID = le.UserID
			  WHERE ua.User = '$user' AND ld.ID = $lbID ";

    $bestScore = $newEntry;

    $dbResult = s_mysql_query( $query );
    if( $dbResult == FALSE )
    {
        error_log( $query );
        log_email( __FUNCTION__ . " failed 1 for $user!" );
        return FALSE;
    }
    else
    {
        //error_log( $query );

        if( mysqli_num_rows( $dbResult ) == 0 )
        {
            //	No data found; add new element!
            $query = "INSERT INTO LeaderboardEntry ";
            $query .= "VALUES ( $lbID, (SELECT ID FROM UserAccounts WHERE User='$user'), $newEntry, NOW() ) ";
            log_sql( $query );

            $dbResult = s_mysql_query( $query );
            if( $dbResult == FALSE )
            {
                error_log( $query );
                log_email( __FUNCTION__ . " failed 2 for $user!" );
                return FALSE;
            }
            else
            {
                postActivity( $user, ActivityType::NewLeaderboardEntry, $scoreData );

                //error_log( $query );
                //log_email( __FUNCTION__ . " NEW entry added OK for $user, new record $newEntry for leaderboard $lbID!" );
            }
        }
        else
        {
            $dataFound = mysqli_fetch_assoc( $dbResult );
            $oldEntry = $dataFound[ 'Score' ];
            $lowerIsBetter = $dataFound[ 'LowerIsBetter' ];
            $format = $dataFound[ 'Format' ];

            settype( $lowerIsBetter, 'integer' );
            settype( $newEntry, 'integer' );
            settype( $oldEntry, 'integer' );

            $newScoreOK = FALSE;
            if( $lowerIsBetter )
                $newScoreOK = ($newEntry < $oldEntry);
            else
                $newScoreOK = ($newEntry > $oldEntry);

            if( $newScoreOK )
            {
                $scoreFormatted = GetFormattedLeaderboardEntry( $format, $newEntry );

                //	worse record found: update with new data!
                $query = "UPDATE LeaderboardEntry AS le ";
                $query .= "LEFT JOIN UserAccounts AS ua ON ua.User = '$user' ";
                $query .= "SET le.Score=$newEntry, le.DateSubmitted = NOW() ";
                $query .= "WHERE le.LeaderboardID = $lbID AND le.UserID = ua.ID ";

                $dbResult = s_mysql_query( $query );
                if( $dbResult == FALSE )
                {
                    error_log( $query );
                    log_email( __FUNCTION__ . " failed 3 for $user!" );
                    return FALSE;
                }
                else
                {
                    postActivity( $user, ActivityType::ImprovedLeaderboardEntry, $scoreData );

                    //error_log( $query );
                    //log_email( __FUNCTION__ . " entry added OK for $user, new record $newEntry (was $oldEntry) for leaderboard $lbID!" );
                }
            }
            else
            {
                $bestScore = $oldEntry;

                //error_log( $query );
                //error_log( __FUNCTION__ );
                //error_log( "old score for $user ($oldEntry) is already better than new value ($newEntry), keeping old value." );
            }
        }
    }

    //	If you fall through to here, populate $dataOut with some juicy info :)
    $dataOut = GetLeaderboardData( $lbID, $user, 5, 0, 0 );

    getLeaderboardRanking( $user, $lbID, $ranking, $totalEntries );
    $dataOut[ 'Rank' ] = $ranking;
    $dataOut[ 'NumEntries' ] = $totalEntries;
    $dataOut[ 'Score' ] = $bestScore;

    return TRUE;
}

function RemoveLeaderboardEntry( $user, $lbID )
{
    $userID = getUserIDFromUser( $user );
    if( $userID > 0 )
    {
        $query = "DELETE FROM LeaderboardEntry
				  WHERE ( LeaderboardID = $lbID AND UserID = $userID )";

        s_mysql_query( $query );

        global $db;
        if( mysqli_affected_rows( $db ) > 0 )
        {
            error_log( "Dropped $user 's LB entry from Leaderboard ID $lbID" );
            return TRUE;
        }
        else
        {
            return FALSE;
        }
    }
    else
    {
        error_log( "Could not find user ID for $user" );
        return FALSE;
    }
}

//	08:52 05/11/2014
function GetLeaderboardRankingJSON( $user, $lbID )
{
    $retVal = array();

    $query = "SELECT COUNT(*) AS Rank,
				(SELECT ld.LowerIsBetter FROM LeaderboardDef AS ld WHERE ld.ID=$lbID) AS LowerIsBetter,
				(SELECT COUNT(*) AS NumEntries FROM LeaderboardEntry AS le WHERE le.LeaderboardID=$lbID) AS NumEntries
			  FROM LeaderboardEntry AS lbe
			  INNER JOIN LeaderboardEntry AS lbe2 ON lbe.LeaderboardID = lbe2.LeaderboardID AND lbe.Score < lbe2.Score
			  LEFT JOIN UserAccounts AS ua ON ua.ID = lbe.UserID
			  WHERE ua.User = '$user' AND lbe.LeaderboardID = $lbID ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        $retVal = mysqli_fetch_assoc( $dbResult );

        //	Query actually gives 'how many players are below me in the list.'
        //	Top position yields '0', which we should change to '1' for '1st'
        //	Reversing the list means we wouldn't need to do this however: Rank 0 becomes 5-0: 5th of 5.
        if( $retVal[ 'LowerIsBetter' ] == 1 )
            $retVal[ 'Rank' ] = ( $retVal[ 'NumEntries' ] - $retVal[ 'Rank' ] );
        else
            $retVal[ 'Rank' ] += 1;      //	0=1st place.
    }

    return $retVal;
}

//	Deprecate: fold into above
function getLeaderboardRanking( $user, $lbID, &$rankOut, &$totalEntries )
{
    $query = "SELECT
			  COUNT(*) AS Rank,
			  (SELECT ld.LowerIsBetter FROM LeaderboardDef AS ld WHERE ld.ID=$lbID) AS LowerIsBetter,
			  (SELECT COUNT(*) AS NumEntries FROM LeaderboardEntry AS le WHERE le.LeaderboardID=$lbID) AS NumEntries
			  FROM LeaderboardEntry AS lbe
			  INNER JOIN LeaderboardEntry AS lbe2 ON lbe.LeaderboardID = lbe2.LeaderboardID AND lbe.Score < lbe2.Score
			  LEFT JOIN UserAccounts AS ua ON ua.ID = lbe.UserID
			  WHERE ua.User = '$user' AND lbe.LeaderboardID = $lbID ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        $db_entry = mysqli_fetch_assoc( $dbResult );

        $rankOut = $db_entry[ 'Rank' ];
        $totalEntries = $db_entry[ 'NumEntries' ];

        //	Query actually gives 'how many players are below me in the list.'
        //	Top position yields '0', which we should change to '1' for '1st'
        //	Reversing the list means we wouldn't need to do this however: Rank 0 becomes 5-0: 5th of 5.
        if( $db_entry[ 'LowerIsBetter' ] == 1 )
            $rankOut = ( $totalEntries - $rankOut );
        else
            $rankOut += 1;      //	0=1st place.

        return TRUE;
    }
    else
    {
        error_log( __FUNCTION__ . " error" );
        error_log( $query );
        return FALSE;
    }
}

function getLeaderboardsForGame( $gameID, &$dataOut, $localUser )
{
    $query = "SELECT InnerTable.LeaderboardID, InnerTable.Title, InnerTable.Description, le.DateSubmitted, ua.User, le.Score, InnerTable.Format FROM (
				SELECT
				CASE
					WHEN !lbd.LowerIsBetter THEN MAX(le2.Score)
											ELSE MIN(le2.Score)
				END
				AS BestScore, le2.UserID, le2.LeaderboardID, lbd.Title, lbd.Description, lbd.Format
				FROM LeaderboardEntry AS le2
				LEFT JOIN LeaderboardDef AS lbd ON lbd.ID = le2.LeaderboardID
                LEFT JOIN UserAccounts AS ua ON ua.ID = le2.UserID
				WHERE ( !ua.Untracked || ua.User = \"$localUser\" ) && lbd.GameID = $gameID
				GROUP BY lbd.ID
			) InnerTable
			LEFT JOIN LeaderboardEntry AS le ON le.LeaderboardID = InnerTable.LeaderboardID AND le.Score = InnerTable.BestScore
			LEFT JOIN UserAccounts AS ua ON ua.ID = le.UserID
			ORDER BY LeaderboardID, DateSubmitted ASC ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        while( $data = mysqli_fetch_assoc( $dbResult ) )
        {
            $lbID = $data[ 'LeaderboardID' ];
            if( !isset( $dataOut[ $lbID ] ) )
            {
                $dataOut[ $lbID ] = $data; //	Potentially overwrites existing one
            }
        }
    }
    else
    {
        global $db;
        error_log( __FUNCTION__ . " error: " . mysqli_error( $db ) );
        //error_log( $query );
    }

    return count( $dataOut );
}

//	15:21 16/10/2014
function GetLeaderboardEntriesDataJSON( $lbID, $user, $numToFetch, $offset, $friendsOnly )
{
    $retVal = array();

    //	'Me or my friends'
    $friendQuery = $friendsOnly ? "( ( ua.User IN ( SELECT Friend FROM Friends WHERE User='$user' ) ) OR ua.User='$user' )" : "TRUE";

    //	Get entries:
    $query = "SELECT ua.User, le.Score, UNIX_TIMESTAMP( le.DateSubmitted ) AS DateSubmitted
			  FROM LeaderboardEntry AS le
			  LEFT JOIN UserAccounts AS ua ON ua.ID = le.UserID
			  LEFT JOIN LeaderboardDef AS lbd ON lbd.ID = le.LeaderboardID
			  WHERE le.LeaderboardID = $lbID AND $friendQuery
			  ORDER BY
			  CASE WHEN !lbd.LowerIsBetter THEN Score END DESC,
			  CASE WHEN lbd.LowerIsBetter THEN Score END ASC, DateSubmitted ASC
			  LIMIT $offset, $numToFetch ";

    $dbResult = s_mysql_query( $query );
    SQL_ASSERT( $dbResult );
    $numFound = 0;
    while( $nextData = mysqli_fetch_assoc( $dbResult ) )
    {
        $nextData[ 'Rank' ] = $numFound + $offset + 1;
        settype( $nextData[ 'Score' ], 'integer' );
        settype( $nextData[ 'DateSubmitted' ], 'integer' );
        $retVal[] = $nextData;
        $numFound++;
    }
    return $retVal;
}

//	15:21 16/10/2014
function GetLeaderboardData( $lbID, $user, $numToFetch, $offset, $friendsOnly )
{
    $retVal = array();

    //	Get raw LB data
    $query = "SELECT ld.ID AS LBID, gd.ID AS GameID, gd.Title AS GameTitle, ld.LowerIsBetter, ld.Title AS LBTitle, ld.Description AS LBDesc, ld.Format AS LBFormat, ld.Mem AS LBMem, gd.ConsoleID, c.Name AS ConsoleName, gd.ForumTopicID, gd.ImageIcon AS GameIcon
			  FROM LeaderboardDef AS ld
			  LEFT JOIN GameData AS gd ON gd.ID = ld.GameID
			  LEFT JOIN Console AS c ON c.ID = gd.ConsoleID
			  WHERE ld.ID = $lbID ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        $retVal = mysqli_fetch_assoc( $dbResult );
        settype( $retVal[ 'LBID' ], 'integer' );
        settype( $retVal[ 'GameID' ], 'integer' );
        settype( $retVal[ 'LowerIsBetter' ], 'integer' );
        settype( $retVal[ 'ConsoleID' ], 'integer' );
        settype( $retVal[ 'ForumTopicID' ], 'integer' );

        $retVal[ 'Entries' ] = array();

        //	Now get entries:
        $query = "SELECT ua.User, le.Score, le.DateSubmitted
				  FROM LeaderboardEntry AS le
				  LEFT JOIN UserAccounts AS ua ON ua.ID = le.UserID
				  LEFT JOIN LeaderboardDef AS lbd ON lbd.ID = le.LeaderboardID
				  WHERE (!ua.Untracked || ua.User = \"$user\" ) AND le.LeaderboardID = $lbID
				  ORDER BY
				  CASE WHEN !lbd.LowerIsBetter THEN Score END DESC,
				  CASE WHEN lbd.LowerIsBetter THEN Score END ASC, DateSubmitted ASC
				  LIMIT $offset, $numToFetch ";

        $dbResult = s_mysql_query( $query );
        if( $dbResult !== FALSE )
        {
            $numResultsFound = 0;
            $userFound = FALSE;

            $entries = array();

            while( $db_entry = mysqli_fetch_assoc( $dbResult ) )
            {
                $db_entry[ 'Rank' ] = $numResultsFound + $offset + 1;
                $db_entry[ 'DateSubmitted' ] = strtotime( $db_entry[ 'DateSubmitted' ] );
                settype( $db_entry[ 'Score' ], 'integer' );

                if( strcmp( $db_entry[ 'User' ], $user ) == 0 )
                    $userFound = TRUE;

                $entries[] = $db_entry;

                //$retVal['Entries'][ $db_entry['Rank'] ] = $db_entry;
                //$retVal[] = $db_entry;

                $numResultsFound++;
            }

            if( $userFound == FALSE )
            {
                //	Go find user's score in this table, if it exists!
                $query = "SELECT ua.User, le.Score, le.DateSubmitted
						  FROM LeaderboardEntry AS le
						  LEFT JOIN UserAccounts AS ua ON ua.ID = le.UserID
						  WHERE le.LeaderboardID = $lbID AND ua.User='$user' ";
                $dbResult = s_mysql_query( $query );
                if( $dbResult !== FALSE )
                {
                    if( mysqli_num_rows( $dbResult ) > 0 )
                    {
                        //	should be 1 or 0?.. I hope...
                        $db_entry = mysqli_fetch_assoc( $dbResult );
                        $db_entry[ 'DateSubmitted' ] = strtotime( $db_entry[ 'DateSubmitted' ] );
                        settype( $db_entry[ 'Score' ], 'integer' );

                        //	Also fetch our user rank!
                        getLeaderboardRanking( $user, $lbID, $userRank, $totalEntries );
                        $db_entry[ 'Rank' ] = $userRank;

                        //$retVal['Entries'][ $db_entry['Rank'] ] = $db_entry;
                        $entries[] = $db_entry;
                    }
                }
                else
                {
                    error_log( __FUNCTION__ . " error: user doesn't have an entry for this leaderboard table?" );
                    error_log( $query );
                }
            }

            $retVal[ 'Entries' ] = $entries;
        }
        else
        {
            error_log( __FUNCTION__ . " error" );
            error_log( $query );
        }
    }

    return $retVal;
}

function GetFormattedLeaderboardEntry( $formatType, $scoreIn )
{
    settype( $scoreIn, 'integer' );

    if( $formatType == 'TIME' )
    {
        $mins = $scoreIn / 3600;
        $secs = ($scoreIn % 3600) / 60;
        $milli = ( ($scoreIn % 3600) % 60 ) * ( 100.0 / 60.0 );
        settype( $mins, 'integer' );
        settype( $secs, 'integer' );
        settype( $milli, 'integer' );
        return sprintf( "%01d:%02d.%02d", $mins, $secs, $milli );
    }
    else if( $formatType == 'TIMESECS' )
    {
        $mins = $scoreIn / 60;
        $secs = $scoreIn % 60;
        return sprintf( "%01d:%02d", $mins, $secs );
    }
    else if( $formatType == 'MILLISECS' )
    {
        $mins = $scoreIn / 6000;
        $secs = ($scoreIn % 6000) / 100;
        $milli = ($scoreIn % 100);
        settype( $mins, 'integer' );
        settype( $secs, 'integer' );
        settype( $milli, 'integer' );
        return sprintf( "%01d:%02d.%02d", $mins, $secs, $milli );
    }
    else
    {
        return "$scoreIn";
    }
}

function getLeaderboardDataSmall( $lbID, &$lbTitleOut, &$lbDescOut, &$lbFormatOut, &$lbLowerIsBetterOut, &$gameIDOut, &$gameTitleOut, &$forumTopicIDOut, &$consoleIDOut, &$consoleTitleOut )
{
    $query = "SELECT ld.Title, Description, GameID, Format, LowerIsBetter, gd.Title AS GameTitle, gd.ConsoleID, gd.ForumTopicID, c.Name AS ConsoleTitle ";
    $query .= "FROM LeaderboardDef AS ld ";
    $query .= "LEFT JOIN GameData AS gd ON gd.ID = ld.GameID ";
    $query .= "LEFT JOIN Console AS c ON c.ID = gd.ConsoleID ";
    $query .= "WHERE ld.ID = $lbID ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        $db_entry = mysqli_fetch_assoc( $dbResult );

        $lbTitleOut = $db_entry[ 'Title' ];
        $lbDescOut = $db_entry[ 'Description' ];
        $lbFormatOut = $db_entry[ 'Format' ];
        $lbLowerIsBetterOut = $db_entry[ 'LowerIsBetter' ];
        $gameIDOut = $db_entry[ 'GameID' ];
        $gameTitleOut = $db_entry[ 'GameTitle' ];
        $forumTopicIDOut = $db_entry[ 'ForumTopicID' ];
        $consoleIDOut = $db_entry[ 'ConsoleID' ];
        $consoleTitleOut = $db_entry[ 'ConsoleTitle' ];

        return TRUE;
    }
    else
    {
        error_log( __FUNCTION__ . " error" );
        error_log( $query );
        return FALSE;
    }
}

//	23:18 23/04/2013
function getLeaderboardsList( $consoleIDInput, $gameID, $sortBy, $count, $offset, &$lbDataOut )
{
    settype( $sortBy, 'integer' );
    settype( $consoleIDInput, 'integer' );
    settype( $gameID, 'integer' );

    $lbCount = 0;

    $whereClause = "";

    if( $gameID != 0 )
        $whereClause = "WHERE gd.ID = $gameID";
    else if( $consoleIDInput != 0 )
        $whereClause = "WHERE gd.ConsoleID = $consoleIDInput";

    $ifDesc = "";
    if( $sortBy >= 10 )
        $ifDesc = " DESC";

    $orderClause = "";
    switch( $sortBy % 10 )
    {
        case 0:
            $orderClause = "ORDER BY ld.DisplayOrder $ifDesc, c.ID, GameTitle";
            break;
        case 1:
            $orderClause = "ORDER BY ld.ID $ifDesc";
            break;
        case 2:
            $orderClause = "ORDER BY GameTitle $ifDesc";
            break;
        case 3:
            $orderClause = "ORDER BY ConsoleName $ifDesc, c.ID, GameTitle";
            break;
        case 4:
            $orderClause = "ORDER BY ld.Title $ifDesc";
            break;
        case 5:
            $orderClause = "ORDER BY ld.Description $ifDesc";
            break;
        case 6:
            $orderClause = "ORDER BY ld.LowerIsBetter $ifDesc, ld.Format $ifDesc";
            break;
        case 7:
            if( $sortBy == 17 )
                $ifDesc = "ASC";
            else
                $ifDesc = "DESC";

            $orderClause = "ORDER BY NumResults $ifDesc";
            break;
        default:
            $orderClause = "ORDER BY ld.ID $ifDesc";
            break;
    }

    $query = "SELECT ld.ID, ld.Title, ld.Description, ld.Format, ld.Mem, ld.DisplayOrder, leInner.NumResults, ld.LowerIsBetter, gd.ID AS GameID, gd.ImageIcon AS GameIcon, gd.Title AS GameTitle, c.Name AS ConsoleName
				FROM LeaderboardDef AS ld
				LEFT JOIN GameData AS gd ON gd.ID = ld.GameID
				LEFT JOIN
				(
					SELECT le.LeaderboardID, COUNT(*) AS NumResults FROM LeaderboardEntry AS le
					GROUP BY le.LeaderboardID
					) AS leInner ON leInner.LeaderboardID = ld.ID
				LEFT JOIN Console AS c ON c.ID = gd.ConsoleID

				$whereClause

				GROUP BY ld.GameID, ld.ID
				$orderClause
				";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        while( $db_entry = mysqli_fetch_assoc( $dbResult ) )
        {
            $lbDataOut[ $lbCount ] = $db_entry;
            $lbCount++;
        }
    }
    else
    {
        error_log( __FUNCTION__ );
        error_log( $query );
    }

    return $lbCount;
}

//	22:52 07/09/2013
function submitLBData( $user, $lbID, $lbMem, $lbTitle, $lbDescription, $lbFormat, $lbLowerIsBetter, $lbDisplayOrder )
{
    settype( $lbDisplayOrder, 'integer' );

    $query = "UPDATE LeaderboardDef AS ld SET
			  ld.Mem = '$lbMem',
			  ld.Format = '$lbFormat',
			  ld.Title = '$lbTitle',
			  ld.Description = '$lbDescription',
			  ld.Format = '$lbFormat',
			  ld.LowerIsBetter = '$lbLowerIsBetter',
			  ld.DisplayOrder = '$lbDisplayOrder'
			  WHERE ld.ID = $lbID";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        error_log( __FILE__ );
        error_log( "$user changed Leaderboard $lbID: $lbMem, $lbTitle, $lbDescription, $lbFormat, $lbLowerIsBetter, $lbDisplayOrder" );
        return true;
    }
    else
    {
        log_email( __FUNCTION__ . " LB catastrophic: $user _ $lbID: $lbMem, $lbTitle, $lbDescription, $lbFormat, $lbLowerIsBetter, $lbDisplayOrder" );
        error_log( __FILE__ );
        error_log( "$user broke Leaderboard $lbID: $lbMem, $lbTitle, $lbDescription, $lbFormat, $lbLowerIsBetter, $lbDisplayOrder" );
        return false;
    }
}

//	17:04 22/10/2014
function SubmitNewLeaderboard( $gameID, &$lbIDOut )
{
    settype( $gameID, 'integer' );
    if( $gameID == 0 )
        return FALSE;

    $defaultMem = "STA:0x0000=h0010_0xhf601=h0c::CAN:0xhfe13<d0xhfe13::SUB:0xf7cc!=0_d0xf7cc=0::VAL:0xhfe24*1_0xhfe25*60_0xhfe22*3600";
    $query = "INSERT INTO LeaderboardDef VALUES ( 0, $gameID, '$defaultMem', 'SCORE', 'My Leaderboard', 'My Leaderboard Description', 0, 0 )";
    log_sql( $query );
    $dbResult = s_mysql_query( $query );
    if( $dbResult !== FALSE )
    {
        global $db;
        $lbIDOut = mysqli_insert_id( $db );
        return TRUE;
    }
    else
    {
        return FALSE;
    }
}

//	15:16 28/12/2013
function requestResetLB( $lbID )
{
    settype( $lbID, 'integer' );
    if( $lbID == 0 )
        return;

    error_log( __FUNCTION__ . " resetting LB $lbID" );
    $query = "DELETE FROM LeaderboardEntry
			  WHERE LeaderboardID = $lbID";
    $dbResult = s_mysql_query( $query );

    return( $dbResult !== FALSE );
}

//	19:21 28/12/2013
function requestDeleteLB( $lbID )
{
    settype( $lbID, 'integer' );
    log_email( __FUNCTION__ . " LB $lbID being deleted!" );

    $query = "DELETE FROM LeaderboardDef WHERE ID = $lbID";

    $dbResult = s_mysql_query( $query );

    return ( $dbResult !== FALSE );
}

//	11:52 31/10/2014
function GetLBPatch( $gameID )
{
    $lbData = array();

    //	Always append LBs?
    $query = "SELECT ld.ID, ld.Mem, ld.Format, ld.Title, ld.Description
			  FROM LeaderboardDef AS ld
			  WHERE ld.GameID = $gameID
			  ORDER BY ld.DisplayOrder, ld.ID ";

    $dbResult = s_mysql_query( $query );
    if( $dbResult !== false )
    {
        while( $db_entry = mysqli_fetch_assoc( $dbResult ) )
        {
            settype( $db_entry[ 'ID' ], 'integer' );
            $lbData[] = $db_entry;
        }
    }
    else
    {
        //	No leaderboards found: this is probably normal.
    }

    return $lbData;
}

?>