fgets / fwrite loop php

am having a problem with a loop.

i have a text file which looks like this

AN Xixerella

AN Vila

AN Sornas

AN Soldeu

AN Sispony

AN Segudet

AN El Tarter

AN Sant Julia de Loria

AN Sant Joan de Caselles

to the exception that i have more than 2 million lines.

i need to get this into a sql request for an insert

for the last 12 hours i've been trying with no success

i've tried like this

<?php
$file = "cities.txt";
$file_w = "cities.sql";
$f = fopen($file, "r");
$nf = fopen($file_w, "w+");

for($l = 0; $l<2; $l++){


$line = fgets($f);
$ex = preg_split('/\s+/', $line);

foreach($ex as $k => $v){

    echo $ex[0].' '. $ex[1];
    echo '<br>';

    $fw = fwrite($nf, "('" . $ex[0] . "','"  . $ex[1] . "')\r\n");

}
}






fclose($f);
fclose($nf);
?>

or even like this

$file = "cities.txt";
$file_w = "cities.sql";
$f = fopen($file, "r");
$nf = fopen($file_w, "w+");

while ($line = fgets($f, 4096000))  {
    // echo $line;


$ex = preg_split('/\s+/', $line);
// var_dump($ex);die();
foreach($ex as $k => $v){

    // echo $ex[0].' '. $ex[1];
    // echo '<br>';

    $fw = fwrite($nf, "('" . $ex[0] . "','"  . $ex[1] . "')\r\n");



}
}





fclose($f);
fclose($nf);

but both times i had this in my written file

('AN','Xixerella')

('AN','Vila')

each line is being repeated 3 times and i cant figure out why.

Thanks in advance for your help

Solution

Because you have the foreach loop nested in your while loop, you're breaking your lines into too many pieces needlessly, and your logic is flawed.

<?php
$infile = "cities.txt";
$outfile = "cities.sql";
$rh = fopen($infile, "r");
$wh = fopen($outfile, "w+");

//this has to change because a blank line or a line that is simply '0' will break the loop
while( ($line = fgets($rh, 4096000)) !== false ) {
    $parts = preg_split('/\s+/', $line, 2); //limit to splitting into TWO parts, state and city.
    $state = $parts[0];
    $city = preg_replace("/'/", '&#39;', $parts[1]); //replace stray apostrophes
    $output = sprintf("('%s','%s')\r\n", $state, $city)
    fwrite($wh, $output)
}

fclose($rh);
fclose($wh);