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 shown 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> Code (markup): i must taking data from that xml file and put to mysql table. now the script already exist as look like: <?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(); } } ?> PHP: 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); Code (markup): but i should customize the scores table above similar to my 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'); Code (markup): 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
in the class function createTable(), you need to change this line: $sql = "CREATE TABLE ".$this->mysql_table." ( scores char(3) NOT NULL default '', value float NOT NULL default '0', PRIMARY KEY(scores) ) ENGINE=MyISAM"; according to your needs: $sql = "CREATE TABLE ".$this->mysql_table." (put here your fields) ENGINE=MyISAM";
Hi max2010 Thanks for your help max2010 I guess being somewhat of a put the fields, I need a bit more of an explanation, then i put there e.g. $sql = "CREATE TABLE ".$this->mysql_table." ( `scores_id` int(11) NOT NULL, `scores_title` varchar(32) collate utf8_bin NOT NULL default '', `scores` char(3) NOT NULL default '', `decimal_place` char(1) collate utf8_bin NOT NULL, `value` float NOT NULL default '0',`date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY(scores) ) ENGINE=MyISAM"; $rs = mysql_query($sql,$conn) or die(mysql_error()); $sql = "INSERT INTO ".$this->mysql_table." VALUES(1,'','GameA0','2',1,'')"; PHP: but i got an error "Column count doesn't match value count at row 1" then the table filled as: CREATE TABLE IF NOT EXISTS `scrore_table` ( `scores_id` int(11) NOT NULL, `scores_title` varchar(32) character set utf8 collate utf8_bin NOT NULL default '', `scores` char(3) NOT NULL default '', `decimal_place` char(1) character set utf8 collate utf8_bin NOT NULL, `value` float NOT NULL default '0', `date_updated` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`scores`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `scrore_table` (`scores_id`, `scores_title`, `scores`, `decimal_place`, `value`, `date_updated`) VALUES (1, '', 'GameA0', '2', 1, '0000-00-00 00:00:00'); PHP: with that table the scores_id,scores_title,scores,decimal_place,value, and date_updated (now/recent date) not automatically filled from var $xml_file = "http://192.168.1.112/gamescores/scores-daily.xml"; i repeat the scores-daily.xml <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> Code (markup): every suggestions and attentions are welcome and many thanks