Upload csv and insert or update the results failing

Question

Can anyone see what I am doing wrong here? Basically what is supposed to happen is you upload the csv, it then pulls the rows out and checks with the database if that row exists, if it does it updates it, if it doesn't it inserts it. If the row is blank it ignores it.

What is actually happening is when I upload a csv I just get 'updated' for each of the 4 test lines whether it is in the database or not.

Also if anyone can suggest a better way of doing this, or trimming down the code please let me know, as I know by coding isn't the greatest by any stretch of the imagination.

if(isset($_GET['uploadfile'])) {
    $file = fopen($_FILES['csvfile']['tmp_name'], 'r+');
    while(! feof($file))
    {
        $line = fgetcsv($file, 0, ',');

        list($productcode, $v9cm, $v1litre, $v2litre, $v3litre, $v5litre, $v7litre) = $line;
        $rowcheck = "SELECT * FROM `stock` WHERE `productcode` = '$productcode'";

if (@mysql_num_rows(mysql_query($rowcheck))!=1) {

    if ($productcode == NULL OR $productcode == ''){}
    else {
        $datecreated = date('Y-m-d');
// Insert Posted Data
mysqli_query($db,"INSERT INTO stock (`datecreated`, `productcode`, `9cm`, `1litre`, `2litre`, `3litre`, `5litre`, `7litre`) VALUES ('$datecreated', '$productcode', '$v9cm', '$v1litre', '$v2litre', '$v3litre', '$v5litre', '$v7litre')") or die ('Unable to execute query. '. mysqli_error());
    echo $productcode.' - Added<br/ >';

    }    
}
else{
    if ($productcode == NULL OR $productcode == ''){}
    else {
$stmt = $db->prepare("UPDATE stock SET 9cm = '$v9cm',1litre = '$v1litre',2litre = '$v2litre',3litre = '$v3litre',5litre = '$v5litre',7litre = '$v7litre' WHERE productcode = '$productcode'");

if ($stmt === FALSE) { echo "an error has occured"; }

$stmt->execute(); 
$stmt->close(); 
echo $productcode.' - Updated<br/ >';
    }
}
}
fclose($file);

}
}

For some reason your query is always returning something. Debug your query, `print_r(mysql_fetch_array(mysql_query($rowcheck)))` — user5542121, Dec 26 '15 at 00:41
Glad you got it working! Remember to tick your answer as the correct one so we know. — Steve, Dec 26 '15 at 22:00

Steve · Answer 1 · 2015-12-26T06:36:46.363

There seemed to be a brace missing which I think would indeed have made it try to update whatever - indented statements to help make it a bit clearer and added mysqli instead of mysql where it appeared to be wrong - assuming everything should be mysqli. Hope that may help.

You will need to add clean-up/sanitizing/escape code but I assume you know that

if(isset($_GET['uploadfile'])){
    $file = fopen($_FILES['csvfile']['tmp_name'], 'r+');
        while(! feof($file)){
        $line = fgetcsv($file, 0, ',');


        list($productcode, $v9cm, $v1litre, $v2litre, $v3litre, $v5litre, $v7litre) = $line;

        $rowcheck = "SELECT * FROM `stock` WHERE `productcode` = '$productcode'";

             if (@mysqli_num_rows(mysqli_query($rowcheck))!=1){

                 if ($productcode == NULL OR $productcode == ''){
                 echo "Product code missing and rowcheck is not 1<br/ >";  // for testing
                 }else{
                 $datecreated = date('Y-m-d');
                 // Insert Posted Data
                 mysqli_query($db,"INSERT INTO stock (`datecreated`, `productcode`, `9cm`, `1litre`, `2litre`, `3litre`, `5litre`, `7litre`) VALUES ('$datecreated', '$productcode', '$v9cm', '$v1litre', '$v2litre', '$v3litre', '$v5litre', '$v7litre')") or die ('Unable to execute query. '. mysqli_error());
                 echo $productcode.' - Added<br/ >';
                 }    

             }elseif($productcode == NULL OR $productcode == ''){
             echo "Product code missing<br/ >"; // for testing
             }else{
             $stmt = $db->prepare("UPDATE stock SET 9cm = '$v9cm',1litre = '$v1litre',2litre = '$v2litre',3litre = '$v3litre',5litre = '$v5litre',7litre = '$v7litre' WHERE productcode = '$productcode'");

                  if ($stmt === FALSE){
                  echo "an error has occured<br/ >"; 
                  }else{
                  $stmt->execute(); 
                  $stmt->close(); 
                  echo $productcode.' - Updated<br/ >';
                  }
             }
       }
 }

You might also like to look at the security implications of using uploaded files directly by their temp name http://www.acunetix.com/websitesecurity/php-security-4/ among many others.

Thanks, I am just getting 'added' each time now, even if the entry still exists, so I get many in the database with the same productcode now, but this shouldnt happen as it is supposed to check to see if that product code exists. — Iain Simpson, Dec 26 '15 at 11:28
By the way, this part of the code was just supposed to check for blank lines in the csv file, as im not sure how to correctly handle that, so all it does is check to see if there is a product code on the line. $productcode == NULL OR $productcode == ''){ — Iain Simpson, Dec 26 '15 at 11:34
Im not sure if its actually doing it line by line, as it seems if the first result is true or false, then all of the results are true or false. — Iain Simpson, Dec 26 '15 at 12:48

score 0 · Accepted Answer · answered Dec 26 '15 at 18:00

This is what worked in the end :

    if(isset($_GET['uploadfile'])){
        $file = fopen($_FILES['csvfile']['tmp_name'], 'r+');
            while(! feof($file)){
            $line = fgetcsv($file, 0, ',');


            list($productcode, $v9cm, $v1litre, $v2litre, $v3litre, $v5litre, $v7litre) = $line;


            $query = mysqli_query($db, "SELECT * FROM stock WHERE productcode='".$productcode."'");

    if(mysqli_num_rows($query) > 0){

         $stmt = $db->prepare("UPDATE stock SET 9cm = '$v9cm',1litre = '$v1litre',2litre = '$v2litre',3litre = '$v3litre',5litre = '$v5litre',7litre = '$v7litre'
           WHERE productcode = '$productcode'");

        if ($stmt === FALSE) { echo "an error has occured"; }

        $stmt->execute(); 
        $stmt->close(); 
        echo $productcode.' - Updated<br/ >';

    }else{
        // do something
        if (!mysqli_query($db,$query))
        {

              $datecreated = date('Y-m-d');
        // Insert Posted Data
       mysqli_query($db,"INSERT INTO stock (`image`,`genusid`,`datecreated`, `productcode`, `9cm`, `1litre`, `2litre`, `3litre`, `5litre`, `7litre`) VALUES ('None.gif','5','$datecreated', '$productcode', '$v9cm', '$v1litre', '$v2litre', '$v3litre', '$v5litre', '$v7litre')") or die ('Unable to execute query. '. mysqli_error());
            echo $productcode.' - Added<br/ >';
        }
    }


                    }}}

score 0 · Answer 3 · edited May 23 '17 at 11:53

It may be easier to use mysql's ON DUPLICATE KEY UPDATE clause (http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html).

See hjpotter92's answer here: On Duplicate Key Update same as insert. Your query would need to be modified to something like this:

INSERT INTO table (id,a,b,c,d,e,f,g)
    VALUES (1,2,3,4,5,6,7,8) 
    ON DUPLICATE KEY
        UPDATE a=a, b=b, c=c, d=d, e=e, f=f, g=g;

This should allow you to remove most of the conditional logic and also cut down on the number of queries you run.

Upload csv and insert or update the results failing

3 Answers3