Support Forum

Active Forum Topics

parsing xml into mysql database

Submitted by bali2002 on Fri, 2009-03-13 06:04 in Magic Parser

I need some help to parse an xml file into a mysql database, I've been trying some of the examples showed in this forum with no results.

the xml file is:

http://www.expocursos.com/XML/cursos.asp?pagina=1&temas=13&subid=XXXX

Any help would be apreciated

Hello Hugo, If you have been

Submitted by support on Fri, 2009-03-13 07:23

Hello Hugo,

If you have been writing code that is not working, it is most likely that your database table does not match the SQL. You can always use mysql_error() to indicate what is wrong.

Based on your XML feed, is a basic example of how you would import that into MySQL:

<?php
  require("MagicParser.php");
  $conn =  mysql_connect("localhost","username","password"); // change for your server!
  $link =  mysql_select_db("databasename",$conn); // change for your server
  function myRecordHandler($record)
  {
    global $link;
    // change curso to the name of your database table, and the fields as required
    // and add any more fields as required
    $sql = "INSERT INTO curso SET
              id = '".mysql_escape_string($record["CURSO-ID"])."',
              nombre = '".mysql_escape_string($record["NOMBRE"])."',
              urlmasinfo = '".mysql_escape_string($record["URLMASINFO"])."',
              duracion = '".mysql_escape_string($record["DURACION"])."'
              "; // ^^ don't forget the last one doesn't have a comma after ^^
    mysql_query($sql,$link);
    // and for debugging:
    print mysql_error();
    print "<br />";
  }
  // we might want to empty the table before we start
  $sql = "TRUNCATE curso";
  mysql_query($sql,$link);
  // now parse the XML to load the new records
  MagicParser_parse("http://www.expocursos.com/XML/cursos.asp?pagina=1&temas=13&subid=XXXX","myRecordHandler","xml|CURSOS/CURSO/");
?>

If you're still having problems, feel free to post your code and I will take a look for you!

Cheers,
David.

Thanks David, I tried the

Submitted by bali2002 on Fri, 2009-03-13 10:50

Thanks David,

I tried the code you gave me, and get this error:

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/bali2002/public_html/parser.php on line 23

And, even if I remove the snippet that truncates the table:

$sql = "TRUNCATE cursos1";
mysql_query($sql,$link);

I don´t get any errors but the table remains empty (it doesn't get populated with the data)

Thanks!!

Hi Hugo, What I normally do

Submitted by support on Fri, 2009-03-13 10:53

Hi Hugo,

What I normally do in situations like that is to print the SQL that is being generated, and then exit, for example:

  function myRecordHandler($record)

  {

    global $link;

    // change curso to the name of your database table, and the fields as required

    // and add any more fields as required

    $sql = "INSERT INTO curso SET

              id = '".mysql_escape_string($record["CURSO-ID"])."',

              nombre = '".mysql_escape_string($record["NOMBRE"])."',

              urlmasinfo = '".mysql_escape_string($record["URLMASINFO"])."',

              duracion = '".mysql_escape_string($record["DURACION"])."'

              "; // ^^ don't forget the last one doesn't have a comma after ^^

    print $sql;exit();

    mysql_query($sql,$link);

    // and for debugging:

    print mysql_error();

    print "<br />";

  }

Then, if you're using a tool like phpMyAdmin, copy & paste the SQL into the execute SQL form in phpMyAdmin and try it directly - you will be able to see any error messages there.

Also feel free to email me your code if you like and I'll take a look!

Cheers,
David.

ok, this is the error i get

Submitted by bali2002 on Fri, 2009-03-13 11:19

ok,

this is the error i get when i copy & paste the SQL into the execute SQL form in phpMyAdmin:

FUNCTION myRecordHandler(
$record
){ GLOBAL $link;

MySQL ha dicho: Documentación
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'function myRecordHandler($record)
{
global $link' at line 1

Hi Hugo, That sounds like

Submitted by support on Fri, 2009-03-13 11:21

Hi Hugo,

That sounds like something is wrong with the $sql rather than the SQL itself. Can you email me your code and I will have a look for you!

Thanks,
David.

I've also emailed you the

Submitted by bali2002 on Fri, 2009-03-13 11:22

I've also emailed you the code (email subject:code xml into mysql)

thx!

Hi! I Have exactly the same

Submitted by raPn3ss on Thu, 2009-07-23 04:28

Hi!

I Have exactly the same Problem!! Is there any known solution for this problem?

Thanks!

Hello raPn3ss, Database

Submitted by support on Thu, 2009-07-23 09:08

Hello raPn3ss,

Database errors can occur for any number of reasons - it's not something that there is a specific solution for i'm afraid but there are things you can do to help you work out what the problem is.

Firstly, make sure that you are including appropriate debug code in your connection to the database server and selection of your database, for example;

  $conn =  mysql_connect("localhost","username","password"); // change for your server!

  if (!$conn) die("Database connection failed");

  $link =  mysql_select_db("databasename",$conn); // change for your server

  if (!$link) die("Database selection failed");

After that, it's a case of ensuring that the SQL that you script constructs is compatible with your table, and also of course that you are using mysql_escape_string() around the data from your XML document in order to ensure that it does not break the SQL. To check this, a simple print statement after you have constructed your SQL into a variable (i.e. $sql) is normally what I do; and then you can copy the SQL from the output of your script into a database debugging tool like phpMyAdmin (select your database, then go to the SQL tab) and this will give you detailed error information if there is a problem with the SQL in relation to your table.

Alternatively, you could add the mysql_error() function to your code, so instead of:

mysql_query($sql,$link);

try;

    if (!mysql_query($sql,$link))

    {

      die(mysql_error());

    }

Hope this helps!
Cheers,
David.