SitePoint Sponsor

User Tag List

Results 1 to 1 of 1
  1. #1
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question XML Feed To MySQL Script Help

    Hi,

    I have written a quick script to insert an XML feed into a MySQL database, but I'm having some problems.

    Here is my PHP code:
    Code:
    <?php
    
    include ("financeshop.co.uk/http/include/site_config.php");
    include ("financeshop.co.uk/http/include/dbconnect.php");
    
    
    $type_SQL = "SELECT * FROM xmlfeed";
    //@debug("SQL: " . $type_SQL);
    $type_Query = mysql_query($type_SQL);
    if (!$type_Query) {
        //@debug("ERROR: " . mysql_error());
    } else {
        $type_RS = mysql_fetch_assoc($type_Query);
        $typeTitle = $type_RS['type'];
    }
    
    if (!$_GET['type']) {
        $_GET['type'] = 'all';
    }
    
    
    $file = "http://feeds.directnews.org.uk/?49871849-a276-4a12-b047-bec260ced467";
    $feed = array();
    $key = "";
    $info = "";
    
    function startElement($xml_parser,  $attrs ) {
      global $feed;
       }
    
    function endElement($xml_parser, $name) {
      global $feed,  $info;
      $key = $name;
      $feed[$key] = $info;
      $info = ""; }
    
    function charData($xml_parser, $data ) {
      global $info;
      $info .= $data; }
    
    $xml_parser = xml_parser_create();
    xml_set_element_handler($xml_parser, "startElement", "endElement");
    xml_set_character_data_handler($xml_parser, "charData" );
    $fp = fopen($file, "r");
    while ($data = fread($fp, 8192))
    !xml_parse($xml_parser, $data, feof($fp));
    xml_parser_free($xml_parser);
    
    $sql= "INSERT INTO `xmlfeed` ( `";
    $j=0;
    $i=count($feed);
    foreach( $feed as $assoc_index => $value )
      {
      $j++;
      $sql.= strtolower($assoc_index);
      if($i>$j) $sql.= "` , `";
      if($i<=$j) {$sql.= "` ) VALUES ('";}
      }
     $h=0;
    foreach( $feed as $assoc_index => $value )
      {
      $h++;
      $sql.= utf8_decode(trim(addslashes($value)));
      if($i-1>$h) $sql.= "', '";
      if($i<=$h) $sql.= "','')";
      }
      $sql=trim($sql);
      
      echo $sql;
      $result = mysql_query($sql,$cn);
    ?>
    Here is my mySQL table:
    Code:
    CREATE TABLE xmlfeed (
      id int(9) NOT NULL auto_increment,
      heading text,
      date text,
      contents text,
      category text,
      categories text,
      article text,
      infostreamresults text,
      PRIMARY KEY  (id)
    ) TYPE=MyISAM;
    The code automatically identifies the fields in the XML, and uses these as the fields in the database. I need to use 'Article ID' for one of these fields, but as there is a space, my code ignores it. How do I fix that?

    Also I would like to loop the script, so that it inserts every item in the feed. How would I do that?

    Any help would be appreciated.

    Thanks!
    Neil
    Last edited by silverdisc; Oct 19, 2005 at 07:40.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •