Jump to content

prevent empty data from inserting into my database.


sashavalentina

Recommended Posts

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. 

image.thumb.png.f9989daa99969194c60391637dd73c8e.png

 

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

Edited by sashavalentina
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.