prevent empty data from inserting into my database.

[sashavalentina]

How do I prevent empty data from inserting into my database. I am implementing the feature of uploading the data into the database using CSV file. But i am facing a problem here, where those empty rows of data in my database will be also insert into my database. How can i prevent that from happen? 

these are my codes

<?php 
include 'conn.php';
$preparing = 2; 
$row=1;

  
if(isset($_POST["import"])){
    $fileName = $_FILES["file"]["tmp_name"];

    if($_FILES["file"]["size"]>0){
        $file=fopen($fileName,"r");


        while(($column = fgetcsv($file,1000,","))!== FALSE){
            if($row == 1){ $row++; continue; }
            $num = count($column);

            $sqlInsert="INSERT INTO `ordered_items` (`order_id`,`user_id`,`seller_id`,
            `product_id`,`quantity`,`quantity_unit`,`product_buy_price`,`discount_price`,`other_charges`,`purchase_price`,
            `delivery_date`,`order_datetime`,`order_status`,`item_deliver_method`,`payment_term`) VALUES
            ('" .$column[0] . "','" .$column[1] . "','" .$column[2] . "','" .$column[3] . "',
            '" .$column[4] . "','" .$column[5] . "','" .$column[6] . "','" .$column[7] . "',
            '" .$column[8] . "','" .$column[9] . "' ,'" .$column[10] . "' ,'" .$column[11] . "' ,'" .$preparing . "','" .$column[12] . "','" .$column[13] . "')";
        
            $result = mysqli_query($conn, $sqlInsert);
            $sql = "SELECT * FROM invoice_price WHERE order_id = ('".$column[0]."') ";
            $query = $conn->query($sql);
            if (!mysqli_num_rows($query)) {
              $invoicesql = "INSERT INTO `invoice_price`(`order_id`) VALUES ('".$column[0]."')";
              $invoicequery = $conn->query($invoicesql);
            }
            else{
              $sql = "UPDATE `invoice_price` SET `order_id`=('".$column[0]."') WHERE order_id = ('".$column[0]."') ";
            }
            if(!empty($result)){
                echo '<script>alert("csv file sucessfully uploaded")
                window.location.href = "orders.php"</script>';
             
            }else{
                echo '<script>alert("problem importing csv") window.location.href = "upload-orders.php"</script>';
               
            }
    }
  }
}
?>

This is what i mean by empty data inserted into the database. Thanks. 

 

I do not want any data insertion when the order_id = 0

Edited August 13, 2021 by sashavalentina

[Phi11W]

It's called Data Validation.  

Just because the data comes from a file doesn't mean that your application should blindly "trust" it. 
The data is still coming from an untrustworthy source (i.e. anything that doesn't run on your own servers). 

Read each line from the file, validate the data, store only what "fits" and reject what doesn't (or rollback your Transaction to throw the whole lot away at the end of a "failed" upload run; YMMV). 

You are wide open to SQL Injection attacks.  Read up about Parameterised Queries. 
Obligatory XKCD Reference - Little Bobby Tables. 

Regards, 
   Phill  W.

 

[Barand]

1 hour ago, sashavalentina said:

I do not want any data insertion when the order_id = 0

Then don't insert them.

[sashavalentina]

7 minutes ago, Barand said:

Then don't insert them.

But it will automatically create a new row in the database where the order_id will be 0. How do i avoid that?

 

[Barand]

if () is the construct that springs to mind.

[Phi11W]

3 hours ago, sashavalentina said:

But it will automatically create a new row in the database

There is no "Magic" here and, in the World of Programming, very little happens "automatically". 

Your code reads every row in the file, [poorly] constructs a SQL insert statement using the values in that row, and then executes that insert statement, thereby adding the row into the database. 

You need to change your code and add tests into it that will reject any row that contains data items that do not conform to your Business Rules (e.g. "Order Id cannot be zero"). 

How you report these failures back to the User is for you to decide. 

Regards, 
   Phill  W.