CSV upload, once uploaded to a temp folder overwrite the existing database

[eluser]the_unforgiven[/eluser]
YOU ABSOLUTE LEGEND!!!!

Just so i know what was missing to make this work, all works super duper now!

[eluser]TheFuzzy0ne[/eluser]
Hooray! I need a vacation... xD

Apologies about all the arsing around. It just goes to show how something simple, but not necessarily obvious can really throw a spanner in the works. It's taken us almost a week to figure this out... O.O

I reluctantly had to add an extra foreach loop which is executed for each row, and escapes each field individually, as opposed to before where I tried to do it on a per-row basis.

[eluser]TheFuzzy0ne[/eluser]
Crap, I hit Quote instead of Edit... Please ignore this post.

[eluser]the_unforgiven[/eluser]
Really appreciate your help. thank you very much! Yes usually the case, but got there in the end.

[eluser]the_unforgiven[/eluser]
Oh right i get ya, see what you've done now, again thanks dude!

[eluser]the_unforgiven[/eluser]
This is not working as we discussed previously, for some reason and I have done is create some $vars for the uploaded file.

Code looks like so:

Code:

public function do_upload()
{
  $csv_path = realpath(APPPATH . '/../assets/uploads/CSV/');
  $config['upload_path']   = $csv_path;
  $config['allowed_types'] = '*'; // All types of files allowed
  $config['overwrite']     = true; // Overwrites the existing file
    
  $this->upload->initialize($config);
  $this->load->library('upload', $config);

  if ( ! $this->upload->do_upload('userfile'))
  {
   $error = array('error' => $this->upload->display_errors());

   $this->layout->buffer('content', 'program/upload', $error);
   $this->layout->render();
  }
  else
  {
   //$csv_upload = array('upload_data' => $this->upload->data());

   $image_data = $this->upload->data();
      $fname = $image_data['file_name'];
      $fpath = $image_data['file_path'].$fname;
      $fh = fopen($fpath, "r");


         if (!empty($fh)) {

          $insert_str = 'INSERT INTO wc_program (`JobRef`, `Area`, `Parish`, `AbbrWorkType`, `WorkType`, `Timing`, `TrafficManagement`, `Location`, `Duration`, `Start`, `Finish`)
          VALUES '."\n";

             // Create each set of values.
             while (($csv_row = fgetcsv($fh, 2000, ',')) !== false) {

                 foreach ($csv_row as &$row) {
                     $row = strtr($row, array("'" => "\'", '"' => '\"'));
                 }

                 $insert_str .= '("'
                     // Implode the array and fix pesky apostrophes.
                     .implode('","', $csv_row)
                     .'"),'."\n";
             }

             // Remove the trailing comma.
             $insert_str = rtrim($insert_str, ",\n");

             // Insert all of the values at once.
             $this->db->set($insert_str);
             var_dump($csv_row);
  
             //$this->layout->set('title','Uploaded File Successfully');
             //$this->layout->buffer('content', 'program/success');
    //$this->layout->render();
  
        
         }
        
  
  
  }
}

When doing a var_dump($csv_row) i get: bool(false)
var_dump($fh) shows: resource(89) of type (stream)
var_dump($insert_str) shows all 1700 records from the csv file (obviously too big to post on here)

So i'm guessing the while statement or the whole from IF statement is wrong somewhere. Really really would appreciate some help on this, I need to get it working by tomorrow (monday)

Thanks.

[eluser]the_unforgiven[/eluser]
Also check $this->db->last_query(); and all i get is the sessions

string(187) "SELECT * FROM (`sessions`) WHERE `session_id` = '7b1b4a96ae3c241eb9be4926624fb85a' AND `user_agent` = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0'"

So something not right and not inserting to DB

[eluser]TheFuzzy0ne[/eluser]
You're using var_dump after the loop has ended, which isn't very helpful. Of course it will be FALSE when the loop ends! That's the loop condition (loop while not FALSE).

I'm not sure why you're using $this->db->set(). I'm pretty sure I didn't in my code. You need to use $this->db->query();

[eluser]the_unforgiven[/eluser]
If i use $this->db->query(); it throws back this:

Code:

A Database Error Occurred

Error Number: 1136

Column count doesn't match value count at row 1

INSERT INTO wc_program (`JobRef`, `Area`, `Parish`, `AbbrWorkType`, `WorkType`, `Timing`, `TrafficManagement`, `Location`, `Duration`, `Start`, `Finish`) VALUES ("63830","Various","Various","SRTS","Safer Routes to Schools Schemes","Days","No Positive Traffic Management","Schemes Electrical Checks","8 days","09/06/2041","0000-00-00 63830","Various","Various","SRTS","Safer Routes to Schools Schemes","Days","No Positive Traffic Management","Schemes Electrical Checks","8 days","09/06/2041","0000-00-00 63230","Rural","Bramber","Micro","Micro-Asphalt Surfacing","TBC","Road Closure","Maudlyn Parkway","1 day","0000-00-00","0000-00-00 63230","Rural","Bramber","Micro","Micro-Asphalt Surfacing","TBC","Road Closure","Maudlyn Parkway","1 day","0000-00-00","0000-00-00 63231","Rural","Bramber","Micro","Micro-Asphalt Surfacing","TBC","Road ........ ......... ........ dots represent more lines theres 1700 records.

[eluser]TheFuzzy0ne[/eluser]
What have you done? (ಠ_ಠ)

I've already written a working script, and you've gone and broken it. Please see my working script, try to understand it, and use it to make your script work.

For some reason, you don't seem to be appending the closing bracket and comma for each row of data.