php – 我需要一些帮助来创建一个查询和一个数据库表来追溯分配

发布时间：2020-12-13 13:11:37 所属栏目：PHP教程来源：网络整理

导读：正如我的信息所述,我继承了当地飞镖联盟的统计学家的工作. (哇哦一季20美元) 我很乐意实施ELO评级系统. 当前数据库表有超过100,000个条目. 有3种不同的游戏.单打,双打和团队. 数据库的发起者输入的游戏如下： Index Player1_num Player2_num Opp_Player1_num

正如我的信息所述,我继承了当地飞镖联盟的统计学家的工作. (哇哦一季20美元)

我很乐意实施ELO评级系统.

当前数据库表有超过100,000个条目.

有3种不同的游戏.单打,双打和团队.

数据库的发起者输入的游戏如下：

Index   Player1_num   Player2_num   Opp_Player1_num  Odd_Player2_num   H_team  V_team Season     Week   W   L  Game_type

单打游戏输入两次.

values(1,20,null,30,200,300,11,2,1,301_singles)

和

values(2,301_singles)

这样做是为了方便个人查看.

双打游戏将具有诸如的价值

(3,21,31,501_doubles)
(4,501_doubles)
(5,501_doubles)
(6,501_doubles)

同样,它是为了更容易查询查询跨季节和不同合作伙伴的赢利和损失而构建的.

团队游戏是：

(7,team_game)

所以我在表格中添加了一个match_num列,以帮助将游戏归为1个数字.

但是,我不确定如何最好地分配数字,可以理解的是,我不想手动完成所有90k条目.

注意：早期的季节是手工修改的,并非所有的双重匹配都有4个条目(有些只有1个).有些匹配也可能出现故障(而不是索引：1,2可能是1,9).

我不知道你是否需要更多信息,我不确定如何发布更大的表格示例.

Current code: 
<body>
<?php
include "inc.php";
// Select Max Match Number
        $sqlMatch="select max(match_num) from stats_results";
        $match =mysql_query($sqlMatch);
        $m= $match ? mysql_result($match,0):mysql_error();
        $matchno= $m+1; // First Match number

$game=array("'301_singles'","'cricket_singles'","'501_singles'","'301_doubles'","'cricket_doubles'");


// selects max season
    $sqlSeason="select max(season_index) from stats_season";
    $season =mysql_query($sqlSeason);
    $smax= $season ? mysql_result($season,0):mysql_error();
#          $smax=2; 
// for each season up to  max season
            for($s=1;$s<=$smax;$s++)
                    {

// Selects max week within the current season
                    $sqlWeek="select max(week) from stats_results where season=$s ";
                    $Week =mysql_query($sqlWeek);
                    $wmax= $Week ? mysql_result($Week,0):mysql_error();
#                        $wmax=2;
// for each week within current season up to the max week
                    for ($w=1;$w<=$wmax;$w++)
                            {

// each singles game
                            foreach($game as $g)
                                    {

###########################################################################################################



$result = mysql_query("SELECT * FROM stats_results where season=$s and week=$w and game_code=$g ;") or die(mysql_error());

// Put them in array
for($i = 0; $rows[$i] = mysql_fetch_assoc($result); $i++) ;

// Delete last empty one
 array_pop($rows);
//******************************************************

$matches=array();
foreach($rows as $record)
{
    // Get a unique match code for this match
    $matchid= getMatchID($record);
    // Have we seen this ID before? If yes add this record index to the existing array
    // otherwise create an array with just this value
    if (!isset($matches[$matchid])) $matches[$matchid]= array($record['index_results']); // No
    else $matches[$matchid][]= $record['index_results']; // Yes,add this Index
}
// Now $matches is an array of arrays of Index values,grouped by "match" so...

/* Sort $matches by key (see getMatchID for why!) */
ksort($matches);

// Update the table 
foreach($matches as $match) 
{

    // Create SQL instruction to set the match_num for all the Index values in each entry
       $sql= "UPDATE stats_results SET match_num = $matchno WHERE index_results IN (".implode(",",$match).")";
echo "<br>";
echo $sql;
/* Execute the SQL using your chosen method! */

// Move the match count on
$matchno++;
}
// End our loops
                                    }
                            }
                    }

function getMatchID($gamerecord) 
{
$index= "{$gamerecord['season']}-{$gamerecord['week']}-{$gamerecord['game_code']}-";


$players= array(
   $gamerecord['player1_num'],empty($gamerecord['player2_num'])?0:$gamerecord['player2_num'],$gamerecord['opp_player1_num'],empty($gamerecord['opp_player2_num'])?0:$gamerecord['opp_player2_num']
); 
// Sort the players to get them in a consistent order
sort($players);

// Add the sorted players to the index
$index.= implode('-',$players);
return $index;
}


?>
</body>

(我把它放到一个答案中,所以我可以让它更好地布局 – 但它并不是真正的“解决方案”！)

我想我要做的是尝试构建一个按游戏分组的索引值数组 – 然后您可以使用它来创建SQL来更新表.

因此,如果$rows是所有记录的数组,我们将执行类似以下伪代码的操作：

$matches= array();

foreach($rows as $record)
{
    // Get a unique match code for this match
    $matchid= getMatchID($record);
    // Have we seen this ID before? If yes add this record index to the existing array
    // otherwise create an array with just this value
    if (!isset($matches[$matchid])) $matches[$matchid]= array($record['Index']); // No
    else $matches[$matchid][]= $record['Index']; // Yes,add this Index
}

// Now $matches is an array of arrays of Index values,grouped by "match" so...

/* Sort $matches by key (see getMatchID for why!) */
ksort($matches);

// Update the table 
$matchno= 1; // First Match number
foreach($matches as $match) 
{
   // Create SQL instruction to set the match_num for all the Index values in each entry
   $sql= "UPDATE games SET match_num = $matchno WHERE Index IN (".implode(",$match).")";

   /* Execute the SQL using your chosen method! */

   // Move the match count on
   $matchno++;
}

所以离开的是getMatchID函数 – 如果我们根据每个匹配的季节,星期以及它的参与者的排序列表(并使用季节和周首先)给每个匹配一个临时ID,这对于每个游戏应该是唯一的,我们可以排序通过此指数稍后以正确的顺序获得游戏.再次粗略的伪代码,如下：

function getMatchID($gamerecord) 
{
   $index= "{$gamerecord['Season']}-{$gamerecord['Week']}-{$gamerecord['H_Team']}-{$gamerecord['V_Team']}-";

   $players= array(
       empty($gamerecord['Player1_Num'])?0:$gamerecord['Player1_Num'],empty($gamerecord['Player2_Num'])?0:$gamerecord['Player2_Num'],empty($gamerecord['Opp_Player1_Num'])?0:$gamerecord['Opp_Player1_Num'],empty($gamerecord['Opp_Player2_Num'])?0:$gamerecord['Opp_Player2_Num']
   ); 

   // Sort the players to get them in a consistent order
   sort($players);

   // Add the sorted players to the index
   $index.= implode('-',$players);

   return $index;
}

因此,无论我们看到的是哪一场比赛记录,所有好的$index都会以11-2-200-300-0-0-20-30的比例回归到你的例子中的第一场单打比赛.

这有什么意义/帮助吗？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!