CSV import validation

I am attempting to validate file fields in csv to check if they are empty or not. If a single field in the uploaded csv file is empty I want to kill the script and not insert anything into the database. However what I have here will insert only one record from a valid CSV file meaning no blank fields but only one record inserted. Any help is much appreciated.

<?php include 'connection.php';?>

<?php
$empty_value_found = false;
$file = $_FILES['file']['tmp_name'];
$handle = fopen ($file,"r");

while(($fileop = fgetcsv($handle,1000,",")) !==false){

$first = trim($fileop[0]);
$last = trim($fileop[1]);
$birthday = trim($fileop[2]);
$age = trim($fileop[3]);
$address = trim($fileop[4]);


if (empty($first) 
|| empty($last) 
|| empty($birthday) 
|| empty($age) 
|| empty($address) 
) {
$empty_value_found = true;
echo "empty field please check";
break; // stop our while-loop
}
}

if (!$empty_value_found) {

$sql = mysqli_query($conn,"INSERT INTO `mytable` (first, last, birthday, age, address) VALUES ('$first','$last','$birthday','$age','$address')");
$getdata =  "SELECT * FROM mytable";
$results = mysqli_query($conn,$getdata);

 if(mysqli_num_rows($results) >0){
echo "<table><tr><th>First</th><th>Last</th><th>Birthday</th><th>Age</th> <th>Address</th></tr>";
}
while($row = mysqli_fetch_assoc($results)){
echo "<tr><td>" . $row["first"]. "</td><td>" . $row["last"]. "</td><td>" . $row["birthday"]. "</td><td>" . $row["age"]. "</td><td>" . $row["address"]. "</td> </tr>";
}
}
echo "</table>";
}

If you indent your code (maybe you did and it just got lost when you copied it here) you’ll notice that you close your first while loop right after the break (you close the IF and the WHILE). So you only ever insert one row in the database.

Do you mean that when there is an empty field anywhere in the csv file, you don’t want to insert anything in the database, not even the rows that are correct?
If so, then you’ll have to change the logic of your script. Instead of putting an INSERT statement inside the loop, you should create your insert statement in the loop, and then execute it once the loop has elaborated the entire csv file without encountering any empty fields.

To extend on that, maybe you wanted to check the file first and then insert the data, if the data was ok. If so, then you need to loop through the data again to insert it.

Scott

If handling ‘small’ (read: memory-loadable) CSV files: [Hint: You can limit file upload sizes if this is your intent]
[Also using PDO here instead of mysqli_, because prepared statements]

<?php
include 'connection.php';
$data = file_get_contents($file);
$spurt = explode(",",$data);
$count = count($spurt);
if($count % 5 != 0) { //Abort; invalid number of fields. }
if(count(array_filter($spurt,'strlen'))) != $count) { //Abort. 1 or more empty fields. }
$stmt = $pdo->prepare("INSERT INTO `mytable` (first, last, birthday, age, address) VALUES (?,?,?,?,?)");
while($count > 0) {
   $stmt->execute(array_slice($spurt,0,5));
   $spurt = array_slice($spurt,5);
   $count -= 5;
}           
?>

EDIT: No, i did that wrong. Splice doesnt return the removed elements.

Alternatively, you can avoid changing the logic of your script and instead implement Transaction logic around it. In PDO, this is accomplished with [FPHP]PDO.beginTransaction[/FPHP] and [FPHP]PDO.commit[/FPHP] or [FPHP]PDO.rollBack[/FPHP]. In mysqli, it would be [FPHP]mysqli_autocommit[/FPHP], [FPHP]mysqli_commit[/FPHP] and [FPHP]mysqli_rollback[/FPHP].

Actually, it does. And the documentation on array_splice is horrible.

   $stmt->execute(array_slice($spurt,0,5));
   $spurt = array_slice($spurt,5);

=>

   $stmt->execute(array_splice($spurt,0,5));

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.