Replacing special characters when importing xml feed into MySQL database

Question

I've got a php script which imports the contents of an xml file into a MySQL database, but the character é is being saved in the db as Ã©

What is the best way to solve this problem so that the character é is saved as e in the db.

The php file:

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'xmltosqldb';
mysql_select_db($dbname);

$sxe = new SimpleXMLElement('http://api.xmlscores.com/matches/?f=xml&c[]=eng_pl&e=1&s=0&l=128&open=e7df44075754027024f0c9e328a64fc4', NULL, TRUE);

foreach($sxe->children() as $match) {
    foreach($match->children() as $item) {
        echo "{$item->teams->hosts->name}<br />";
        $matchstart = $item['timestamp-starts'];
        $hostid = $item->teams->hosts[id];
        $hostname = $item->teams->hosts->name;
        $hostfullname = $item->teams->hosts->fullname;
        $awayid = $item->teams->guests[id];
        $awayname = $item->teams->guests->name;
        $awayfullname = $item->teams->guests->fullname;
        $title = $item->details->contest->competition->title;
        $season = $item->details->contest->season;
        $fixture = $item->details->{'fixture-info'};
        $currenttime = date( "Y-m-d H:m:i");
        $sql = "SELECT match_status FROM matches WHERE match_id ='$item[id]'";
        $result = mysql_query($sql) or die(mysql_error());
        $num    = mysql_num_rows($result);

        //Check if ID exists
            //IF YES
        if($num == '1'){
            $row = mysql_fetch_array($result);

            //Check if status changed
                if($item[status] != $row['match_status']){
                //IF YES
                    //Change status & Update scores
                        $sql = "UPDATE matches SET match_status = '$item[status]', match_score = '$item->score', date_updated = '$currenttime' WHERE match_id ='$item[id]'";
                        echo $sql;
                            echo '<br />';
                            echo '<br />';
                        mysql_query($sql) or die(mysql_error());
                    if($item[status] == 'finished'){    
                        foreach($item->events->children() as $event) {
                            $sql = "INSERT INTO events (match_id,match_starts,home_id,away_id,event_type,event_team,event_player,event_score,event_minute,date_updated) 
                                    VALUES ('$item[id]',
                                    '$matchstart',
                                    '$hostid',
                                    '$awayid',
                                    '$event[type]',
                                    '$event[team]',
                                    '$event->player',
                                    '$event->score',
                                    '$event->minute',
                                    '$currenttime'
                                    )";
                            echo $sql;
                            echo '<br />';
                            echo '<br />';
                            mysql_query($sql) or die(mysql_error());
                        }//END EVENTS FOREACH LOOP
                    }//IF STATUS == FINISHED
                }//IF STATUS CHANGED
        }//IF RECORD EXISTS

        else{
        //IF NO

            //Create ID & is Status Finished
            $sql = "INSERT INTO matches ( 
                                match_contest, 
                                match_id,
                                match_status, 
                                match_starts, 
                                home_id, 
                                home_name, 
                                home_fullname, 
                                away_id, 
                                away_name, 
                                away_fullname, 
                                match_score, 
                                match_competition_id, 
                                match_competition_title,
                                match_season,
                                match_fixture, 
                                date_updated) 
                    VALUES      (
                    '$item[contest]',
                    '$item[id]',
                    '$item[status]',
                    '$matchstart',
                    '$hostid',
                    '$hostname',
                    '$hostfullname',
                    '$awayid',
                    '$awayname',
                    '$awayfullname',
                    '$item->score',
                    '$title',
                    '$title',
                    '$season',
                    '$fixture',
                    '$currenttime')";
            mysql_query($sql) or die(mysql_error());
            echo $sql;
            echo '<br />';
            echo '<br />';

            if($item[status] == 'finished'){    
                foreach($item->events->children() as $event) {
                    $sql = "INSERT INTO events (match_id,match_starts,home_id,away_id,event_type,event_team,event_player,event_score,event_minute,date_updated) 
                            VALUES ('$item[id]',
                            '$matchstart',
                            '$hostid',
                            '$awayid',
                            '$event[type]',
                            '$event[team]',
                            '$event->player',
                            '$event->score',
                            '$event->minute',
                            '$currenttime'
                            )";
                    mysql_query($sql) or die(mysql_error());
                    echo $sql;
                    echo '<br />';
                    echo '<br />';
                }//END EVENTS FOREACH LOOP
            } //END IF STATUS == FINISHED   
        }//END IF MATCH ID DOESN'T ALREADY EXIST    
    }//END ITEMS FOREACH LOOP       
}//END MATCH FOREACH LOOP
?>

The xml feed:

<?xml version="1.0" encoding="UTF-8"?>
<xslf:livescore-feed xsi:schemaLocation="http://xmlscores.com/XSLF http://xmlscores.com/schemas/2/matches.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xslf="http://xmlscores.com/XSLF" timestamp-created="1346930723">
      <matches total-count="1" first="1" last="1">
      <item contest="eng_pl" id="8efd06e37b1d9e209f6ec7b0045e9a66" status="finished" timestamp-starts="1346517000">
         <teams>
            <hosts id="mcfc_en">
               <name>Man. City</name>
               <fullname>Manchester City FC</fullname>
            </hosts>
            <guests id="qprfc_en">
               <name>QPR</name>
               <fullname>Queens Park Rangers FC</fullname>
            </guests>
         </teams>
         <score>3 - 1</score>
         <details>
            <contest>
               <competition id="eng_pl">
                  <title>eng_pl</title>
               </competition>
               <season>2012/2013</season>
            </contest>
            <fixture-info>3</fixture-info>
         </details>
         <events>
                <event xsi:type="xslf:GoalEvent" type="goal" team="hosts">
               <player>Touré Y.</player>
               <score>1 - 0</score>
               <minute>16</minute>
            </event>
                    <event xsi:type="xslf:CardEvent" type="yellow_card" team="hosts">
               <player>Kolarov A.</player>
               <minute>46</minute>
            </event>
                    <event xsi:type="xslf:GoalEvent" type="goal" team="guests">
               <player>Zamora B.</player>
               <score>1 - 1</score>
               <minute>59</minute>
            </event>
                    <event xsi:type="xslf:GoalEvent" type="goal" team="hosts">
               <player>Dzeko E.</player>
               <score>2 - 1</score>
               <minute>61</minute>
            </event>
                    <event xsi:type="xslf:CardEvent" type="yellow_card" team="hosts">
               <player>Rodwell J.</player>
               <minute>75</minute>
            </event>
                    <event xsi:type="xslf:CardEvent" type="yellow_card" team="guests">
               <player>Zamora B.</player>
               <minute>86</minute>
            </event>
                    <event xsi:type="xslf:GoalEvent" type="goal" team="hosts">
               <player>Tévez C.</player>
               <score>3 - 1</score>
               <minute>90</minute>
            </event>
             </events>
      </item>
   </matches>
</xslf:livescore-feed>

Possibly http://stackoverflow.com/questions/132318/how-do-i-correct-the-character-encoding-of-a-file could answer your question. — Esben Tind, Sep 29 '12 at 10:26

score 0 · Answer 1 · answered Sep 06 '12 at 12:35

0

do a

htmlspecialchars($variable);

on variables, that could have a special character.

answered Sep 06 '12 at 12:35

Florian Bauer

626
3
12

score 0 · Answer 2 · answered Sep 06 '12 at 13:05

Matt, u know that mysql_* functions are DEPRECATED in new php versions? From now even official guides on php.net says to use PDO or mysqli_*.

Also, as a sweet cookie for developer, there is no more pain with injections and special characters. Try it and u'll love it :)

Some code for example (mostly based on your php script):

// This is DataBase connection
try{
    $dbh = new PDO("mysql:host=$host;dbname=$name", $user, $pass);      
}
catch(PDOException $e){
    die ('Error Connecting to DB!');
}

// Example for your first SELECT
$q = $DB->prepare("SELECT match_status FROM matches WHERE match_id = ?"); 
if( $q->execute( array($item[id]) ) ) 
{
    $result = $q->fetch(PDO::FETCH_ASSOC);
    //..other code
}

After this code in $result variable u'll got an array.

More info on: http://www.php.net/manual/en/book.pdo.php

prepared statements will make data insertion correct and exact — StasGrin, Sep 29 '12 at 17:08

score 0 · Answer 3 · answered Sep 29 '12 at 11:04

0

Are you sure that the encoding of both your php file and your database table/columns are UTF-8 (without byte order mark)? If one of them isn't, that could very easily be causing this!

answered Sep 29 '12 at 11:04

Esben Tind

885
4
14

Replacing special characters when importing xml feed into MySQL database

3 Answers3