Parsing XML with php and save it to mysql.

Question

Hi Guys, I have been asked to parse a simple file which is stored as an XML file, the data is to be then save it to a mysql.

However I have absolutely no clue what to do and after looking online all the examples given seem either too complicated for my problem or not the right solution. The XML file looks like this

games_scores.xml

<gesmes:Letter>
<gesmes:subject>Reference Scores</gesmes:subject>
-
<gesmes:Sender>
<gesmes:name>Game Information Scores</gesmes:name>
</gesmes:Sender>
-
<Cube>
-
<Cube time="2010-10-13">
<Cube scores="GameA1" value="1.5803"/>
<Cube scores="GameA2" value="21.35"/>
............etc
<Cube scores="GameA15" value="135"/>
</Cube>
</Cube>
</gesmes:Letter>

then i must saved it to mysql existing, that's looks

CREATE TABLE IF NOT EXISTS `scrore_table` (
  `scrore_id` int(11) NOT NULL auto_increment,
  `scrore_title` varchar(32) collate utf8_bin NOT NULL default '',
  `scores` varchar(15) collate utf8_bin NOT NULL default '',
  `decimal_place` char(1) collate utf8_bin NOT NULL,
  `value` float(15,8) NOT NULL,
  `date_updated` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`currency_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; ;

INSERT INTO `scrore_table` (`scrore_id`, `scrore_title`, `scores`, `decimal_place`, `value`, `date_updated`) VALUES
(1, 'Game Class A0', 'GameA0', '2', 1.00000000, '2010-04-06 22:00:54'),
(2, 'Game Class A1', 'GameA1', '2', 1.58030000,  '2010-04-06 22:00:54'),
(3, 'Game Class A2', 'GameA2', '2', 21.3503000,  '2010-04-06 22:00:54'),
..............................etc
(15, 'Game Class A15', 'GameA15', '2', 135,  '2010-04-06 22:00:54');

directions to this issue i have php script by following:

<?php
class Scores_Converter {
   var $xml_file = "http://192.168.1.112/gamescores/games_scores.xml";
   var $mysql_host, $mysql_user, $mysql_pass, $mysql_db, $mysql_table;
   var $scores_values = array();

   //Load convertion scores
   function Scores_Converter($host,$user,$pass,$db,$tb) {
      $this->mysql_host = $host;
      $this->mysql_user = $user;
      $this->mysql_pass = $pass;
      $this->mysql_db = $db;
      $this->mysql_table = $tb;

      $this->checkLastUpdated();

      $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); 
      $rs = mysql_select_db($this->mysql_db,$conn);
      $sql = "SELECT * FROM ".$this->mysql_table;
      $rs =  mysql_query($sql,$conn);   
      while($row = mysql_fetch_array($rs)) {
         $this->scores_values[$row['scores']] = $row['value'];         
      }
   }

   /* Perform the actual conversion, defaults to 1.00 GameA1 to GameA3 */
   function convert($amount=1,$from="GameA1",$to="GameA3",$decimals=2) {     return(number_format(($amount/$this->scores_values[$from])*$this->scores_values[$to],$decimals));
   }

   /* Check to see how long since the data was last updated */
   function checkLastUpdated() {
      $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass);

      $rs = mysql_select_db($this->mysql_db,$conn);
      $sql = "SHOW TABLE STATUS FROM ".$this->mysql_db." LIKE '".$this->mysql_table."'";
      $rs =  mysql_query($sql,$conn);

      if(mysql_num_rows($rs) == 0 ) {
         $this->createTable();
      } else {
         $row = mysql_fetch_array($rs);
         if(time() > (strtotime($row["Update_time"])+(12*60*60)) ) {
            $this->downloadValueScores();         
         }
      }
   }

   /* Download xml file, extract exchange values and save the values in database */
   function downloadValueScores() {
      $scores_domain = substr($this->xml_file,0,strpos($this->xml_file,"/"));
      $scores_file = substr($this->xml_file,strpos($this->xml_file,"/"));
      $fp = @fsockopen($scores_domain, 80, $errno, $errstr, 10);
      if($fp) {
         $out = "GET ".$scores_file." HTTP/1.1\r\n";
         $out .= "Host: ".$scores_domain."\r\n";
         $out .= "User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8) Gecko/20051111 Firefox/1.5\r\n";
         $out .= "Connection: Close\r\n\r\n";
         fwrite($fp, $out);
         while (!feof($fp)) {
            $buffer .= fgets($fp, 128);
         }
         fclose($fp);

         $pattern = "{<Cube\s*scores='(\w*)'\s*value='([\d\.]*)'/>}is";
         preg_match_all($pattern,$buffer,$xml_values);
         array_shift($xml_values);

         for($i=0;$i<count($xml_values[0]);$i++) {
            $exchange_value[$xml_values[0][$i]] = $xml_values[1][$i];
         }

         $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass);
         $rs = mysql_select_db($this->mysql_db,$conn);

         foreach($exchange_value as $scores=>$value) {
            if((is_numeric($value)) && ($value != 0)) {
               $sql = "SELECT * FROM ".$this->mysql_table." WHERE scores='".$scores."'";
               $rs =  mysql_query($sql,$conn) or die(mysql_error());
               if(mysql_num_rows($rs) > 0) {
                  $sql = "UPDATE ".$this->mysql_table." SET value=".$value." WHERE scores='".$scores."'";
               } else {
                  $sql = "INSERT INTO ".$this->mysql_table." VALUES('".$scores."',".$value.")";
               }
               $rs =  mysql_query($sql,$conn) or die(mysql_error());
            }
         }   
      }
   }

   /* Create the scores table */
   function createTable() {
      $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); 
      $rs = mysql_select_db($this->mysql_db,$conn);
      $sql = "CREATE TABLE ".$this->mysql_table." ( `scrore_id` int(11) NOT NULL, `scrore_title` varchar(32) collate utf8_bin NOT NULL default '', `scrores` char(12) NOT NULL default '', `decimal_place` char(1) collate utf8_bin NOT NULL default '2', `value` float(15,8) NOT NULL,`date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY(currency) ) ENGINE=MyISAM";   
      $rs =  mysql_query($sql,$conn) or die(mysql_error());
      $sql = "INSERT INTO ".$this->mysql_table." VALUES(1,'','GameA0','2',1,now())";
      $rs =  mysql_query($sql,$conn) or die(mysql_error());
      $this->downloadValueScores();   
   }
}
?> 

then with that script mysql table/query created look like:

INSERT INTO `scrore_table` (`scrore_id`, `scrore_title`, `scrores`, `decimal_place`, `value`, `date_updated`) VALUES
(1, 'Game Class A0', 'GameA0', '2', 1.00000000, '2010-10-24 00:27:01');

but the database not auto populate, this seems function downloadValueScores() not work correctly

I've tried fixing it and I think that's the direction I have to go but I just have no idea. also i am sorry, i am new to php.

Any help or pointers would be great and many thanks

Answer 1

Where from do you get the information, that the query looks like that:

INSERT INTO `scrore_table` (`scrore_id`, `scrore_title`, `scrores`, `decimal_place`, `value`, `date_updated`) 
    VALUES (1, 'Game Class A0', 'GameA0', '2', 1.00000000, '2010-10-24 00:27:01');

?

When I look at the function downLoadValueScores, the query will made of this:

$sql = "INSERT INTO ".$this->mysql_table." VALUES('".$scores."',".$value.")";

...this for sure will not build the query above, this will just produce an error, because you have only 2 values assigned for a row to be inserted into a table with 6 fields.

Answer 2

Hi Dr.Molle, You're Right, Great.

now the scrore_id already auto increment,

history modified are: at function createTable() change

$sql = "CREATE TABLE ".$this->mysql_table." ( `scrore_id` int(11) NOT NULL, `scrore_title` varchar(32) collate utf8_bin NOT NULL default '', `scrores` char(12) NOT NULL default '', `decimal_place` char(1) collate utf8_bin NOT NULL default '2', `value` float(15,8) NOT NULL,`date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY(scrore_id) ) ENGINE=MyISAM";

to

$sql = "CREATE TABLE ".$this->mysql_table." ( `scrore_id` int(11) NOT NULL auto_increment, `scrore_title` varchar(32) collate utf8_bin NOT NULL default '', `scrores` char(12) NOT NULL default '', `decimal_place` char(1) collate utf8_bin NOT NULL default '2', `value` float(15,8) NOT NULL,`date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY(scrore_id) ) ENGINE=MyISAM";

auto_increment should be add after scrore_id` int(11) NOT NULL

then for function downloadValueScores()

change:

$sql = "INSERT INTO ".$this->mysql_table." VALUES('NULL','','".$scores."','2',".$value.",now())";

to:

$sql = "INSERT INTO ".$this->mysql_table." VALUES(NULL,'','".$scores."','2',".$value.",now())";

the result my table/query scrore_id auto increment and also auto populate.

Regarding to scrore_title it similar to country code 3 letters and the name of country, e.g.

if USA = 'United State'
if GBR = 'Great Britain'
if FRC = 'France' etc..

following to my issue

if scrores = 'GameA0' then scrore_title = 'Game Class A0'
if scrores = 'GameA1' then scrore_title = 'Game Class A1'
if scrores = 'GameA2' then scrore_title = 'Game Class A2'

in anothers word if found scrores = 'GameA0' then insert the scrore_title = 'Game Class A0' etc..

scrores already figured from games_scores.xml but scrore_title i should figured with myself. is there idea how to solve this?

Many thanks for your helps so far.