CODEWITHSUNDEEP
phpmysqlsqlcsvphpmyadmin
Peter
Peter

Reputation: 15

how to escape double quote characters in mysqli_query() in PHP?

I am trying to update the mysql database using a CSV file.

I have this Query working in phpmyadmin 

LOAD DATA INFILE 'C:/wamp/www/website/test_mysql_import.csv' INTO TABLE `csv_preset` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n'

but when i try to use it within the mysqli_query function it does not update the database.

here's the full code block:

<?php require "db_connect.php"; ?>    
    <?php
    $csv_file = "C:/wamp/www/website/test_mysql_import.csv";

    $sql_dump ="TRUNCATE TABLE csv_preset";
    $sql_update = "LOAD DATA INFILE '$csv_file' INTO TABLE `csv_preset` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n'");
    mysqli_query($db_conx, $sql_dump);
    mysqli_query($db_conx, $sql_update);
?>

Upvotes: 0

Views: 784

Answers (2)

Macbernie
Macbernie

Reputation: 1323

<?php require "db_connect.php"; ?>    
    <?php
    $csv_file = "C:/wamp/www/website/test_mysql_import.csv";

    $sql_dump ="TRUNCATE TABLE csv_preset";
    $sql_update = "LOAD DATA INFILE '".$csv_file."' INTO TABLE `csv_preset` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\n'");
    mysqli_query($db_conx, $sql_dump);
    mysqli_query($db_conx, $sql_update);
?>

Upvotes: 1

Akis
Akis

Reputation: 178

Try this code instead:

        $sql_update = "LOAD DATA INFILE $csv_file INTO TABLE `csv_preset` FIELDS TERMINATED BY ',' ENCLOSED BY &quot; ESCAPED BY &quot; LINES TERMINATED BY \\n";

There is no need for single quotes around your variable since it is already in double. Double quotes you can escape them with htmlcode and new line with the "\".

Upvotes: 0

Related Questions