sashavalentina Posted August 13, 2021 Share Posted August 13, 2021 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Phi11W Posted August 13, 2021 Share Posted August 13, 2021 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2021 Share Posted August 13, 2021 1 hour ago, sashavalentina said: I do not want any data insertion when the order_id = 0 Then don't insert them. Quote Link to comment Share on other sites More sharing options...
sashavalentina Posted August 13, 2021 Author Share Posted August 13, 2021 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 13, 2021 Share Posted August 13, 2021 if () is the construct that springs to mind. 1 Quote Link to comment Share on other sites More sharing options...
Phi11W Posted August 13, 2021 Share Posted August 13, 2021 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. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.