为什么PHP中的mySQL返回null结果？

Question

I am trying to migrate ~55K records from a mySQL server over to MongoDB. I can't do this via any of the easily accessible methods like JSON/CSV importing because the data storage method (the way it is structured) will be very different. Because of this, I have created a script in php that is designed to do this.

The issue I have been running into with this is that over large sets of data (not reproducible using smaller data sets even when the smaller set includes problem entries) the queries will occasionally report no data despite the entry existing. It absolutely exists because when php accesses that specific entry directly or it is included in a smaller data set, it works just fine. For instance, in the import to a text file, i only received ~42k/54k records.

In the echo I am receiving through the url the php file is called through, I am showing that the query is called the correct number of times, but there are many records that are showing as not existing, thus the answering echo is blank. The code is included below:

//Makes a connection to the database
$conn = makeConnection();

$filename = '/home/dbserverdownload.txt';
$file = fopen($filename, 'a');

$sql = "SELECT * FROM maintable ORDER BY ID DESC LIMIT 1";
$resultID = mysqli_query($conn, $sql);
$ID = mysqli_fetch_object($resultID);
echo $ID->ID;
//loops through the database and appends the data to the file as it goes
for($var=2; $var <= $ID->ID; $var++){
    $sql1 = "SELECT * FROM servertable WHERE ID = '$var'";
    $result1 = mysqli_query($conn, $sql1);
    $values = mysqli_fetch_object($result1);

    $id =  $values->ID;
    $ip = $values->IP;
    $port = $values->port;
    $running = $values->running;
    $afk = $values->afk;
    $gamemode = $values->gamemode;
    $maxplayers = $values->maxplayers;
    $spawnprotection = $values->spawnprotection;
    $whitelist = $values->whitelist;
    $enablequery = $values->enablequery;
    $enablercon = $values->enablercon;
    $rconpassword = $values->rconpassword;
    $motd = $values->motd;
    $achievements = $values->announceplayerachievements;
    $allowflight = $values->allowflight;
    $spawnanimals = $values->spawnanimals;
    $spawnmobs = $values->spawnmobs;
    $forcegamemode = $values->forcegamemode;
    $hardcore = $values->hardcore;
    $pvp = $values->pvp;
    $difficulty = $values->difficulty;
    $generatorsettings = $values->generatorsettings;
    $levelname = $values->levelname;
    $levelseed = $values->levelseed;
    $leveltype = $values->leveltype;
    $autosave = $values->autosave;

if($ip == "148.57.44.10"){
  //if the server is server1
  $servername = "server1".$port;
} else if ($ip == "165.108.22.199"){
  //if the server is server2
  $servername = "server2".$port;
} else{
  $servername = "";
}



//Adds all content that was already gained to the JSON string
$startingContent = "{\"_id\":\"$servername\",
                    \"ip\":\"$ip\",
                    \"port\":\"$port\",
                    \"running\":\"$running\",
                    \"afk\":\"$afk\",
                    \"gamemode\":\"$gamemode\",
                    \"maxplayers\":\"$maxplayers\",
                    \"spawnprotection\":\"$spawnprotection\",
                    \"whitelist\":\"$whitelist\",
                    \"enablequery\":\"$enablequery\",
                    \"enablercon\":\"$enablercon\",
                    \"rconpassword\":\"$rconpassword\",
                    \"motd\":\"$motd\",
                    \"announceplayerachievements\":\"$achievements\",
                    \"allowflight\":\"$allowflight\",
                    \"spawnanimals\":\"$spawnanimals\",
                    \"spawnmobs\":\"$spawnmobs\",
                    \"forcegamemode\":\"$forcegamemode\",
                    \"hardcore\":\"$hardcore\",
                    \"pvp\":\"$pvp\",
                    \"difficulty\":\"$difficulty\",
                    \"generatorsettings\":\"$generatorsettings\",
                    \"levelname\":\"$levelname\",
                    \"levelseed\":\"$levelseed\",
                    \"leveltype\":\"$leveltype\",
                    \"autosave\":\"$autosave\"
                  }";



echo $startingContent."<br/>";


//This is the JSON data that will be passed to mongo

if(strlen($ip)>6){
    if (fwrite($file, $startingContent) === FALSE) {
        echo "Cannot write to file ($filename) with $startingContent";
        exit;
    }
}
}

I have also tried this with a query that pulls a significant number(all, half, a quarter, etc) of the results in one chunk instead of tons of individual queries. The end result of that experiment was that a variable number of records were updated (usually a seemingly random number between 400 and 4000) each time it was run. Does anyone have any idea why this might be occurring? If not, should I just make my own program to iterate over the CSVs that I can export from mySQL?

Answer 1

First of all, replace your query $sql = "SELECT * FROM maintable ORDER BY ID DESC LIMIT 1"; with this $sql = "SELECT MAX(ID) as ID FROM maintable"; for better performance.

Your problem here is that you probably has a query that return false, then your application dies. Let's say, for example, your loop tries to query ID=3, but there is no row in your database with this ID. Then the next lines throws a exception, that you are not seeing. You could use set display_errors=On in you dev machine php.ini.

Use this to check if your have results to fetch:

....
for($var=2; $var <= $ID->ID; $var++){
    $sql1 = "SELECT * FROM servertable WHERE ID = '$var'";
    $result1 = mysqli_query($conn, $sql1);

    if($result) {
        $values = mysqli_fetch_object($result1);

        $id =  $values->ID;
        $ip = $values->IP;
        ....