Geeklog Forums

function phpblock_minitable(){

include(\'/home/ginger/public_html/league/admin/user.php\');
$connection = mysql_connect(\"$host\",\"$user\",\"$password\")
or die(mysql_error());
mysql_select_db(\"$txt_db_name\",$connection)
or die(mysql_error());

//
//Season id
//
$season_id = 1;

//
//For win, draw and lost?
//
$for_win = 3;
$for_draw = 1;
$for_lose = 0;

//
//Query to get teams from selected season
//
$get_teams = mysql_query(\"
SELECT DISTINCT
O.OpponentName AS name,
O.OpponentID AS id
FROM tplls_opponents O, tplls_leaguematches LM
WHERE LM.LeagueMatchSeasonID LIKE \'$season_id\' AND
(O.OpponentID = LM.LeagueMatchHomeID OR
O.OpponentID = LM.LeagueMatchAwayID)
ORDER BY name
\", $connection)
or die(mysql_error());

//
//Lets read teams into the table
//
$i = 0;
while($data = mysql_fetch_array($get_teams))
{
        $team[$i] = $data[\'name\'];
        $teamid[$i] = $data[\'id\'];

        //
        //Home data
        //
        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS homewins
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchHomeWinnerID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Home wins into the table
        //
        $mdata = mysql_fetch_array($query);
        $homewins[$i] = $mdata[\'homewins\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS homedraws
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchHomeTieID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Home draws into the table
        //
        $mdata = mysql_fetch_array($query);
        $homedraws[$i] = $mdata[\'homedraws\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS homeloses
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchHomeLoserID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Home loses into the table
        //
        $mdata = mysql_fetch_array($query);
        $homeloses[$i] = $mdata[\'homeloses\'];

        mysql_free_result($query);


        //
        //Away data
        //
        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS awaywins
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchAwayWinnerID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Away wins into the table
        //
        $mdata = mysql_fetch_array($query);
        $awaywins[$i] = $mdata[\'awaywins\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS awaydraws
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchAwayTieID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Away draws into the table
        //
        $mdata = mysql_fetch_array($query);
        $awaydraws[$i] = $mdata[\'awaydraws\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        COUNT(DISTINCT LM.LeagueMatchID) AS awayloses
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchAwayLoserID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Away loses into the table
        //
        $mdata = mysql_fetch_array($query);
        $awayloses[$i] = $mdata[\'awayloses\'];

        mysql_free_result($query);

        //
        //Query to get goals
        //

        $query = mysql_query(\"
        SELECT
        SUM( LM.LeagueMatchHomeGoals) AS homegoals
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchHomeID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Goals scored in hom
        //
        $mdata = mysql_fetch_array($query);
        if(is_null($mdata[\'homegoals\']))
                $homegoals[$i] = 0;
        else
                $homegoals[$i] = $mdata[\'homegoals\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        SUM( LM.LeagueMatchAwayGoals) AS homegoalsagainst
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchHomeID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Goals against in home
        //
        $mdata = mysql_fetch_array($query);
        if(is_null($mdata[\'homegoalsagainst\']))
                $homegoalsagainst[$i] = 0;
        else
                $homegoalsagainst[$i] = $mdata[\'homegoalsagainst\'];

        mysql_free_result($query);


        $query = mysql_query(\"
        SELECT
        SUM( LM.LeagueMatchAwayGoals) AS awaygoals
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchAwayID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Goals scored in away
        //
        $mdata = mysql_fetch_array($query);
        if(is_null($mdata[\'awaygoals\']))
                $awaygoals[$i] = 0;
        else
                $awaygoals[$i] = $mdata[\'awaygoals\'];

        mysql_free_result($query);

        $query = mysql_query(\"
        SELECT
        SUM( LM.LeagueMatchHomeGoals) AS awaygoalsagainst
        FROM
        tplls_leaguematches LM
        WHERE
        LM.LeagueMatchAwayID = \'$teamid[$i]\' AND
        LM.LeagueMatchSeasonID LIKE \'$season_id\'
        \", $connection)
        or die(mysql_error());

        //
        //Goals against in away
        //
        $mdata = mysql_fetch_array($query);
        if(is_null($mdata[\'awaygoalsagainst\']))
                $awaygoalsagainst[$i] = 0;
        else
                $awaygoalsagainst[$i] = $mdata[\'awaygoalsagainst\'];

        mysql_free_result($query);


        //
        //Calculates points and matches
        //

        $wins[$i] = ($homewins[$i]+$awaywins[$i]);
        $draws[$i] = ($homedraws[$i]+$awaydraws[$i]);
        $loses[$i] = ($homeloses[$i]+$awayloses[$i]);
        $goals_for[$i] = ($homegoals[$i] + $awaygoals[$i]);
        $goals_against[$i] = ($homegoalsagainst[$i] + $awaygoalsagainst[$i]);

        //
        //Lets make change in points if there are data in tplls_deductedpoints-table
        //
        $get_deduct = mysql_query(\"
        SELECT points
        FROM tplls_deductedpoints
        WHERE seasonid LIKE \'$season_id\' AND
        teamid = \'$teamid[$i]\'
        LIMIT 1
        \", $connection)
        or die(mysql_error());

        $temp_points = 0;

        if(mysql_num_rows($get_deduct) > 0)
        {
                while($d_points = mysql_fetch_array($get_deduct))
                {
                        $temp_points = $temp_points + $d_points[\'points\'];
                }
        }

        mysql_free_result($get_deduct);


        $points[$i] = $temp_points + (($homewins[$i]+$awaywins[$i])*$for_win) + (($homedraws[$i]+$awaydraws[$i])*$for_draw) + (($homeloses[$i]+$awayloses[$i])*$for_lose);
        $pld[$i] = $homewins[$i]+$homedraws[$i]+$homeloses[$i]+$awaywins[$i]+$awaydraws[$i]+$awayloses[$i];

        //
        //Calculates goal difference
        //
        $diff[$i] = ($homegoals[$i] + $awaygoals[$i]) - ($homegoalsagainst[$i] + $awaygoalsagainst[$i]);

        $i++;

}
$qty = mysql_num_rows($get_teams);

mysql_free_result($get_teams);
//
//Sort by points
//
array_multisort($points, SORT_DESC, SORT_NUMERIC, $diff, SORT_DESC, SORT_NUMERIC, $goals_for, SORT_DESC, SORT_NUMERIC, $wins, SORT_DESC, SORT_NUMERIC, $goals_against, SORT_ASC, SORT_NUMERIC, $draws, $loses, $pld, SORT_DESC, SORT_NUMERIC, $team, $homewins, $homedraws, $homeloses, $awaywins, $awaydraws, $awayloses, $homegoals, $homegoalsagainst, $awaygoals, $awaygoalsagainst);

?>