How can I put XML into mysql tables, keeping encoding?

I have an XML file with data from a latin1 encoded, latin1_swedish_ci collated table that I want to transfer to another mysql table. I have set up the destination table with the same encodings. I query the database for each row of the table and I end up with all the special characters messed up. I have also set the encoding in the XML file <?xml version="1.0" encoding="latin1" ?>. Can it be a problem of PHP? Do I have to set up the encoding somewhere else:

Here is my script:

    // create table courses if not exists
echo("Creating new tables...\n");
if (!mysql_query("CREATE TABLE IF NOT EXISTS members (
    id int(11) NOT NULL,
    isfg_no int(11) NOT NULL,
    lab_id int(11) NOT NULL,
    first varchar(256) NOT NULL,
    last varchar(512) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1"))
    die(mysql_error());

    // load xml file
echo "Loading XML file...\n";
$members = simplexml_load_file("members.xml");

foreach ($members as $row) {
    $id = mysql_real_escape_string($row->SO_IdSocio);
    $isfg_no = mysql_real_escape_string($row->SO_Numisfg);
    $lab_id = mysql_real_escape_string($row->SO_Numlab);
    $first = mysql_real_escape_string($row->SO_Nombre);
    $last = mysql_real_escape_string($row->SO_Apellidos);

    if (!mysql_query("INSERT INTO members (id, isfg_no, lab_id, first, last)VALUES ('$id', '$isfg_no', '$lab_id', '$first', '$last')")) 
        die(mysql_error());
}

Here is some of the XML

<?xml version="1.0" encoding="latin1" ?>
<SOCIOS_INTRA>
<row>
    <SO_IdSocio>1</SO_IdSocio>
    <SO_Numsocio>001</SO_Numsocio>
    <SO_Numisfg>404</SO_Numisfg>
    <SO_Nombrelab>SIN LABORATORIO</SO_Nombrelab>
    <SO_Numlab>0</SO_Numlab>
    <SO_Nombre>******</SO_Nombre>
    <SO_Apellidos>*******</SO_Apellidos>
    <SO_Direccion>C/Recoletos 22, 3ª planta</SO_Direccion>
    <SO_Cp>28001</SO_Cp>
    <SO_Poblacion>Madrid</SO_Poblacion>
    <SO_Provincia>Madrid</SO_Provincia>
    <SO_Idpais>25</SO_Idpais>
    <SO_Email1>*******</SO_Email1>
    <SO_Email2></SO_Email2>
    <SO_Telefono>***</SO_Telefono>
    <SO_Fax>****</SO_Fax>
    <SO_Login></SO_Login>
    <SO_Password></SO_Password>
    <SO_Fecha>19960101</SO_Fecha>
    <SO_SituacionISFG>0</SO_SituacionISFG>
    <SO_SituacionGEP>1</SO_SituacionGEP>
    <SO_Observaciones></SO_Observaciones>
</row>

Answers


You don't need to set encoding in PHP. You should use SET NAMES 'latin1' as first SQL-statement.

UPDATE:

I think that you have misconfigured server. I have run this code and all works perfectly

<?php

$lnk=mysql_connect('localhost', 'root', '');
mysql_select_db('test', $lnk);

echo("Creating new tables...\n");
if (!mysql_query("CREATE TABLE IF NOT EXISTS members (
    id int(11) NOT NULL,
    isfg_no int(11) NOT NULL,
    lab_id int(11) NOT NULL,
    first varchar(256) NOT NULL,
    last varchar(512) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1"))
    die(mysql_error());

    // load xml file
echo "Loading XML file...\n";
$members = simplexml_load_file("members.xml");

foreach ($members as $row) {
    $id = mysql_real_escape_string($row->SO_IdSocio);
    $isfg_no = mysql_real_escape_string($row->SO_Numisfg);
    $lab_id = mysql_real_escape_string($row->SO_Numlab);
    $first = mysql_real_escape_string($row->SO_Nombre);
    $last = mysql_real_escape_string($row->SO_Apellidos);

    if (!mysql_query("INSERT INTO members (id, isfg_no, lab_id, first, last)VALUES ('$id', '$isfg_no', '$lab_id', '$first', '$last')")) 
        die(mysql_error());
    else
        print("INSERT INTO members (id, isfg_no, lab_id, first, last)VALUES ('$id', '$isfg_no', '$lab_id', '$first', '$last')<br />");
}

?>

and this XML

<?xml version="1.0" encoding="latin1" ?>
<SOCIOS_INTRA>
<row>
    <SO_IdSocio>2</SO_IdSocio>
    <SO_Numsocio>001</SO_Numsocio>
    <SO_Numisfg>404</SO_Numisfg>
    <SO_Nombrelab>SIN LABORATORIO</SO_Nombrelab>
    <SO_Numlab>0</SO_Numlab>
    <SO_Nombre>******</SO_Nombre>
    <SO_Apellidos>*******</SO_Apellidos>
    <SO_Direccion>C/Recoletos 22, 3ª planta</SO_Direccion>
    <SO_Cp>28001</SO_Cp>
    <SO_Poblacion>Madrid</SO_Poblacion>
    <SO_Provincia>Madrid</SO_Provincia>
    <SO_Idpais>25</SO_Idpais>
    <SO_Email1>*******</SO_Email1>
    <SO_Email2></SO_Email2>
    <SO_Telefono>***</SO_Telefono>
    <SO_Fax>****</SO_Fax>
    <SO_Login></SO_Login>
    <SO_Password></SO_Password>
    <SO_Fecha>19960101</SO_Fecha>
    <SO_SituacionISFG>0</SO_SituacionISFG>
    <SO_SituacionGEP>1</SO_SituacionGEP>
    <SO_Observaciones></SO_Observaciones>
</row><row>
    <SO_IdSocio>3</SO_IdSocio>
    <SO_Numsocio>001</SO_Numsocio>
    <SO_Numisfg>404</SO_Numisfg>
    <SO_Nombrelab>SIN LABORATORIO</SO_Nombrelab>
    <SO_Numlab>0</SO_Numlab>
    <SO_Nombre>******</SO_Nombre>
    <SO_Apellidos>*******</SO_Apellidos>
    <SO_Direccion>C/Recoletos 22, 3ª planta</SO_Direccion>
    <SO_Cp>28001</SO_Cp>
    <SO_Poblacion>Madrid</SO_Poblacion>
    <SO_Provincia>Madrid</SO_Provincia>
    <SO_Idpais>25</SO_Idpais>
    <SO_Email1>*******</SO_Email1>
    <SO_Email2></SO_Email2>
    <SO_Telefono>***</SO_Telefono>
    <SO_Fax>****</SO_Fax>
    <SO_Login></SO_Login>
    <SO_Password></SO_Password>
    <SO_Fecha>19960101</SO_Fecha>
    <SO_SituacionISFG>0</SO_SituacionISFG>
    <SO_SituacionGEP>1</SO_SituacionGEP>
    <SO_Observaciones></SO_Observaciones>
</row>
</SOCIOS_INTRA>

Need Your Help

How do you profile your code?

c profiling productivity

I hope not everyone is using Rational Purify.

Getting an integer for the date when the input is a string

c# winforms datetime

I'm struggling to understand how to get the numbered day (of the month) that corresponds to the string that contains the name of that day. For example, let's assume that Monday is the first of Janu...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.