Hi All,

I Strongly need your help for my urgent home work.

i am new to php so i can't solve my case but this very urgent to me, please somebody help me...

we have games online and the scores update daily the the scores showed to xml file

inside the xml file itself look like:

<gesmes:Envelope>
<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:Envelope>

i must taking data from that xml file and put to mysql table. now the script already exist as look like:

PHP Code:

<?php
 
class Scores_Converter {
   
   var $xml_file = "http://192.168.1.112/gamescores/scores-daily.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 store 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." ( scores char(3) NOT NULL default '', value float NOT NULL default '0', PRIMARY KEY(scores) ) ENGINE=MyISAM";
      
      $rs =  mysql_query($sql,$conn) or die(mysql_error());
 
      $sql = "INSERT INTO ".$this->mysql_table." VALUES('GameA0',1)";
 
      $rs =  mysql_query($sql,$conn) or die(mysql_error());
      
      $this->downloadValueScores();   
   }
 
}
?>

then the table result in mysql from script above look like:

CREATE TABLE IF NOT EXISTS `scrore_table` (
  `scores` char(3) NOT NULL default '',
  `value` float NOT NULL default '0',
  PRIMARY KEY  (`scores`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `scrore_table`
--

INSERT INTO `scrore_table` (`scores`, `value`) VALUES
('GameA0', 1),
('GameA1', 1.5651),
......etc
('GameA15', 95.572);

but i should customize the scores table above and put it to the existing mysql table look like:

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(3) 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.52600002,  '2010-04-06 22:00:54'),
..............................etc
(3, 'Game Class A15', 'GameA15', '2', 1.13999999,  '2010-04-06 22:00:54');

how can i add new row/column e.g. scrore_id, scrore_title,decimal_place, date_updated to php script above?

I am sorry, i'm blind with php code, any suggestions are welcome and thanks in advance

Still no body? please help...

I'd see 2 ways.

1st: Adapt your createTable() and downloadValueScores() functions to have the missing fields, and the correct insert statements.

2nd: If the missing infos are easy to add by hand, as this is urgent, do a 2 step insert.
First insert as you do now, but in a "to be deleted" table.
Then, insert values into the final table, with the correct structure from the first.

So, given that you have run your procedure, and everything is into the table "step1", you would start a query like this in mysql:

INSERT INTO scrore_table (scrore_title, scores, decimal_place, value, date_updated) 
SELECT concat('Game Class A',right(scores,1)), scores, 2, value, now() 
from step1;

Which will populate your scrore_table with datas from the step1 table.

I don't know what "decimal" should be, so I specify it to always be 2.
And I made the score_title follow the pattern you gave in your example. adapt as needed.

You don't want to output the scrore_id field. Let the DB compute it as an auto_increment field.
You simply don't specify it into the insert.

You have an error in your table definition too.

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(3) 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;

This won't allow "GameAXX" to fit entirely. They will be trimmed to "Gam".
You need to specify an larger field size.

Finally i got reply of my first posted, i am happy, i appreciated it and welcome.

then i try to replace:

PHP Code:

$sql = "SELECT * FROM ".$this->mysql_table." WHERE scores='".$scores."'"; 


with:

PHP Code:

$sql = "SELECT concat('Game Class A',right(scores,1)), scores, 2, value, now() "; 


in the function downloadValueScores() section, but it look doesn't work. seem i missed something that i dont know what is it.

but the good news are now

PHP Code:

`scores` varchar(3) collate utf8_bin NOT NULL default '', 


i put to large field size. become

PHP Code:

`scores` varchar(12) collate utf8_bin NOT NULL default '', 


and at the all. my query of function createTable() created look like:

PHP Code:

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 my table seem not auto populate from .xml file data above, i assume that function downloadValueScores() as:

PHP Code:

        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());
            }
         } 


not suitable to my existing query, so please need more assists and many thanks,

tripy you're wonderful and i am sorry if i look stupid with php, i am new to php