input with special characters disappears on mysql insert

[TheRealDan]

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_esca pe_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>65 83</integer>
<key>Name</key><string>360 ° (Oh Yeah?)</string>
<key>Artist</key><string>Pro pellerheads</string>
.....

<dict>
<key>Track ID</key><integer>13 257</integer>
<key>Name</key><string>Agu a De Bebér</string>
<key>Artist</key><string>Ser gio 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%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">0 KB</td>
<td width="10%"></td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
</tr>

<tr><td width="10%">Agu a De Beb</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">0 KB</td>
<td width="10%"></td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</td>
<td width="10%">&nb sp;</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 mysqlPushIntoTa ble(&$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_esca pe_string($tune[$col]));
}
foreach($tune as $key=>$val)
$tune["$key"] = utf8_decode($va l);
if ($tune['Track ID'] == 0)
die("phptunest error, Track ID is 0! Please report this error to nikita@maczsoft ware.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($qu er) or die("Couldn't insert new tune records into database<br>" . mysql_error() . "<br>Query was: $quer<br>");
}

called by:
function xmlCharacterDat a($parser, $data)
{
global $phptunestXmlDe pth,$phptunestA boutToClose, $phptunestValue Coming, $phptunestLastK ey;

if (($phptunestXml Depth == 5) && ($data != ""))
{
if($phptunestAb outToClose) // last entry!
{
$this->curTune['Library Folder Count'] = $data;
if ($this->enableSortin g == 0 && $this->mysqlEnabled == 0)
$this->outputTune($th is->curTune);
else
$this->mysqlPushIntoT able($this->curTune);


...
// Using implode(file()) , because file_get_conten ts() 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($tuneX ML, array($this, "xmlStartElemen t"), array($this, "xmlEndElement" ));
xml_set_charact er_data_handler ($tuneXML, array($this, "xmlCharacterDa ta"));

Can anyone tell me how to fix this?

Many thanks,
Dan

[kovik]

What is the character encoding on your page and your database?

[TheRealDan]

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

[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

[TheRealDan]

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

[TheRealDan]

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?

// 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 __MUNGED__.<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"));

[pbmods]

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:

mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER SET 'utf8'");