php Import CSV to MySQL where CSV contains HTML Content

Question

My method $csv->getContent(); contains my column names and the 'data' is my individual rows to import to the database. The loop below works flawlessly when importing my various CSVs to the database. However, when I have a column in the CSV that has html content, many rows import fine but there are some that do not and I get the SQLSTATE[HY093]: "Invalid parameter number: number of bound variables does not match number of tokens" error.

From my research, I am seeing that doing things the way I have shown below is supposed to properly escape characters to avoid the need for addslashes but it does not seem to make a difference. The only time this does work is if I addslashes(addslashes($values)); but thats kind of ridiculous and it leaves my content in the database with \\\ before the applicable characters.

Am I missing a step here or am I just stuck. The way I see it, I should be able to "bulletproof" my content to get it into my db each and every time.

$this->db->exec("SET CHARACTER SET utf8");                          
$this->db->beginTransaction();
$content = $csv->getContent();
foreach($content['data'] as $key => $value) {

    $sql  = "INSERT INTO `".$destination_table."` (`";
    $sql .= implode("`, `", $content['columns']);
    $sql .= "`) VALUES (";
    $sql .= implode(", ", array_fill(0, count($content['columns']), "?"));
    $sql .= ")";
    $statement = $this->db->prepare($sql);
    $statement->execute($value);

}
$this->db->commit();

try to look at this thread. http://stackoverflow.com/questions/10170913/invalid-parameter-number-number-of-bound-variables-does-not-match-number-of-tok — Jayson O., Apr 23 '13 at 02:35
and in your loop. its it really `$content['columns']` or should be `$value['columns']`? — Jayson O., Apr 23 '13 at 02:37
$value[columns] doesnt exist. $content is a multidimensional array. Sorry but these responses are not addressing my question. I am very interested in using prepared statements to execute these lines. Thanks — Shawn Abramson, Apr 23 '13 at 02:46

score 0 · Answer 1 · edited May 23 '17 at 11:52

0

I do not prefer your code. What if there are thousands of data and you loop each data then insert to database. There is a mysql query to import csv file to database table.

source: how-to-import-csv-file-to-mysql-table

edited May 23 '17 at 11:52

Community

1
1

answered Apr 23 '13 at 02:27

Jayson O.

485
1
6
20

There are thousands of rows but its still fast. At times I use a Bulk Insert Statement but for this particular issue, I need to do it this way. – Shawn Abramson Apr 23 '13 at 02:28

score 0 · Accepted Answer · answered Apr 23 '13 at 17:39

Ok, I have found an answer to my question. I built a small app for personal use where I can keep the content for auctions I list online. Some of the content is old and contains mal-formed html so when parsing the csv and importing into the database, mal-formed html can really screw things up. However, after more research and reading, the "bindParam" and then "execute" methods of PDO allowed to seemlessly import the content no matter how poorly formed the html is. Here is how I structured my test query and then just kept experimenting with messed up html to the database.

$sql = $objDb->prepare('UPDATE `autolist_html` SET `value` = :html WHERE entity = "'.$entity[1].'"');
$sql->bindValue(':html',$_POST[$entity[1].'_html'],PDO::PARAM_STR);
$sql->execute();

php Import CSV to MySQL where CSV contains HTML Content

2 Answers2