Dan Becker's Games Site

Update Game IDs

Purpose: Find Plays table entries with 0 gameid. Inserts proper id.

\n"; $commandDelim = "=,"; $command = strtok( $update, $commandDelim ); if (0 == strcmp( $command, "update" )) { $playid = strtok( $commandDelim ); $gameid = strtok( $commandDelim ); if (( false != $playid ) && ( false != $gameid )) { $sql = "UPDATE $playsTable SET gameid = $gameid WHERE ( playid = $playid )"; $sqlResults = sqlQuery( $sql, false ); } else { echo "Error: Garbled play or game id command=$command, playid=\"$playid\", gameid=\"$gameid\".
\n"; } } else if (0 == strcmp( $command, "insert" )) { $newName = strtok( $commandDelim ); if (( false != $newName ) && ( strlen( $newName ) > 0 )) { $sql = "INSERT INTO $gameNamesTable ( name ) VALUES ( \"$newName\" )"; $sqlResults = sqlQuery( $sql, false ); } else { echo "Error: Garbled game name for command=$command, name=\"$newName\".
\n"; } } else if (0 == strcmp( $command, "delete" )) { $playid = strtok( $commandDelim ); if (( false != $playid ) && ( strlen( $playid ) > 0 )) { $sql = "DELETE FROM $gameNamesTable WHERE ( playid = $playid )"; $sqlResults = sqlQuery( $sql, false ); } else { echo "Error: Garbled playid for command=$command, playid=\"$playid\".
\n"; } } else if (0 == strcmp( $command, "deleteall" )) { $gameName = strtok( $commandDelim ); if (( false != $gameName ) && ( strlen( $gameName ) > 0 )) { $sql = "DELETE FROM $gameNamesTable WHERE ( game = $gameName )"; $sqlResults = sqlQuery( $sql, false ); } else { echo "Error: Garbled game name for command=$command, name=\"$gameName\".
\n"; } } else { echo "Error: Unknown command \"$command\".
\n"; }}// Do a check that all game ids in Plays exist in GameNames.// Cannot do sub selects.// $sql = "SELECT playid, date// FROM $playsTable// WHERE $gameid NOT IN// ( SELECT $gameNamesTable.gameid FROM $gameNamesTable )";$sql = "SELECT playid, date, $playsTable.gameid FROM $playsTable LEFT JOIN $gameNamesTable ON $playsTable.gameid=$gameNamesTable.gameid WHERE $gameNamesTable.gameid IS NULL";$sqlResults = sqlQuery( $sql, false );$numRows = mysql_num_rows( $sqlResults );if ( $numRows > 0 ) { echo "

Warning: The following $playsTable gameids do not exist in $gameNamesTable.\n"; // Show first 10 tables with this problem showResults( $sqlResults, true, 0, 10 ); // Rewind results to first item. mysql_data_seek( $sqlResults, 0 ); // Turn result into array. Use MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH to select keying. while ( $data = mysql_fetch_array( $sqlResults, MYSQL_ASSOC )) { $playid = $data[ "playid" ]; $gameName = $data[ "game" ]; $sql = "SELECT gameid, name FROM $gameNamesTable WHERE ( name = \"$gameName\" )"; $nameResults = sqlQuery( $sql, false ); if ( 0 == mysql_num_rows( $nameResults ) ) { echo "
Did not find gameid for play $playid, name=\"$gameName\"\n"; // Output form to give user a choice. echo "

\n"; echo "\n"; // Add a few database potentials. $shortGameName = substr( $gameName, 0, 2 ); // Get first two letters of name. // echo "DEBUG: Searching on name \"$shortGameName\"\n"; // DEBUG $sql = "SELECT gameid, name FROM $gameNamesTable WHERE ( name like \"$shortGameName%\" )"; $nameResults = sqlQuery( $sql, false ); // Turn result into array. Use MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH to select keying. while ( $data = mysql_fetch_array( $nameResults, MYSQL_ASSOC )) { $gameid = $data[ "gameid" ]; $potentialName = $data[ "name" ]; echo "\n"; } // while // Add the option of adding the name to the database echo "\n"; echo "\n"; echo "\n"; echo "
$potentialName
New game name
Delete this game play
\n"; echo "
\n"; } else { if ( $nameData = mysql_fetch_array( $nameResults, MYSQL_ASSOC )) { $gameid = $nameData[ "gameid" ]; $updates = array( "gameid" => $gameid ); updateSQLValues( $playsTable, $updates, "( playid = '$playid')" ); echo "Updated gameid to $gameid for play $playid, name=\"$gameName\"
\n"; } } } // while} else echo "

All $playsTable play gameids exist in $gameNamesTable.\n";// Do a check to ensure all Play game names match all GameName game names.// $sql = "SELECT playid, date, game, name// FROM $playsTable, $gameNamesTable// WHERE (( $playsTable.gameid = $gameNamesTable.gameid ) &&// ( game != name ))";// $sqlResults = sqlQuery( $sql, false );// if ( 0 == mysql_num_rows( $sqlResults ) ) {// echo "

All $playsTable play names match $gameNamesTable game names.\n";// } else {// echo "

Warning: The following $playsTable play names do not match $gameNamesTable game names.\n";// showResults( $sqlResults, true );// }?>

User menu |Admin menu