2

So I am a little out of my depth here (one again) and need to remove any apostrophes from a CSV dataset upon importing it to my database...

The code I have at the moment reads as so

<?php

include "connection.php"; //Connect to Database

$deleterecords = "TRUNCATE TABLE riders_long"; //empty the table of its current records
mysql_query($deleterecords);

//Upload File
if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
        echo "<h2>Displaying contents:</h2>";
        readfile($_FILES['filename']['tmp_name']);
    }

    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT into riders_long(Onlineentry,SaleID,FirstName,LastName,ClubTeamName,DOB,IsBCMember,MemberNo,Agreetermsandconditions,Agreeemailconfirmation,Agreeemailmarketing,LicenceCategory,Gender,Email,Daytimephone,Address1,Address2,TownCity,Region,Country,Postcode,EmergencyContactName,EmergencyContactPhoneNumber,DatePaid,AmountPaid,RefundAmount,PaymentType,Status) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]','$data[17]','$data[18]','$data[19]','$data[20]','$data[21]','$data[22]','$data[23]','$data[24]','$data[25]','$data[26]','$data[27]')";

        mysql_query($import) or die(mysql_error());
    }

    fclose($handle);

    print "Import done";

    //view upload form
}else {

    print "Upload new csv by browsing to file and clicking on Upload<br />\n";

    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";

    print "File name to import:<br />\n";

    print "<input size='50' type='file' name='filename'><br />\n";

    print "<input type='submit' name='submit' value='Upload'></form>";

}

?>

If I am honest I don't even know where to begin with this one...

I presume I need to use a str_replace() function... Something along the lines of

$remove = array("'");
$filtered = str_replace($remove, "", "Hello World of PHP");

$filtered would then be the strings without 'apostrophes but then I am a little confused about how and where i'd best integrate this

Advice on where to start would be really appreciated

Bhavin
  • 2,070
  • 6
  • 35
  • 54
Henry Aspden
  • 1,863
  • 3
  • 23
  • 45
  • Surely what you really need to do isn't remove the apostrophes, it's to escape them so that they won't break your SQL Insert statement – Mark Baker Sep 09 '16 at 11:55
  • If your server allow it you could import the file directly using something as the answer in http://stackoverflow.com/questions/11077801/import-csv-to-mysql-table/36586798, or you could use mysqli_ or PDO instead of the deprecated mysql_ functions and use bind variables. (The old way used with the type of query you have is to use `mysql_real_escape_string()` around all your fields (`mysql_real_escape_string($data[5])`) – rypskar Sep 09 '16 at 12:03

1 Answers1

1

Use file_get_contents, sth like this:

$data = file_get_contents(...);
$cleanData = str_replace("'","",$data);
Grzegorz B.
  • 101
  • 6