ERROR: check the manual [...] MySQL server version for the right syntax to use near '

Question

So I have no idea what the deal is here... the following code below produces the mysql error following the code.

$fcontents = file("inventory.csv");

for ($i = 1; $i < sizeof($fcontents); $i++) { 
    $line = trim($fcontents[$i]);
    $arr = explode(',', $line);
    $values = implode(',', $arr);
    $values = str_replace('&', 'and', $values);

    $sql = 'INSERT INTO inventory (dealerid, name, vin, stock, newused, year, ' .
           'make, model, series, body, color, intcolor, price, retailprice, ' .
           'miles, transmission, engine, restraint, certified, photourl, ' .
           'comments, flag, options, citympg, hwympg) ' .
           'VALUES mysql_real_escape_string(' . $values . ')';
     mysql_query($sql);

     echo $sql.'<br><br>';
     if (mysql_error()) {
         echo mysql_error() .'<br><br>';
     }
}

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 '

RESOLVED!!! So I wanted to post the solution to the stupid, dumb single quote, double quote glitch when dumping a .csv file in mysql... See below.:

$fcontents = file("http://pathtofile.com/inventory.csv"); for($i=1; $i < sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode(",", $line); $arr = str_replace ("'","&#39;", $arr); $values = implode("','", $arr); $values = str_replace("\"',",'\'",', $values); $values = str_replace(",'\"",',"\'', $values); $values = str_replace("&", "and", $values); $sql = "INSERT INTO vehicles.inventory (dealerid,name,vin,stock,newused,year,make,model,series,body,color,intcolor,price,retailprice,miles,transmission,engine,restraint,certified,photourl,comments,flag,options,citympg,hwympg) VALUES ('".$values."')"; mysql_query($sql);

Answer 1

Between the explode() and implode() lines, you should be mysql_real_escape_string()-ing each of the values in $arr. The function should be executed in PHP, not sent to MySQL for it to execute.

You could have printed out (or logged) your generated SQL statement and you would probably have spotted the problem.

Answer 2

mysql_real_escape_string() is a PHP function, but you're still within string scope when it appears in your code.

Try this:

$sql = 'INSERT INTO inventory (dealerid, name, vin, stock, newused, year, make, model, series, body, color, intcolor, price, retailprice, miles, transmission, engine, restraint, certified, photourl, comments, flag, options, citympg, hwympg) VALUES (' . mysql_real_escape_string($values) .')';

Answer 3

You have a PHP function in your MYSQL Query.

I don't believe you can just move the function outside of the quotes, but you have to loop through all values:

ie.

foreach($values as $key=>$value){
$values[$key] = mysql_real_escape_string($value);
}

Add that to escape all of the values, then change your query to remove the PHP function.

Also, you have to implode with quotes as well, not just commas.