[RESOLVED] mysql_real_escape_string?

kbc1

I have this code which reads the contents of a CSV file and inserts it into my database table, however, I am having a couple of problems with some records that do not insert.

Can someone point out where the mysql_real_escape_string function will be applied in order for them to be inserted?

				[code=php]for ($i=1; $i<sizeof($filecontents); $i++) { 
				  $line = trim($filecontents[$i], ',');
				  $arr = explode(",", $line);



				  $q= "INSERT INTO mytable (field1, field2, field3, field4) 

													   VALUES ('". implode("','", $arr) ."')";
				  mysql_query($q);
				}[/code]

Many thanks

scrupul0us


for ($i=1; $i<sizeof($filecontents); $i++) 
{
$line = trim($filecontents[$i], ',');
$arr = explode(",", $line);

$q= "INSERT INTO mytable (field1, field2, field3, field4) VALUES ('". mysql_real_escape_string(implode("','", $arr)) ."')";

mysql_query($q);
}

kbc1

Thanks for the reply but these leave me with a problem. The following query is what is echoed and it obviously does not insert into the database:

INSERT INTO mytable (field1, field2, field3, field4) 

VALUES ('text2\',\'text2\',\'text3\',\'text4\r\n')

I tried changing it to addslashes but this still causes the problem. Is ther any way I can just escape any chars in one field, for example 'field4'?

for ($i=1; $i<sizeof($filecontents); $i++)
{
$line = trim($filecontents[$i], ',');
$arr = explode(",", $line);

$q= "INSERT INTO mytable (field1, field2, field3, field4) VALUES ('". implode("','", $arr) ."')";

mysql_query($q);
}

Many thanks for your help.

scrupul0us

did u try stripslashes?

kbc1

Hi scrupul0us

I have just discovered my problem but if you could offer a solution to the problem that would be great!

One of my field contents in my CSV file has comma's in it. I.e. the field might contain a short sentence such as 'Hello, my head is done in today, but thats life'.

But because i am exploding/imploding on the comma (because its a comma separated document) this gives me my problem.

So is there a way around it?

Thanks in advance

scrupul0us

so i understand, your data might appear as

text, text, im here, text text, text

i dont think youll be able to have php understand which is a token and which is punctuation...

UNLESS you have a space then the comma and another space, then you can plode on " , " otherwise if u can switch the file type to be say tab delimited or pipe "|" delimited then it will be come much easier

kbc1

That is correct. if there is no way around it I will see about changing the file type.

Many Thanks.

scrupul0us

no problem... feel free to contact me if u need further help

Weedpacket

mysql_real_escape_string(implode("','", $arr)) ."')";

Try using mysql_real_escape_string before imploding.

$arr = explode(",", $line); 
$arr = array_map('mysql_real_escape_string', $arr);

And you might want to have a look at using [man]fgetcsv[/man] to read your CSV file. Assuming that fields that contain the character that is used as the field delimiter are quoted, it should be able to tell real field delimiters apart from the fakes.