CSV while loop not looping

Question

So I want to loop through 2 files (1 csv, 1 asc) and i want to loop trough both so that they both get insterted into the database but it doesnt import them.
This is the code:

<?php
    function importdb() {
        $File = 'lijst.csv';
        $File2 = 'preise.asc';
        $handle     = fopen($File, "r");
        $handle2     = fopen($File2, "r");
        $arrResult  = array();
        $arrResult2  = array();
        fgetcsv($handle);
        fgetcsv($handle);
        fgetcsv($handle);
        fgetcsv($handle);
            while(($data = fgetcsv($handle, 1000, ";")) !== FALSE || (($data2 = fgetcsv($handle2, 1000, ";"))) !== FALSE){
                include('db_config.php');
                $artikelnmr = $data[0];
                $barcode = $data[1];
                $omschrijving_nl = $data[2];
                $omschrijving_exp = $data[3];
                $bruto_prs = $data[4];
                $staffel_prs = $data[5];
                $aktie_prs = $data[6];
                $bruto_antl = $data[8];
                $staffel_antl = $data[9];
                $aktie_aantal = $data[10];
                $voorraad = $data[15];
                $leverdatum = $data[16];
                $besteld = $data[17];
                $pallet_antl = $data[19];
                $artikel_groep = $data[22];
                $extra_info = $data[27];
                $stmt = $db->prepare("INSERT INTO `producten`(`id`, `artikelnr`, `barcode`, `omschrijving_nl`, `omschrijving_exp`, `bruto_prijs`, `bruto_aant`, `staffel_prijs`, `staffel_aantal`, `aktie_prijs`, `aktie_aantal`, `voorraad`, `leverdatum`, `besteld`, `pallet_aantal`, `artikel_groep`, `extra`)
                VALUES ('', :artikelnmr,:barcode,:omschrijving_nl,:omschrijving_exp,:bruto_prijs,:bruto_aantal,:staffel_prijs,:staffel_aantal,:aktie_prijs,:aktie_aantal,:voorraad,:leverdatum,:besteld,:pallet_aantal,:artikel_groep,:extra)");
                $stmt->bindParam(":artikelnmr", $artikelnmr);
                $stmt->bindParam(":barcode", $barcode);
                $stmt->bindParam(":omschrijving_nl", $omschrijving_nl);
                $stmt->bindParam(":omschrijving_exp", $omschrijving_exp);
                $stmt->bindParam(":bruto_prijs", $bruto_prs);
                $stmt->bindParam(":bruto_aantal", $bruto_antl);
                $stmt->bindParam(":staffel_aantal", $staffel_antl);
                $stmt->bindParam(":staffel_prijs", $staffel_prs);
                $stmt->bindParam(":aktie_aantal", $aktie_aantal);
                $stmt->bindParam(":aktie_prijs", $aktie_prs);
                $stmt->bindParam(":voorraad", $voorraad);
                $stmt->bindParam(":leverdatum", $leverdatum);
                $stmt->bindParam(":besteld", $besteld);
                $stmt->bindParam(":pallet_aantal", $pallet_antl);
                $stmt->bindParam(":artikel_groep", $artikel_groep);
                $stmt->bindParam(":extra", $extra_info);
                $stmt->execute();
                $stmt2 = $db->prepare("INSERT INTO `prijzen`(`artikelnr`, `prijs_soort`, `prijs1`, `prijs2`, `prijs3`, `prijs4`, `prijs5`) VALUES (:artikelnmr, :soort, :prijs1, :prijs2, :prijs3, :prijs4, :prijs5)");
                $type = $data2[0];
                $artikel = $artikelnmr;
                $prijs1 = $data2[6];
                $prijs2 = $data2[7];
                $prijs3 = $data2[8];
                $prijs4 = $data2[9];
                $prijs5 = $data2[10];
                $stmt2->bindParam("artikelnmr", $artikel);
                $stmt2->bindParam(":soort", $type);
                $stmt2->bindParam(":prijs1", $prijs1);
                $stmt2->bindParam(":prijs2", $prijs2);
                $stmt2->bindParam(":prijs3", $prijs3);
                $stmt2->bindParam(":prijs4", $prijs4);
                $stmt2->bindParam(":prijs5", $prijs5);
                $stmt2->execute();
            }
            fclose($handle);
            fclose($handle2);

    }
    importdb();
?>

And when I run the code it returns this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'prijs_soort' cannot be null' in /home/ubuntu/workspace/import.php:65 Stack trace: #0 /home/ubuntu/workspace/import.php(65): PDOStatement->execute() #1 /home/ubuntu/workspace/import.php(71): importdb() #2 {main} thrown in /home/ubuntu/workspace/import.php on line 65

i think you should do a loop for each csv file alone, not both together. Now if one file is finished but not the other, it will still continue doing the loop but one of the two $data is null... — kscherrer
– kscherrer, Commented Dec 6, 2016 at 8:09
@Cashbee i was doing that in the past but I need to get a variable from the other while loop and I couldnt get that one — Rik Nijdeken
– Rik Nijdeken, Commented Dec 6, 2016 at 8:10
because you have a foreign key dependency within your db tables you need to ensure the required value is populated before trying the second insert. Also, no need to include the db_config.php in the loop - once outside the loop only! — Professor Abronsius
– Professor Abronsius, Commented Dec 6, 2016 at 8:10
@RamRaider Yes I know but the collumn 'prijs_soort' is always inserted even if it says that it isnt. $stmt2->bindParam("artikelnmr", $artikel); This one doesnt insert — Rik Nijdeken
– Rik Nijdeken, Commented Dec 6, 2016 at 8:12
in the first sql statement i see that the db column is called 'artikelnr'. Is that correct, or is it artikelnmr? — kscherrer
– kscherrer, Commented Dec 6, 2016 at 8:14

kscherrer · Accepted Answer · 2016-12-06 08:50:56Z

1

replace '||' with '&&' in the while condition.

This error occurs because one of the files is finished but the other isnt but the condition still says true since one of the 'or-conditions' is true. For the code to work you need both conditions to be true.

answered Dec 6, 2016 at 8:50

kscherrer

5,7142 gold badges25 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Professor Abronsius · Accepted Answer · 2016-12-06 08:35:42Z

This may, or may not, be of use to you but when dealing with PDO statements I find the following function useful to aid debugging.

function debugpdo( $sql, $sqlparams ){
    $a=$b=array();
    foreach( $sqlparams as $key => $value ){
        $a[]="set @".str_replace(':','',$key)."='".$value."';";
        $b[]=str_replace(':','@',$key );
    }
    echo '<pre>';
    echo implode( PHP_EOL, $a ) . str_repeat( PHP_EOL, 1 );
    echo str_replace( array_keys( $sqlparams ), $b, $sql );
    echo '</pre>';
}



$sql='insert into `prijzen`( `artikelnr`, `prijs_soort`, `prijs1`, `prijs2`, `prijs3`, `prijs4`, `prijs5` ) values ( :artikelnmr, :soort, :prijs1, :prijs2, :prijs3, :prijs4, :prijs5 )';
$sqlparams=array(
    ":artikelnmr"   =>  $artikel,
    ":soort"        =>  $type,
    ":prijs1"       =>  $prijs1,
    ":prijs2"       =>  $prijs2,
    ":prijs3"       =>  $prijs3,
    ":prijs4"       =>  $prijs4,
    ":prijs5"       =>  $prijs5
);
/* copy the output and run in gui app */
debugpdo( $sql, $sqlparams );


$sql="insert into `producten`(`id`, `artikelnr`, `barcode`, `omschrijving_nl`, `omschrijving_exp`, `bruto_prijs`, `bruto_aant`, `staffel_prijs`, `staffel_aantal`, `aktie_prijs`, `aktie_aantal`, `voorraad`, `leverdatum`, `besteld`, `pallet_aantal`, `artikel_groep`, `extra`)
values ('', :artikelnmr,:barcode,:omschrijving_nl,:omschrijving_exp,:bruto_prijs,:bruto_aantal,:staffel_prijs,:staffel_aantal,:aktie_prijs,:aktie_aantal,:voorraad,:leverdatum,:besteld,:pallet_aantal,:artikel_groep,:extra)";
$sqlparams=array(
    ':artikelnmr'       => $artikelnmr,
    ':barcode'          => $barcode,
    ':omschrijving_nl'  => $omschrijving_nl,
    ':omschrijving_exp' => $omschrijving_exp,
    ':bruto_prijs'      => $bruto_prs,
    ':bruto_aantal'     => $bruto_antl,
    ':staffel_aantal'   => $staffel_antl,
    ':staffel_prijs'    => $staffel_prs,
    ':aktie_aantal'     => $aktie_aantal,
    ':aktie_prijs'      => $aktie_prs,
    ':voorraad'         => $voorraad,
    ':leverdatum'       => $leverdatum,
    ':besteld'          => $besteld,
    ':pallet_aantal'    => $pallet_antl,
    ':artikel_groep'    => $artikel_groep,
    ':extra'            => $extra_info
);
debugpdo( $sql, $sqlparams );

I then try running the sql statements, with their associated variables, directly in the mysql client ( in my case usually Heidi )

I realise that you do not have your sql parameters in array form but perhaps this might aid debugging.

Collectives™ on Stack Overflow

CSV while loop not looping

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related