<?php
include "../include/set_env.php";

// Note the above set_env.php file includes the database connection and uses 
// PEAR::DB to connect. The database handle is called $db below.

// This script assumes a BIS table named players with a field called playerId 
// that is compared to whatever tables are defined in the $tables array 
// below.

//select players from pitchfx table that don't have player IDs
//and try to find the matching player ID from the players table


$tables = array('pitchfx_players');

foreach($tables as $table) {
    $query = "select * from $table where playerId is null or playerId = ''";
    $res = $db->query($query);
    $total_without_playerIds = $res->numRows();
    $orphan_count = 0;
    while($row = $res->fetchRow()) {
        $first = $row['firstname'];
        $last  = $row['lastname'];
        echo $first . " " . $last . "\n";
        $query = "select * from players where firstName=\"$first\" and lastName=\"$last\"";
        $p_res = $db->query($query);
        if ($p_res->numRows() == 0) {
            $query = "select * from players where firstName like \"%$first%\"
                and lastName like \"$last%\"";
            $p_res2 = $db->query($query);
            if ($p_res2->numRows() == 0) {
                    $orphan_count ++;
            } else {
                while($p_row2 = $p_res2->fetchRow()) {
                    $playerId = $p_row2['playerId'];
                    $first2   = $p_row2['firstName'];
                    $last2    = $p_row2['lastName'];
                    echo "\t$first2 $last2 : $playerId\n";
                    if ($p_res2->numRows() == 1) {
                        $query = "update $table set playerId='$playerId'
                            where firstname=\"$first\" and lastname=\"$last\" ";
                        echo $query;
                        $db->query($query);
                    }

                }
            }
        }
        echo "\tNumber of matches: " . $p_res->numRows() . "\n";
        while($p_row = $p_res->fetchRow()) {
            $playerId = $p_row['playerId'];
            echo "\t$playerId\n";
            if($p_res->numRows() == 1) {
                $query = "update $table set playerId='$playerId'
               where firstname=\"$first\" and lastname=\"$last\"  ";
                $db->query($query);
            }
        }
    }
}

echo "\nTotal players without playerIds: $total_without_playerIds.\n";
echo "\nTotal players not found in players table: $orphan_count.\n";

?>