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

[eluser]the_unforgiven[/eluser]
Following on from what was said, i'm now having trouble saving it to the db.

by this line from the code above:

Code:

$sql = "INSERT INTO wc_program_temp VALUES ";

Error is:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

INSERT INTO wc_program_temp VALUES ;

So what should I do? Code is exactly the same in the 2nd post by davidMC1982

[eluser]TheFuzzy0ne[/eluser]
The problem is still exactly the same as what I explained [url="http://ellislab.com/forums/viewthread/234195/#1050153"]here[/url].

Debug, debug, debug!

[eluser]the_unforgiven[/eluser]
That's just it i have but cant find what it is hence coming back to the forums for help....

[eluser]the_unforgiven[/eluser]
Any1 else willing to offer some advice/help?

[eluser]the_unforgiven[/eluser]
Ok i figured this out byt going back to basics and debugging using the following code

Code:

$data = file_get_contents("http://localhost/sussex/assets/uploads/wc_program.csv");
  
   //Explode the csv into an array of lines
   $lines = explode(",", $data);
    
   $file_handle = fopen("http://localhost/sussex/assets/uploads/wc_program.csv", "r");

  
   while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE)
   {

          $line_import_query="INSERT INTO wc_program_temp (JobRef, Area, Parish, AbbrWorkType, WorkType, Timing, TrafficManagement, Location, Duration, Start, Finish) values(
           '$line_of_data[0]',
           '$line_of_data[1]',
           '$line_of_data[2]',
           '$line_of_data[3]',
           '$line_of_data[4]',
           '$line_of_data[5]',
           '$line_of_data[6]',
           '$line_of_data[7]',
           '$line_of_data[8]',
           '$line_of_data[9]',
           '$line_of_data[10]')";

        //mysql_query($line_import_query) or die(mysql_error());
         if ($this->db->query($line_import_query))
     {
       $this->load->view('program/success');
     }
     else {
      echo "Failed";
     }

       }

But then i get this error:

Code:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Church', '1 day', '02/05/2041', '0000-00-00')' at line 9

INSERT INTO wc_program_temp (JobRef, Area, Parish, AbbrWorkType, WorkType, Timing, TrafficManagement, Location, Duration, Start, Finish) values( '903475', 'Rural', 'West Lavington', 'CWY SD', 'Carriageway Surface Dressing', 'Days', 'No Positive Traffic Management', 'Chichester Road - Cocking Causeway to St. Mary's Church', '1 day', '02/05/2041', '0000-00-00')

Which i guess is caused by having ' in words such as Mary's so how can i from the code above remove these so it stops the errors and shows the success page?

[eluser]TheFuzzy0ne[/eluser]
Just replace a single apostrophe with two apostrophes.

Code:

// So...
$sql = '...St. Mary\'s Church';

$sql = strtr($sql, array('\'' => '\'\''));

// Becomes $sql = '...St. Mary\'\'s Church';

[eluser]the_unforgiven[/eluser]
That's not going to work with what i have in http://ellislab.com/forums/viewthread/234195/#1050313 will it has I dont have what was originally posted with the $sql var

[eluser]TheFuzzy0ne[/eluser]
You've lost me...

However you're building your insert array, you just need to loop through the values and replace any single quotes with two single quotes.

You need to decide how you're going to pragmatically insert your data into the database. Once you've figured that out, post back with what you have, and we'll see what we can do.

[eluser]the_unforgiven[/eluser]
Well this is what i have at present and works just fine, except it throws SQL error because there's some apostrophes in the content

Code:

$data = file_get_contents("http://localhost/sussex/assets/uploads/wc_program.csv");
  
   //Explode the csv into an array of lines
   $lines = explode(",", $data);
    
   $file_handle = fopen("http://localhost/sussex/assets/uploads/wc_program.csv", "r");

  
   while (($line_of_data = fgetcsv($file_handle, 1000, ",")) !== FALSE)
   {

          $line_import_query="INSERT INTO wc_program_temp (JobRef, Area, Parish, AbbrWorkType, WorkType, Timing, TrafficManagement, Location, Duration, Start, Finish) values(
           '$line_of_data[0]',
           '$line_of_data[1]',
           '$line_of_data[2]',
           '$line_of_data[3]',
           '$line_of_data[4]',
           '$line_of_data[5]',
           '$line_of_data[6]',
           '$line_of_data[7]',
           '$line_of_data[8]',
           '$line_of_data[9]',
           '$line_of_data[10]')";

        //mysql_query($line_import_query) or die(mysql_error());
         if ($this->db->query($line_import_query))
     {
       $this->load->view('program/success');
     }
     else {
      echo "Failed";
     }

       }

[eluser]TheFuzzy0ne[/eluser]
This is untested, but should work.

I've made a few improvements by removing some the redundant code (the first two lines), and now your insert is made in a single query, rather than hitting the database once per insert.

Code:

// The start of the insert string. We just add to this with our loop.
        $insert_str = 'INSERT INTO wc_program_temp (JobRef, Area, Parish, AbbrWorkType, WorkType, Timing, TrafficManagement, Location, Duration, Start, Finish) VALUES '."\n";

        // Open the file in read only mode and get a handle.
        $fh = fopen("http://localhost/sussex/assets/uploads/wc_program.csv", "r");

        if ($fh) {
            // Create each set of values.
            while (($csv_row = fgetcsv($fh, 1000, ',')) !== 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->query($insert_str);

            $this->load->view('program/success');
        } else {
            echo "Failed";
        }

It could probably do with being tweaked a bit more, but I'll leave that to you.