PHP / MySQL - Undefined variable: sql when uploading a file

Question

I'm currently working on a sample system that can upload csv files to my MySQL database.

No data is being inserted in my database anymore.

My code used to work before, after a few uploads of data this started happening. Every time I upload my csv, it gives me an error of:

Notice: Undefined variable: SQL in C:\xampp\htdocs\intern\orders.php on line 43

Upload source code:

$con = mysqli_connect('localhost','root','') or die (mysql_error());

mysqli_select_db($con, 'test');

$mimes = array('application/vnd.ms-excel','text/plain','text/csv','text/tsv');

if (isset($_POST['submit'])) {
    $file = $_FILES['file']['tmp_name'];
    $handle = fopen($file,"r");
    $num = 0;

    if (in_array($_FILES['file']['type'],$mimes)) {
        while (($fileop = fgetcsv($handle,2000,",")) !== false) {
            if ($num == 0) {
                $num++;
            } else {
                $KeyAccount = $fileop[0];
                $BatchNumber= $fileop[1];
                $Product = $fileop[2];
                $Quantity = $fileop[3];
                $PO = $fileop[4];
                $DateRequested = $fileop[5];
                $DateDelivered = $fileop[6];
                $Status = $fileop[7];
                $Serial = $fileop[8];
                $Voucher = $fileop[9];
                $DateExpiry = $fileop[10];

                $sql = mysqli_query($con, "INSERT INTO orders (KeyAccount,BatchNumber,Product,Quantity,PO,DateRequested,DateDelivered,Status,Serial,Voucher,DateExpiry) VALUES ('$KeyAccount','$BatchNumber','$Product','$Quantity','$PO','$DateRequested','$DateDelivered','$Status','$Serial','$Voucher','$DateExpiry')");
            }
        }
    } else {
        echo '<script language="javascript">';
        echo 'alert("INVALID FILE FORMAT.")';
        echo '</script>';

        die(header("refresh:0;"));
    }

    if ($sql) {
        echo '<script language="javascript">';
        echo 'alert("Successfully Inserted.")';
        echo '</script>';
    } else {
        echo "error";
    }
}

You should never, ever create a SQL query by building it as a string, as that creates the pattern for SQL Injection security risks. You should use parametrised queries. — Dragonthoughts, Jul 20 '18 at 07:40
I doubt that your `IF ELSE` condition where you check `$num` variable is culprit. Because when `$num`won't be `0`, it will go to `else` part and the query will be executed. Otherwise not. Check around that. — Himanshu Upadhyay, Jul 20 '18 at 07:56

score 1 · Answer 1 · edited Jul 20 '18 at 08:41

1

just define this $sql variable before if(isset($_POST['submit'])) this condition like

$sql='';

if(isset($_POST['submit']))

edited Jul 20 '18 at 08:41

Nigel Ren

56,122
11
43
55

answered Jul 20 '18 at 07:29

ashish kumar

172
7

Thanks the error message is gone! But somehow it still wont import any data into my phpmyadmin database. – John Dale Jul 20 '18 at 07:33
It is a MySQL database. PHPmyadmin is a tool to see or view the database. – Dragonthoughts Jul 20 '18 at 07:42
can you please explain in detail ? – ashish kumar Jul 20 '18 at 08:30

score 0 · Answer 2 · answered Jul 20 '18 at 07:31

If no file is uploaded, it will not go inside IF condition and $sql variable will not be declared. You can make the following changes and fix the issue:

Move entire following code inside first if condition which checks for file:

if(in_array($_FILES['file']['type'],$mimes)){
    while(($fileop = fgetcsv($handle,2000,",")) !==false){
        if($num == 0){
            $num++;
        } 
        else{
            $KeyAccount = $fileop[0];
            $BatchNumber= $fileop[1];
            $Product = $fileop[2];
            $Quantity = $fileop[3];
            $PO = $fileop[4];
            $DateRequested = $fileop[5];
            $DateDelivered = $fileop[6];
            $Status = $fileop[7];
            $Serial = $fileop[8];
            $Voucher = $fileop[9];
            $DateExpiry = $fileop[10];
            $sql = mysqli_query($con, "INSERT INTO orders (KeyAccount,BatchNumber,Product,Quantity,PO,DateRequested,DateDelivered,Status,Serial,Voucher,DateExpiry) VALUES ('$KeyAccount','$BatchNumber','$Product','$Quantity','$PO','$DateRequested','$DateDelivered','$Status','$Serial','$Voucher','$DateExpiry')");
          if($sql){
            echo '<script language="javascript">';
            echo 'alert("Successfully Inserted.")';
            echo '</script>';
         }
         else{
            echo "error";
        }
      }
    }
}

score 0 · Answer 3 · answered Jul 20 '18 at 08:04

You should use mysqli_effected_rows to verify how many records inserted. About not inserting, You need to check whether the file is read properly Wat insert statement it makes plus any error message is thrown.

if(in_array($_FILES['file']['type'],$mimes)){
      $inteffectedRows  = 0;
        while(($fileop = fgetcsv($handle,2000,",")) !==false) {
                $KeyAccount = $fileop[0];
                $BatchNumber= $fileop[1];
                $Product = $fileop[2];
                $Quantity = $fileop[3];
                $PO = $fileop[4];
                $DateRequested = $fileop[5];
                $DateDelivered = $fileop[6];
                $Status = $fileop[7];
                $Serial = $fileop[8];
                $Voucher = $fileop[9];
                $DateExpiry = $fileop[10];
                $sql = mysqli_query($con, "INSERT INTO orders (KeyAccount,BatchNumber,Product,Quantity,PO,DateRequested,DateDelivered,Status,Serial,Voucher,DateExpiry) VALUES ('$KeyAccount','$BatchNumber','$Product','$Quantity','$PO','$DateRequested','$DateDelivered','$Status','$Serial','$Voucher','$DateExpiry')");
                $inteffectedRows += mysqli_effected_rows($con);

        }

        if( 0 < $inteffectedRows ) {
            echo '<script language="javascript">';
            echo 'alert("Successfully Inserted.")';
            echo '</script>';
        }
        else{
            echo "error";
        }

    }
    else {
        echo '<script language="javascript">';
        echo 'alert("INVALID FILE FORMAT.")';
        echo '</script>';
        die(header( "refresh:0;" ));

    }

PHP / MySQL - Undefined variable: sql when uploading a file

3 Answers3