Insert multiple rows into a database table from a CSV file using Codeigniter

Question

I'm trying to get the data in the array that came from another function(that function is extracting the data in the csv file) and when i tried calling the two fields from that array it shows an error that it is unidentified variables.

The $this->csv_process(); as shown on the function action() is the function that extracts the data from the csv file and stores it in an array which is successful since I tried checking it on var_dump();

I also named the two fields as $name and $email as shown below:

Function CSV_process()

public function csv_process()
    {
        /* variables for openning the csv file */                        

            if (!in_array($extension, $allowed_ext)) {
                    $this->session->set_flashdata("message", "Sorry, CSV file only.");            
            } else {
                if ($filesize > 0) {
                $file         = fopen($filename, "r");
                $toWrite     = array();        
                $error         = false;
                $col_size     = 2;
                $skip         = 0;                                
                    while ($data = fgetcsv($file, 10000, ","))
                    {
                        $skip++;
                        if ($skip == 1) {
                            continue;
                        }
                        $numofcol = count($data);
                        if ($numofcol != $col_size ) {
                            $this->session->set_flashdata("message", "Column count exceeded or missing.");
                        } else {
                            $name1         = $data[0];
                            $name         = str_replace("'", "''", $name1);
                            $email1     = $data[1];
                            $email         = str_replace("'", "''", $email1);
                            $toWrite[]    = [
                                        'name'  => $name,
                                        'email' => $email
                            ];    
                        }                        
                    }
                }
            }

            return $toWrite;
    }

Function Action()

function action(){
$toWrite[] = $this->csv_process();
    foreach ($toWrite as $arr) {
        list($name, $email) = $arr;
        //die(var_dump($arr));
        $query = $this->db->query("SELECT * FROM import WHERE name ='$name'  AND email = '$email'");
        if ($query->num_rows() >= 1) {

        } else {
            if ($name == "" OR $email == "") {
            } else {
                if ((filter_var($email, FILTER_VALIDATE_EMAIL)) == FALSE ) {
                } else {
                    $this->db->query("INSERT INTO import(name, email, created_date) VALUES('".$name."', '".$email."', '".date("Y-m-d h-i-s")."')");
                    $this->session->set_flashdata('message', 'SUCCESS YEAY');
                    redirect('Clean_csv/index');
                }
            }
        }
        $query->free_result();
    }
 }

Listing arrays doesn't seem to work for here, anyone knows how to extract the data array from $arr?

Answer 1

You don't need to extract the values. You can use each $arr in a bound query. It simplifies the syntax for the select query.

For inserting use CodeIgniter's insert() method. Again, the $arr can be used directly by adding the date to it before the insert is attempted.

I think this will work.

function action()
    {
        $toWrite[] = $this->csv_process();
        foreach($toWrite as $arr)
        {
            $query = $this->db->query("SELECT * FROM import WHERE name=?  AND email=?", $arr);
            if($query->num_rows() >= 1)
            {}
            else
            {
                if($arr['name'] == "" OR $arr['email'] == "")
                {}
                else
                {
                    if((filter_var($email, FILTER_VALIDATE_EMAIL)) == FALSE)
                    {}
                    else
                    {
                        $arr['created_date'] = date("Y-m-d h-i-s");
                        $this->db->insert("import", $arr);
                        $this->session->set_flashdata('message', 'SUCCESS YEAY');

                        //??? redirect('Clean_csv/index');  
                        //Are you sure, you may still have more $arr in $toWrite to process - right?
                    }
                }
            }
            $query->free_result();
        }
    }

You need to know what a terrible idea it is to repeatedly run database queries inside a loop. Even though you use free_result() it could be a massive drain on server resources. If your csv file has several thousand items you are severely stressing the database and the server.