Escape special characters

[budimir]

Hey,

 

I'm haveing problem with insert query. Query is breaking and I have found a problem in special characters. I tried to use htmlentites() and mysql_real_esacpe_string(9 but it doesn't work. Can you help me out a bit? What peace of code could I use to escape special characters?

 

This is the peace of code I'm using to insert data from csv into mysql.

 

	//Import uploaded file to Database
	$handle = fopen($_FILES['filename']['tmp_name'], "r");

	while (($data = fgetcsv($handle, 1000000, ";")) !== FALSE) {
	
		$import="INSERT DELAYED INTO kalkulacija_import_kategorija (uvezao, vrijeme,kat_br,naziv_artikla,kategorija_artikla,grupa_proizvoda,podgrupa_proizvoda) VALUES ('$napravio','$vrijeme','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]')";
		echo "$import<br>";
		mysql_query($import) or die(mysql_error());
	
	}

$data[1] is the part that inserts problematic data into mysql. Error message I'm getting is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'N'G BP/225/232','1H','1HRD','1HRDDG')' at line 1

[jazzman1]

Never ever run a query in loop, it's a very bad practice!

 

Anyway, try this:

 

$import  = spintf("INSERT DELAYED INTO kalkulacija_import_kategorija (uvezao, vrijeme,kat_br,naziv_artikla,kategorija_artikla,grupa_proizvoda,podgrupa_proizvoda) VALUES (
        '%s','%s','%s','%s','%s','%s','%s')",
 mysql_real_escape_string($napravio),
 mysql_real_escape_string($vrijeme),
 mysql_real_escape_string($data[0]),
 mysql_real_escape_string($data[1]),
 mysql_real_escape_string($data[2]),
 mysql_real_escape_string($data[3]),
 mysql_real_escape_string($data[4]));

[budimir]

 

Never ever run a query in loop, it's a very bad practice!

 

Anyway, try this:

 

$import  = spintf("INSERT DELAYED INTO kalkulacija_import_kategorija (uvezao, vrijeme,kat_br,naziv_artikla,kategorija_artikla,grupa_proizvoda,podgrupa_proizvoda) VALUES (
        '%s','%s','%s','%s','%s','%s','%s')",
 mysql_real_escape_string($napravio),
 mysql_real_escape_string($vrijeme),
 mysql_real_escape_string($data[0]),
 mysql_real_escape_string($data[1]),
 mysql_real_escape_string($data[2]),
 mysql_real_escape_string($data[3]),
 mysql_real_escape_string($data[4]));

 

jazzman1, thank you so much! That worked like a charm...

 

When you say, to put a query outside of a loop... Do you have example how to do that?

[Psycho]

When you say, to put a query outside of a loop... Do you have example how to do that?

 

If you have multiple records to insert you should create ONE query with all the values to insert.

 

INSERT INTO table
    (field1, field2, field3)
VALUES
    (value1a, value2a, value3a),
    (value1b, value2b, value3b),
    (value1c, value2c, value3c),
    (value1d, value2d, value3d)

 

So, using your above logic you would create the "values" list in the loop, then run one query after the loop with all those values.

[Psycho]

 

//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");

$values = array();
while (($data = fgetcsv($handle, 1000000, ";")) !== FALSE)
{
    //Create record inserts as array elements
    $values[] = spintf("('%s','%s','%s','%s','%s','%s','%s')",
                        mysql_real_escape_string($napravio),
                        mysql_real_escape_string($vrijeme),
                        mysql_real_escape_string($data[0]),
                        mysql_real_escape_string($data[1]),
                        mysql_real_escape_string($data[2]),
                        mysql_real_escape_string($data[3]),
                        mysql_real_escape_string($data[4])
                      );
}

$query="INSERT DELAYED INTO kalkulacija_import_kategorija
            (uvezao, vrijeme,kat_br,naziv_artikla,kategorija_artikla,grupa_proizvoda,podgrupa_proizvoda)
        VALUES " . implode(",\n", $values);
echo "<pre>{$query}</pre><br>";
mysql_query($query) or die(mysql_error());

[jazzman1]

When you say, to put a query outside of a loop... Do you have example how to do that?

 

Check this out reply #11 and you could use the same logic.

 

EDIT: Psycho is already gave you a good example, I like this guy

Edited March 1, 2013 by jazzman1

[budimir]

Guys, thank you so much for the help!!! You saved me...

 

Psycho, you are the man. You gave a such good and simple example. I tried to implement something like that a few times, but I couldn't get to it work. This has pointed out what I have been doing wrong! I'm going to rewrite the rest of my code where I have been using while loops for insert query.

 

Once again, thank you so much!!!

 

I love this forum!!!