How to resolve "Illegal double ... value found during parsing" on a mysql insert?

Question

For a year or so I've used a php page to import ebay orders from a csv file into a mysql database. This is how my query looks:

 "INSERT INTO orders VALUES (0, {$fname}, {$lname}, {$address1}, {$address2}, {$town}, {$county}, {$postcode}, {$country}, {$tel}, {$email}, '', {$postage}, {$subtotal}, {$total}, {$basket},'', 'eBay', now(), 'GBP', 'paypal', 1, '', 1, {$txnId}) ON DUPLICATE KEY UPDATE txnid=txnid, complete=complete, new=new, time=time;";

Last week I started receiving the error "Illegal double (txnId varchar) value found during parsing" on some orderlines and it prevents me from importing further orders.

I've read suggestions that this is because the value $txnId is not correctly delimited. I have tried changing it to both '{$txnId}' and \'{$txnId}\', which works but in both cases it results in the non-problematic orders having their txnIds prefixed with ''.

Can anyone help me find a way to amend my query so that I no longer receive this error on certain orders? And, as a bonus, can anyone explain to me exactly what this error message means and why it only affects certain orders as I'd like to understand this problem as well as fix it.

Answer 1

The 'E' in the variable may have confused the system into thinking it as a double value because double values can expressed as something similar to "7.012E-5"