I am populating a mysql database table from data in a comma delimited .csv file by using php
The script automatically runs without any user input and it populates the mysql table with the data previously entered into the .csv file.
Also, the script is set so that it does not "add" the data to previously existing rows in the mysql table but instead erases all the table rows and then inserts all the new information from the .csv file. In other words the mysql table always reflects what is contained in the .csv file.
The form that is used to enter the data is an .html file, but I could also use a .php file to do that. To view the web page that displays the data thet the user enters, they go to another page (a .php page) which pulls the data from the .csv file and displays it on the web page AND also runs the following script which inserts the data into a mysql table. Once again the script works fine unless the user has enteredan apostrophe, double quote or single quote in one of the data entry form fields.
The Problem If the user enters an apostrophe (') or double quotes (") or single quotes (') and other characters when submitting the form data to the .csv file that the php script stops running when it hits those characters.
Where I need Help: At a minimum I need to either keep those characters from being written to the .csv file or have the php INSERT query escape them.
I think preg_replacecan be used but I am not familiar with using it or where it would be located.
Below is the php INSERT script I am using.
Once again, the code runs perfectly accept for this "escape" issue, which then stops the mysql table from updating to match the .csv file. As long as neither a (") or (') are entered into the form, all is perfect.
I have invested about 8 hours trying to resolve this and tried using the following (separately, not combined) but neither worked:
(#1) ABOVE THE INSERT QUERY I PUT
for($x=0; $x < count($data); $x++)
{
$data[$x] = mysql_escape_string($data[$x]);
}
(#2) I PUT
$book_name = str_replace("\'", "\\\\'", $book_name);
$book_name = addslashes($book_name);
$DateTime = str_replace("\'", "\\\\'", $DateTime);
$DateTime = addslashes($DateTime);
ABOVE
foreach ($data as $row){
the PHP INSERT code follows
<?php
// connect to database
$connection = mysql_connect('localhost', 'usr', 'password')
or die('Cannot connect to MySQL');
mysql_select_db('database_name')
or die('Cannot connect to database');
// clear the table down
$sql = "TRUNCATE TABLE books";
// run the first query to clear table
mysql_query($sql) or die(mysql_error());
// open the csv file
$data = file('/path/to/.csv file');
// create an insert statement for each row in the csv file
foreach ($data as $row){
$cols = explode(",", $row); // put each piece of data seperated by a comma, into an array called $cols
$sql = "INSERT INTO books (DateTime, fname, lname, rate, useremail, book_category, book_name, book_author, book_publisher, book_description, book_comment) VALUES ('$cols[0]', '$cols[1]', '$cols[2]', '$cols[3]', '$cols[4]', '$cols[5]', '$cols[6]', '$cols[7]', '$cols[8]', '$cols[9]', '$cols[10]')";
mysql_query($sql)or die(mysql_error());
}
?>
