Importing MSexcel Sheet to MySQL Apostrophe Error

  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

I'm trying to add data from an excel spreadsheet to a MySQL table. I'm using the common excel reader class for php found on sourceforge. I've been tailoring it to my needs and haven't had any issues until now. I have a list of addresses in an excel spreadsheet that I'm adding to mysql using php. However, I only get about 90% of the records added to the db table when I run my script. I've already checked to make sure my loop was in fact reading every line of the spreadsheet. I did a comparison of which records got stored and which didn't and it seems the only thing the records that didn't get stored have in common are apostrophes. Every record that didn't get stored had an apostrophe. Does anyone know a fix for this or does this sound even like a valid reason for my problem? I'm thinking I may just have to escape the apostrophe, but I'm not sure the best way to do that, maybe str_replace and change ' to \'. I'd then have to figure out how to display. I know I've stored apostrophes and quotes in a mysql table without escaping them before. What is the best practice?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Apostrophes would certainly break your query. Try

Code: [ Select ]
str_replace("'","''",$str);


That's dbl quotes around a single for param 1, and then dbl quotes around two single-quotes for param 2, which is the SQL escape of a single quote.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

Code: [ Select ]
$str = mysql_real_escape_string($str);


http://us3.php.net/manual/en/function.m ... string.php
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Tried both. I went with the mysql_real_escape_string. Either way got me fixed. That was obviously the culprit. Thanks guys.
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6252
  • Loc: Seattle, WA

Post 3+ Months Ago

For your particular problem UPSGuy's solution works just as well, but mysql_real_escape_string() will keep you covered in case any other funky characters show up in your file. ;)

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 134 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.