By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,190 Members | 1,469 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,190 IT Pros & Developers. It's quick & easy.

input with special characters disappears on mysql insert

P: 4
Hi all.
I'm having a problem with a special characters.
I have php script that reads from an xml file and writes to a mysql db.
It's a script called phptunest that I found on the net, although the
original website for the author appears to be gone.

It works really nicely except when it hits special characters. Everything from a sp char forward
just gets lost. It is using mysql_real_escape_string, but that doesn't seem to help with the
weird characters like (, é)
For example,
here's a couple of xml entries that get screwed up:

<dict>
<key>Track ID</key><integer>6583</integer>
<key>Name</key><string>360 (Oh Yeah?)</string>
<key>Artist</key><string>Propellerheads</string>
.....

<dict>
<key>Track ID</key><integer>13257</integer>
<key>Name</key><string>Agua De Bebér</string>
<key>Artist</key><string>Sergio Mendes & Brasil '66</string>
...

When the script inserts to db then reads them out into html they come out as:

<tr><td width="10%">360</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">0 KB</td>
<td width="10%"></td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
</tr>

<tr><td width="10%">Agua De Beb</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">0 KB</td>
<td width="10%"></td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
<td width="10%">&nbsp;</td>
</tr>

I have figured out that the problem is in the sql insert.
When it inserts it to the db everthing in that record from the special characters (, é)
just gets lost.

Here's the code snippets that do the insert.

// Pushes a single tune into MySQL table
function mysqlPushIntoTable(&$tune)
{
if (!isset($tune['Track ID']))
return;
foreach($this->allCols as $col)
{
if(!isset($tune["$col"]))
$tune["$col"] = 0;
$tune["$col"] = str_replace("%", "\%", mysql_real_escape_string($tune[$col]));
}
foreach($tune as $key=>$val)
$tune["$key"] = utf8_decode($val);
if ($tune['Track ID'] == 0)
die("phptunest error, Track ID is 0! Please report this error to nikita@maczsoftware.com.<br>");
$quer = "INSERT INTO phptunest_tunes values(\"" . $tune['Track ID'] . "\", \"" . $tune['Name'] . "\", \"" . $tune['Artist'] . "\", \"" . $tune['Composer'] . "\", \"" . $tune['Album'] . "\", \"" . $tune['Genre'] . "\", \"" . $tune['Kind'] . "\"," . $tune['Size'] . "," . $tune['Total Time'] . "," . $tune['Disc Number'] . ", " . $tune['Disc Count'] . "," . $tune['Track Number'] . "," . $tune['Track Count'] . "," . $tune['Year'] . "," . $tune['BPM'] . ", " . "\"" . $tune['Date Modified'] . "\", \"" . $tune['Date Added'] . "\", " . $tune['Bit Rate'] . "," . $tune['Sample Rate'] . "," . "\"" . $tune['Equalizer'] . "\", \"" . $tune['Comments'] . "\"," . $tune['Play Count'] . "," . $tune['Play Date'] . "," . "\"" . $tune['Play Date UTC'] . "\", " . $tune['Normalization'] . "," . $tune['Rating'] . "," . $tune['File Type'] . "," . $tune['File Creator'] . "," . "\"" . $tune['Location'] . "\", " . $tune['File Folder Count'] . "," . $tune['Library Folder Count'] . ")";
mysql_query($quer) or die("Couldn't insert new tune records into database<br>" . mysql_error() . "<br>Query was: $quer<br>");
}

called by:
function xmlCharacterData($parser, $data)
{
global $phptunestXmlDepth,$phptunestAboutToClose, $phptunestValueComing, $phptunestLastKey;

if (($phptunestXmlDepth == 5) && ($data != ""))
{
if($phptunestAboutToClose) // last entry!
{
$this->curTune['Library Folder Count'] = $data;
if ($this->enableSorting == 0 && $this->mysqlEnabled == 0)
$this->outputTune($this->curTune);
else
$this->mysqlPushIntoTable($this->curTune);


...
// Using implode(file()), because file_get_contents() requires
// PHP 4.3, so why bother :)
if (!$tunestr = implode("\n", file($this->file)))
die("Couldn't load XML data");

// We won't use xml_parse_into_struct() here.
// Let's use event-driven track-by-track parsing, with saving each
// track into MySQL database (this saves LOT of RAM).
xml_set_element_handler($tuneXML, array($this, "xmlStartElement"), array($this, "xmlEndElement"));
xml_set_character_data_handler($tuneXML, array($this, "xmlCharacterData"));

Can anyone tell me how to fix this?

Many thanks,
Dan
Aug 2 '07 #1
Share this Question
Share on Google+
6 Replies


kovik
Expert 100+
P: 1,044
What is the character encoding on your page and your database?
Aug 2 '07 #2

P: 4
Hope someone's still around, I got sidetracked for a bit.

I looked and my db encoding just has character-set=latin1

I see that there are numerous options and numerous collation values to add.

How in the world do I figure out which ones to use?

dan
Aug 23 '07 #3

100+
P: 228
jx2
Hope someone's still around, I got sidetracked for a bit.

I looked and my db encoding just has character-set=latin1

I see that there are numerous options and numerous collation values to add.

How in the world do I figure out which ones to use?

dan
if you dont know which one or you use many languages set every thing (DB and php and server) to UTF-8 thats universal coding (but it takes twice as much space in your database)

regards
jx2
Aug 23 '07 #4

P: 4
OK, I've set the default character set to UTF-8 in my php.ini.
(The iTunes XML file has a default of UTF-8)
I also ran an ALTER DATABASE, and an ALTER TABLE command to
set the character set of my mysql db/table to UTF-8.

Reran the php file and I still get the same results. Nothing past the special characters gets into the db (for each xml record).

Any suggestions? Ideas on what I'm missing?

thanks,
Dan
Aug 27 '07 #5

P: 4
Hi.
I posted about this a while back, but I still haven't solved it and have some more information.
I've got a php script I found on the net that parses your iTunes xml file, optionally dumps it into a mysql db, then produces an html file of your library.
Tracks that include special characters, like (, é) get truncated after the special character.

I have the encoding for mysql and php set to UTF-8, same as the xml file.
If I do a direct sql insert of the track info into mysql it goes in just fine.
Also, the script has an option to NOT use mysql, just do it in internal memory, calls "ramParse()"?? - If I use that option, the html file comes out correct, with special characters, etc.

So that seems to tell me that php itself is ok with the sp chars and mysql is ok with them, so it appears to be the mysqlParse routine that is somehow choking on the data and not inserting it correctly. Here is the relevant(?) code, can anyone help?

Expand|Select|Wrap|Line Numbers
  1. // Pushes a single tune into MySQL table
  2. function mysqlPushIntoTable(&$tune)
  3. {
  4. if (!isset($tune['Track ID']))
  5. return;
  6. foreach($this->allCols as $col)
  7. {
  8. if(!isset($tune["$col"]))
  9. $tune["$col"] = 0;
  10. $tune["$col"] = str_replace("%", "\%", mysql_real_escape_string($tune[$col]));
  11. }
  12. foreach($tune as $key=>$val)
  13. $tune["$key"] = utf8_decode($val);
  14. if ($tune['Track ID'] == 0)
  15. die("phptunest error, Track ID is 0! Please report this error to __MUNGED__.<br>");
  16. $quer = "INSERT INTO phptunest_tunes values(\"" . $tune['Track ID'] . "\", \"" . $tune['Name'] . "\", \"" . $tune['Artist'] . "\", \"" . $tune['Composer'] . "\", \"" . $tune['Album'] . "\", \"" . $tune['Genre'] . "\", \"" . $tune['Kind'] . "\"," . $tune['Size'] . "," . $tune['Total Time'] . "," . $tune['Disc Number'] . ", " . $tune['Disc Count'] . "," . $tune['Track Number'] . "," . $tune['Track Count'] . "," . $tune['Year'] . "," . $tune['BPM'] . ", " . "\"" . $tune['Date Modified'] . "\", \"" . $tune['Date Added'] . "\", " . $tune['Bit Rate'] . "," . $tune['Sample Rate'] . "," . "\"" . $tune['Equalizer'] . "\", \"" . $tune['Comments'] . "\"," . $tune['Play Count'] . "," . $tune['Play Date'] . "," . "\"" . $tune['Play Date UTC'] . "\", " . $tune['Normalization'] . "," . $tune['Rating'] . "," . $tune['File Type'] . "," . $tune['File Creator'] . "," . "\"" . $tune['Location'] . "\", " . $tune['File Folder Count'] . "," . $tune['Library Folder Count'] . ")";
  17. mysql_query($quer) or die("Couldn't insert new tune records into database<br>" . mysql_error() . "<br>Query was: $quer<br>");
  18. }
  19.  
  20. called by:
  21. function xmlCharacterData($parser, $data)
  22. {
  23. global $phptunestXmlDepth,$phptunestAboutToClose, $phptunestValueComing, $phptunestLastKey;
  24.  
  25. if (($phptunestXmlDepth == 5) && ($data != ""))
  26. {
  27. if($phptunestAboutToClose) // last entry!
  28. {
  29. $this->curTune['Library Folder Count'] = $data;
  30. if ($this->enableSorting == 0 && $this->mysqlEnabled == 0)
  31. $this->outputTune($this->curTune);
  32. else
  33. $this->mysqlPushIntoTable($this->curTune);
  34.  
  35.  
  36. ...
  37. // Using implode(file()), because file_get_contents() requires
  38. // PHP 4.3, so why bother :)
  39. if (!$tunestr = implode("\n", file($this->file)))
  40. die("Couldn't load XML data");
  41.  
  42. // We won't use xml_parse_into_struct() here.
  43. // Let's use event-driven track-by-track parsing, with saving each
  44. // track into MySQL database (this saves LOT of RAM).
  45. xml_set_element_handler($tuneXML, array($this, "xmlStartElement"), array($this, "xmlEndElement"));
  46. xml_set_character_data_handler($tuneXML, array($this, "xmlCharacterData"));
Oct 23 '07 #6

pbmods
Expert 5K+
P: 5,821
Heya, Dan.

Make sure your database and tables use the UTF character set and also check to see that your connection encoding is UTF.

You can take care of the latter with these two lines:
Expand|Select|Wrap|Line Numbers
  1. mysql_query("SET NAMES 'utf8'");
  2. mysql_query("SET CHARACTER SET 'utf8'");
  3.  
Nov 27 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.