How do I validate CSV Contents before uploading and save it to msql database using php?

Question

I searched everywhere in the internet but I couldn't find a perfect solution for my question, regarding on how to validate the CSV Contents before it uploads and save it to database, I am using msqli for the database and PHP for the language script. For example I have 3 columns in localhost database:

These are my following headers I made both in locahost database and in CSV File (1.)Date (2.)Email (3.)Phone Number.

Before uploading and saving to the localhost database it should meet the restrictions of the contents are the ff: For the Date: It should mm/dd/yy ----> 1/31/2018 or 12/31/2018 For the Email: It should name@domain.com ----> saffron@gmail.com For the Number: It should 12 digits number only and the format is ----> 0906-021-0156

If those restrictions meets perfectly the CSV File will be uploaded and save to the database, if not it will throw an error or pop-up message.

I really don't know how to start the execution of program. I am really new to PHP so please help me with this.

This is the code I worked, and I am stack here...

<?php

$dbHost = 'localhost';
$dbUsername = '';
$dbPassword = 'root';
$dbName = 'dbUpload';

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Unable to connect database: " . $db->connect_error);
}

if(isset($_POST['submit'])){
 
 $row = 1;
 $mycsvfile = array(); //define the main array.
 
 if ($_FILES['csv']['size'] > 0) {
     //get the csv file 
      $file = $_FILES['csv']['tmp_name']; 
      $handle = fopen($file, "r");
      $i = 0;
      
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $num = count($data); 
         $row++;
 
      $mycsvfile[] = $data; //add the row to the main array.
      
      $headerDate = $mycsvfile[0][0];
      $headerEmail = $mycsvfile[0][1];
      $headerPhone =  $mycsvfile[0][2];

      if ($headerDate !== 'Date' || $headerEmail !== 'Email' || $headerPhone !== 'Phone Number') {
        $qstring = '?status=invalid_header';        
        fclose($handle);     
       }
       
       else {
        if ($i > 0) {
           
              $import = "INSERT into upload (techDate, techEmail, techPhone)values('$data[0]','$data[1]','$data[2]')";
              
              $db->query($import);
              $qstring = '?status=succ';
          }
       }
      $i++;        
  } 
  fclose($handle);
  
  

}
else{
 $qstring = '?status=err';
   }
}
header("Location: uploadvalid.php".$qstring);
?>

Answer 1

You can use the function preg_match().

I took the regex (for date and email) from StackOverflow. Links are below this code. I made the regex for your phone format.

---

<?php

$dbHost = 'localhost';
$dbUsername = '';
$dbPassword = 'root';
$dbName = 'dbUpload';

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {
    die("Unable to connect database: " . $db->connect_error);
}

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

    $row = 1;
    $mycsvfile = array(); //define the main array.

    if ($_FILES['csv']['size'] > 0) {
        //get the csv file 
            $file = $_FILES['csv']['tmp_name']; 
            $handle = fopen($file, "r");
            $i = 0;


            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

            $num = count($data);    
            $row++;

            $mycsvfile[] = $data; //add the row to the main array.

            $headerDate = $mycsvfile[0][0];
            $headerEmail = $mycsvfile[0][1];
            $headerPhone =  $mycsvfile[0][2];

            if ($headerDate !== 'Date' || $headerEmail !== 'Email' || $headerPhone !== 'Phone Number') {
                    $qstring = '?status=invalid_header';                    
                    fclose($handle);                
                }

                else {
                    if ($i > 0) {

                        $patternDate = '^(?:(?:31(\/|-|\.)(?:0?[13578]|1[02]))\1|(?:(?:29|30)(\/|-|\.)(?:0?[1,3-9]|1[0-2])\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(\/|-|\.)0?2\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(\/|-|\.)(?:(?:0?[1-9])|(?:1[0-2]))\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$';

                        $patternEmail = '(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])';

                        $patternPhone = '0906\d021\d0156';

                        // Check if the row has the correct format
                        if (preg_match($data[0], $patternDate)
                            && preg_match($data[1], $patternEmail)
                            && preg_match($data[2], $patternPhone)) {

                            // Format is OK, let's insert

                            $import = "INSERT into upload (techDate, techEmail, techPhone)values('$data[0]','$data[1]','$data[2]')";

                            $db->query($import);
                            $qstring = '?status=succ';

                         } else {
                             // The row doesn't have the right format
                             echo "The row $row doesn't have the right format";
                         }
                     }
                }
            $i++;                   
        }   
        fclose($handle);



}
else{
    $qstring = '?status=err';
    }
}
header("Location: uploadvalid.php".$qstring);
?>

---

link to the StackOverflow date regex

link to the StackOverflow email regex