CODEWITHSUNDEEP
phpsqlcsvmysqli
noel293
noel293

Reputation: 504

CSV values with single quotation get ignored

I am trying to insert a CSV file to a database through a website. The script works fine but it will not insert any values into the database that contain single quotes (for example "May's Flowers"). I have tried the real_escape_string function and the striplashes one but it didn't help. I even tried the casting the variable as a string. Any tips?

Part of the code:

if($_FILES["file"]["size"] > 0)
{
    $file = fopen($filename, "r");
    //skip first line
    fgetcsv($file); // first line wont show but neither the 3r
    while (($getData = fgetcsv($file, 1000)) !== FALSE)
    {
        $sql = "INSERT into accounts (accountnumber, accountname, bookccy, date) 
                values ('$getData[0]','$getData[1]','$getData[2]','$getData[3]')";
        $result = mysqli_query($conn, $sql);

Upvotes: 1

Views: 482

Answers (2)

noel293
noel293

Reputation: 504

well i inserted these two (one is commented out now but both work) and they did the job!

//$name = addcslashes($getData[1], "'");
  $name = mysqli_real_escape_string($conn,$getData[1]);

Upvotes: 0

kojow7
kojow7

Reputation: 11404

Looping through a file like this is not as efficient as using LOAD DATA INFILE. Try something like this instead:

<?php

define("DBNAME", "databasename");
define("DBUSER", "username");
define("DBPWD", "aoeuaoeuauea");

$csvfile = "myfile.csv";


if(!file_exists($csvfile)) {
    die("Error: File not found!");
}

try {
    $dbh = new PDO("mysql:host=localhost;dbname=". DBNAME, DBUSER, DBPWD, array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));

    $sql = <<<SQL
        LOAD DATA INFILE '$csvfile'
        INTO TABLE accounts
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        (accountnumber, accountname, bookccy, date)
SQL;

    $dbh->exec($sql);

} catch (PDOException $e) {
    die("An error has occured ". $e->getMessage());
}

Depending on how your CSV file is coded, you may have to change the FIELDS TERMINATED BY and LINES TERMINATED BY sections.

I also used HEREDOC syntax to make it a bit neater. If you are not familiar with HEREDOC syntax it is important that the SQL; line has no space before or after it. The <<<SQL line should not have any space after it either.

Upvotes: 1

Related Questions