Hi, I have mysql table that i want to auto fill the empty values with php code, is it possible? the table: CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin NOT NULL default '', `code` varchar(3) collate utf8_bin NOT NULL default '', `top` char(1) collate utf8_bin NOT NULL, `bottom` char(1) NOT NULL, PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, '', 'CAN', '', ''), (2, '', 'ESP', '', ''), (3, '', 'USA', '', ''); PHP: E.g. if code = CAN then fill the title = Canada and top = 1 if code = ESP then fill the title = Spayol and top = 1 if code = USA then fill the title = United State and bottom = 1 so finally will be look like: CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin NOT NULL default '', `code` varchar(3) collate utf8_bin NOT NULL default '', `top` char(1) collate utf8_bin NOT NULL, `bottom` char(1) NOT NULL, PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, 'Canada', 'CAN', '1', ''), (2, 'Spayol', 'ESP', '1', ''), (3, 'United State', 'USA', '', '1'); PHP: your help and pointer / example appreciated and thanks in advance
Sorry, i am not php-mysql guru, i very new to php, then here i want to learn php, just please let me know that will be better as look like?
did you mean as: CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin NOT NULL default '', `code` varchar(3) collate utf8_bin NOT NULL default '', `top` char(1) collate utf8_bin NOT NULL default '', `bottom` char(1) NOT NULL default '', PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, 'Canada', 'CAN', '1', ''), (2, 'Spayol', 'ESP', '1', ''), (3, 'United State', 'USA', '', '1'); PHP: ??? Regards
Hi ActiveFrost, if i am not wrong by following to your instruction that should be: CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin default '', `code` varchar(3) collate utf8_bin, `top` char(1) collate utf8_bin default '', `bottom` char(1) default '', PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, '', 'CAN', '', ''), (2, '', 'ESP', '', ''), (3, '', 'USA', '', ''); PHP: E.g. if code = CAN then fill the title = Canada and top = 1 if code = ESP then fill the title = Spayol and top = 1 if code = USA then fill the title = United State and bottom = 1 so finally will be look like: CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin default '', `code` varchar(3) collate utf8_bin, `top` char(1) collate utf8_bin default '', `bottom` char(1) default '', PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ; INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, 'Canada', 'CAN', '1', ''), (2, 'Spayol', 'ESP', '1', ''), (3, 'United State', 'USA', '', '1'); PHP: then please let me know the php code to auto fill the empty fields?
I don't really understand why you are doing it this way. It sounds like you may end up with duplicate information. Can you give some background as to why it needs to be completed this way? Either way here is something that will do what you want CREATE TABLE IF NOT EXISTS `Countries` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin default '', `code` varchar(3) collate utf8_bin, `top` char(1) collate utf8_bin default '', `bottom` char(1) default '', PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `Crazy_Duplicate_Table` ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin default '', `code` varchar(3) collate utf8_bin, `top` char(1) collate utf8_bin default '', `bottom` char(1) default '', PRIMARY KEY (`num_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `Crazy_Duplicate_Table` (`title`, `code`, `top`, `bottom`) VALUES ('Canada', 'CAN', '1', ''), ('Spayol', 'ESP', '1', ''), ('United State', 'USA', '', '1'); INSERT INTO `Countries` (`title`, `code`, `top`, `bottom`) SELECT `Crazy_Duplicate_Table`.`title`,`Crazy_Duplicate_Table`.`code`,`Crazy_Duplicate_Table`.`top`,`Crazy_Duplicate_Table`.`bottom` FROM `Crazy_Duplicate_Table` WHERE `Crazy_Duplicate_Table`.`code` = 'CAN'; Code (markup):
Thanks so much for your attentions.. regarding to your code, then if i have code as: function downloadValueCountries() { $code_domain = substr($this->xml_file,0,strpos($this->xml_file,"/")); $code_file = substr($this->xml_file,strpos($this->xml_file,"/")); $fp = @fsockopen($code_domain, 80, $errno, $errstr, 10); if($fp) { $out = "GET ".$code_file." HTTP/1.1\r\n"; $out .= "Host: ".$code_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*code='(\w*)'\s*temperature='([\d\.]*)'/>}is"; preg_match_all($pattern,$buffer,$xml_count); array_shift($xml_count); for($i=0;$i<count($xml_count[0]);$i++) { $exchange_temperature[$xml_count[0][$i]] = $xml_count[1][$i]; } $conn = mysql_connect($this->mysql_host,$this->mysql_user,$this->mysql_pass); $rs = mysql_select_db($this->mysql_db,$conn); foreach($exchange_temperature as $code=>$temperature) { if((is_numeric($temperature)) && ($temperature != 0)) { $sql = "SELECT * FROM ".$this->mysql_table." WHERE code='".$code."'"; $rs = mysql_query($sql,$conn) or die(mysql_error()); if(mysql_num_rows($rs) > 0) { $sql = "UPDATE ".$this->mysql_table." SET temperature=".$temperature.",title=".$title.",bottom='".$bottom."',top='".$top."' WHERE code='".$code."'"; } else { $sql = "INSERT INTO ".$this->mysql_table." VALUES(NULL,'".$title."','".$code."',".$temperature.",'".$bottom."','".$top."')"; } $rs = mysql_query($sql,$conn) or die(mysql_error()); } } } } /* Create the Countries 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." ( `num_id` int(11) NOT NULL auto_increment, `title` varchar(32) collate utf8_bin NOT NULL default '', `code` char(3) NOT NULL default '', `temperature` float(15,8) NOT NULL, `bottom` char(1) collate utf8_bin NOT NULL default '',`top` char(1) collate utf8_bin NOT NULL default '', PRIMARY KEY(num_id) ) ENGINE=MyISAM"; $rs = mysql_query($sql,$conn) or die(mysql_error()); $sql = "INSERT INTO ".$this->mysql_table." VALUES(1,'','USA',22,'','')"; $rs = mysql_query($sql,$conn) or die(mysql_error()); $this->downloadValueCountries(); } PHP: what i should to to combine them and work properly? More help will be appreciated.
i also have idea using php conditional as: if($code == 'CAN') { $title = 'Canada'; $top = 1; } elseif($code == 'USA') { $title = 'United State'; $top = 1; } elseif($code == 'ESP') { $title = 'Spayol'; $bottom = 1; } Code (markup): but actually i don't know how to use it.
You could just use an sql select query $title = "SELECT title FROM Countries WHERE code='".$code."'"; PHP:
Hi MyVodaFone, do you mean if($code == 'CAN') { // $title = 'Canada'; $title = "SELECT title FROM Countries WHERE code='".$code."'"; $top = 1; } PHP: ?? Sorry, but if yes then where the value of $title? because in the table/query title is still empty and need to auto fill with php code Thank You
I thought you already had the countries in a database ? never mind, I'm confusing you sorry. INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, 'Canada', 'CAN', '1', ''), (2, 'Spayol', 'ESP', '1', ''), (3, 'United State', 'USA', '', '1'); Code (markup):
You quoted the complete table-query that i want to solve with auto fill using php code. below the existing table-query: INSERT INTO `Countries` (`num_id`, `title`, `code`, `top`, `bottom`) VALUES (1, '', 'CAN', '', ''), (2, '', 'ESP', '', ''), (3, '', 'USA', '', ''); PHP: then i looking for tips to fill it using php code, that able to combine with my php code at the previous. hope some one help me out. Many thanks..