Colton.Wagner Posted October 6, 2009 Share Posted October 6, 2009 Here is my problem I have a .xml file that is 15mb. I have to import all of the information into the mysql database. Here is the real problem the xml file is not constant how do I make it continue to insert the values even if some of them are empty? <?php session_start(); $_SESSION['parts'] = array(); ?> <html> <head> <link rel="shortcut icon" href="/favicon.ico" /> <title> title </title> </head> <body> <?php mysql_connect('', '', ''); mysql_select_db('products'); $parser = xml_parser_create(); function start($parser, $element_name, $element_attrs) { switch($element_name) { case "WAREHOUSELOC": // this is the start of a set of data $_SESSION['parts'] = array(); // create an empty set break; case "DESCRIPTION": $query = sprintf("INSERT INTO values (warehouseloc, trackingnumber, partnumber, name, imagename, primarycatalog primaryyear, primarypage, origincountry, weight, price, catagory1, catagory2, catagory3, catagory4, web_catagory1, web_catagory2, web_catagory3, web_catagory4, description) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',)", mysql_real_escape_string($_SESSION['parts']['WAREHOUSELOC']), mysql_real_escape_string($_SESSION['parts']['TRACKINGNUMBER']), mysql_real_escape_string($_SESSION['parts']['PARTNUMBER']), mysql_real_escape_string($_SESSION['parts']['NAME']), mysql_real_escape_string($_SESSION['parts']['IMAGENAME']), mysql_real_escape_string($_SESSION['parts']['PRIMARYCATALOG']), mysql_real_escape_string($_SESSION['parts']['PRIMARYYEAR']), mysql_real_escape_string($_SESSION['parts']['PRIMARYPAGE']), mysql_real_escape_string($_SESSION['parts']['ORIGINCOUNTRY']), mysql_real_escape_string($_SESSION['parts']['WEIGHT']), mysql_real_escape_string($_SESSION['parts']['PRICE']), mysql_real_escape_string($_SESSION['parts']['CATAGORY1']), mysql_real_escape_string($_SESSION['parts']['CATAGORY2']), mysql_real_escape_string($_SESSION['parts']['CATAGORY3']), mysql_real_escape_string($_SESSION['parts']['CATAGORY4']), mysql_real_escape_string($_SESSION['parts']['WEB_CATAGORY1']), mysql_real_escape_string($_SESSION['parts']['WEB_CATAGORY2']), mysql_real_escape_string($_SESSION['parts']['WEB_CATAGORY3']), mysql_real_escape_string($_SESSION['parts']['WEB_CATAGORY4']), mysql_real_escape_string($_SESSION['parts']['DESCRIPTION'])); mysql_query($query); break; default: } } function stop($parser, $element_name) { } function char($parser,$data) { $data = trim($data); if($data != ''){ if(!isset($_SESSION['parts']['WAREHOUSELOC'])){ $_SESSION['parts']['WAREHOUSELOC'] = $data; } elseif(!isset($_SESSION['parts']['TRACKINGNUMBER'])){ $_SESSION['parts']['TRACKINGNUMBER'] = $data; } elseif (!isset($_SESSION['parts']['PARTNUMBER'])){ $_SESSION['parts']['PARTNUMBER'] = $data; } elseif (!isset($_SESSION['parts']['NAME'])){ $_SESSION['parts']['NAME'] = $data; } elseif (!isset($_SESSION['parts']['IMAGENAME'])){ $_SESSION['parts']['IMAGENAME'] = $data; } elseif (!isset($_SESSION['parts']['PRIMARYCATALOG'])){ $_SESSION['parts']['PRIMARYCATALOG'] = $data; } elseif (!isset($_SESSION['parts']['PRIMARYYEAR'])){ $_SESSION['parts']['PRIMARYYEAR'] = $data; } elseif (!isset($_SESSION['parts']['PRIMARYPAGE'])){ $_SESSION['parts']['PRIMARYPAGE'] = $data; } elseif (!isset($_SESSION['parts']['ORIGINCOUNTRY'])){ $_SESSION['parts']['ORIGINCOUNTRY'] = $data; } elseif (!isset($_SESSION['parts']['WEIGHT'])){ $_SESSION['parts']['WEIGHT'] = $data; } elseif (!isset($_SESSION['parts']['PRICE'])){ $_SESSION['parts']['PRICE'] = $data; } elseif (!isset($_SESSION['parts']['CATAGORY1'])){ $_SESSION['parts']['CATAGORY1'] = $data; } elseif (!isset($_SESSION['parts']['CATAGORY2'])){ $_SESSION['parts']['CATAGORY2'] = $data; } elseif (!isset($_SESSION['parts']['CATAGORY3'])){ $_SESSION['parts']['CATAGORY3'] = $data; } elseif (!isset($_SESSION['parts']['CATAGORY4'])){ $_SESSION['parts']['CATAGORY4'] = $data; } elseif (!isset($_SESSION['parts']['WEB_CATAGORY1'])){ $_SESSION['parts']['WEB_CATAGORY1'] = $data; } elseif (!isset($_SESSION['parts']['WEB_CATAGORY2'])){ $_SESSION['parts']['WEB_CATAGORY2'] = $data; } elseif (!isset($_SESSION['parts']['WEB_CATAGORY3'])){ $_SESSION['parts']['WEB_CATAGORY3'] = $data; } elseif (!isset($_SESSION['parts']['WEB_CATAGORY4'])){ $_SESSION['parts']['WEB_CATAGORY4'] = $data; } elseif (!isset($_SESSION['parts']['DESCRIPTION'])){ $_SESSION['parts']['DESCRIPTION'] = $data; } } } xml_set_element_handler($parser, "start", "stop"); xml_set_character_data_handler($parser, "char"); $fp=fopen("xml.xml","r"); while ($data=fread($fp,4096)) { xml_parse($parser,$data,feof($fp)) or die (sprintf("XML Error: %s at line %d", xml_error_string(xml_get_error_code($parser)), xml_get_current_line_number($parser))); } xml_parser_free($parser); ?> </body> </html> Thanks in advanced for the help! I am new to Php programming and this is one of the friendliest community environments I have been in. Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/ Share on other sites More sharing options...
MadTechie Posted October 6, 2009 Share Posted October 6, 2009 I'm sorry i don't get the question ? PS s','%s','%s','%s',)", should be s','%s','%s','%s')", *remove the last , Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931245 Share on other sites More sharing options...
Colton.Wagner Posted October 6, 2009 Author Share Posted October 6, 2009 alright I took it out. Here is the problem hopefully I can explain it better. The xml file has 20 different fractions. Now not all of them are entered into the file. For instance say there was no recorded description for the object it would put this error message out. Notice: Undefined index: DESCRIPTION in on line 53 how do I make it ignore that sometime it wont be there. I already have the database read the ones that cause problems set to null. Any suggestions? Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931253 Share on other sites More sharing options...
Colton.Wagner Posted October 6, 2009 Author Share Posted October 6, 2009 Any suggestions? Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931818 Share on other sites More sharing options...
MadTechie Posted October 6, 2009 Share Posted October 6, 2009 a quick way would be to change your $_SESSION['parts'] = array() to $_SESSION['parts'] = array("WAREHOUSELOC"=>"", "TRACKINGNUMBER"=>"", "PARTNUMBER"=>"") ect etc etc also change if(!isset($_SESSION['parts']['WAREHOUSELOC'])){ $_SESSION['parts']['WAREHOUSELOC'] = $data; } elseif(!isset($_SESSION['parts']['TRACKINGNUMBER']) to if(empty($_SESSION['parts']['WAREHOUSELOC'])){ $_SESSION['parts']['WAREHOUSELOC'] = $data; } elseif(empty($_SESSION['parts']['TRACKINGNUMBER']) $_SESSION['parts']['TRACKINGNUMBER'] = $data; etc etc etc this will make a default value empty and non-empty values replace them Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931838 Share on other sites More sharing options...
Colton.Wagner Posted October 6, 2009 Author Share Posted October 6, 2009 Alright I will try that. Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931844 Share on other sites More sharing options...
Colton.Wagner Posted October 6, 2009 Author Share Posted October 6, 2009 After using all of your corrections it still pulled up the same error. Thank you however for cleaning up my code. Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931857 Share on other sites More sharing options...
MadTechie Posted October 6, 2009 Share Posted October 6, 2009 Do you have a an XML i can see Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-931968 Share on other sites More sharing options...
Colton.Wagner Posted October 7, 2009 Author Share Posted October 7, 2009 yes, I do it is a huge file so good luck. http://nicknackery.com/un-released/xml.xml Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932089 Share on other sites More sharing options...
MadTechie Posted October 7, 2009 Share Posted October 7, 2009 Hi Colton, After looking at your code i noticed that your code assumes the next value is the next one in the list, for example, your code would work with this <warehouseloc>0-00-00-00</warehouseloc> <trackingnumber>152130</trackingnumber> <partnumber>DU1277</partnumber> but with <warehouseloc>0-00-00-00</warehouseloc> <partnumber>DU1277</partnumber> <trackingnumber>152130</trackingnumber> partnumber & trackingnumber would be in the wrong fields in the database.. Normally i would write this as a class thus to avoid the unneeded use of sessions, you could also use global but i have left the sessions in.. and written is differently, It should work fine, any questions just let ask <?php session_start(); $_SESSION['parts'] = array(); ?> <html> <head> <link rel="shortcut icon" href="/favicon.ico" /> <title>title</title> </head> <body> <?php mysql_connect('', '', ''); mysql_select_db('products'); $parser = xml_parser_create(); xml_set_element_handler($parser, "start", "stop"); xml_set_character_data_handler($parser, "char"); $fp = fopen("xml.xml", "r"); while ($data = fread($fp, 4096)) { xml_parse($parser, $data, feof($fp)) or die(sprintf("XML Error: %s at line %d", xml_error_string(xml_get_error_code($parser)), xml_get_current_line_number($parser))); } xml_parser_free($parser); /** * Ooow a new tag, lets take a note. */ function start ($parser, $element_name, $element_attrs) { $_SESSION['TAG'] = $element_name; } /** * collection finshed, Let's add it to the database */ function stop ($parser, $element_name) { if ($element_name == "PART") { //Lucky the fieldname are the same as the elementnames soo //Build SQL statement from ONLY fields that have been set $fields = ""; $values = ""; foreach ($_SESSION['parts'] as $K => $V) { if (! empty($V)) { $K = mysql_real_escape_string($K); $V = mysql_real_escape_string($V); $fields .= "`$K`,"; $values .= "'$V',"; } } $fields = trim($fields, ","); //remove trailing , $values = trim($values, ","); //remove trailing , $query = "INSERT INTO values ($fields) VALUES ($values)"; //error report failed to insert (for debugging you may want to change to die mysql_query($query) or trigger_error("MySQL Failed: [$query]".mysql_error()."<br />\n",E_USER_NOTICE); $_SESSION['TAG'] = ""; $_SESSION['parts'] = array(); // create an empty set } } /** * Data Collection */ function char ($parser, $data) { $data = trim($data); if(empty($data)) return false; switch ($_SESSION['TAG']) { case 'WAREHOUSELOC': $_SESSION['parts']['WAREHOUSELOC'] = $data; break; case 'TRACKINGNUMBER': $_SESSION['parts']['TRACKINGNUMBER'] = $data; break; case 'PARTNUMBER': $_SESSION['parts']['PARTNUMBER'] = $data; break; case 'NAME': $_SESSION['parts']['NAME'] = $data; break; case 'IMAGENAME': $_SESSION['parts']['IMAGENAME'] = $data; break; case 'PRIMARYCATALOG': $_SESSION['parts']['PRIMARYCATALOG'] = $data; break; case 'PRIMARYYEAR': $_SESSION['parts']['PRIMARYYEAR'] = $data; break; case 'PRIMARYPAGE': $_SESSION['parts']['PRIMARYPAGE'] = $data; break; case 'ORIGINCOUNTRY': $_SESSION['parts']['ORIGINCOUNTRY'] = $data; break; case 'WEIGHT': $_SESSION['parts']['WEIGHT'] = $data; break; case 'PRICE': $_SESSION['parts']['PRICE'] = $data; break; case 'CATAGORY1': $_SESSION['parts']['CATAGORY1'] = $data; break; case 'CATAGORY2': $_SESSION['parts']['CATAGORY2'] = $data; break; case 'CATAGORY3': $_SESSION['parts']['CATAGORY3'] = $data; break; case 'CATAGORY4': $_SESSION['parts']['CATAGORY4'] = $data; break; case 'WEB_CATAGORY1': $_SESSION['parts']['WEB_CATAGORY1'] = $data; break; case 'WEB_CATAGORY2': $_SESSION['parts']['WEB_CATAGORY2'] = $data; break; case 'WEB_CATAGORY3': $_SESSION['parts']['WEB_CATAGORY3'] = $data; break; case 'WEB_CATAGORY4': $_SESSION['parts']['WEB_CATAGORY4'] = $data; break; case 'DESCRIPTION': $_SESSION['parts']['DESCRIPTION'] = $data; break; } } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932282 Share on other sites More sharing options...
Colton.Wagner Posted October 7, 2009 Author Share Posted October 7, 2009 Seems like I will be able to figure it out but here is what it pulled right away. Notice: MySQL Failed: [iNSERT INTO values (`WAREHOUSELOC`,`TRACKINGNUMBER`,`PARTNUMBER`,`NAME`,`IMAGENAME`,`PRIMARYCATALOG`,`PRIMARYYEAR`,`PRIMARYPAGE`,`ORIGINCOUNTRY`,`PRICE`,`DESCRIPTION`) VALUES ('0-00-00-00','152130','DU1277','DRAGON PROP','/oscommerce/catalog/images/DU1277.jpg','MAIN CATALOG','2006','Page 12','USA','3450.00','This beautiful piece stands approximately 8 feet tall. Foam filled latex with metal base. Ships via truck only')]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values (`WAREHOUSELOC`,`TRACKINGNUMBER`,`PARTNUMBER`,`NAME`,`IMAGENAME`,`PRIMARY' at line 1 in on line 54 Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932310 Share on other sites More sharing options...
MadTechie Posted October 7, 2009 Share Posted October 7, 2009 LOL oooops $query = "INSERT INTO values ($fields) VALUES ($values)"; should be $query = "INSERT INTO ($fields) VALUES ($values)"; Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932321 Share on other sites More sharing options...
Colton.Wagner Posted October 7, 2009 Author Share Posted October 7, 2009 Same ERROR: Notice: MySQL Failed: [iNSERT INTO (`WAREHOUSELOC`,`TRACKINGNUMBER`,`PARTNUMBER`,`NAME`,`IMAGENAME`,`PRIMARYCATALOG`,`PRIMARYYEAR`,`PRIMARYPAGE`,`ORIGINCOUNTRY`,`PRICE`,`DESCRIPTION`) VALUES ('0-00-00-00','152130','DU1277','DRAGON PROP','/oscommerce/catalog/images/DU1277.jpg','MAIN CATALOG','2006','Page 12','USA','3450.00','This beautiful piece stands approximately 8 feet tall. Foam filled latex with metal base. Ships via truck only')]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`WAREHOUSELOC`,`TRACKINGNUMBER`,`PARTNUMBER`,`NAME`,`IMAGENAME`,`PRIMARYCATALOG' at line 1 in on line 54 Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932337 Share on other sites More sharing options...
MadTechie Posted October 7, 2009 Share Posted October 7, 2009 Whats the name of the table your inserting into ? is it called values ? if change to $query = "INSERT INTO `values` ($fields) VALUES ($values)"; which is kinda confusing! (personally I would change the table name) Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932338 Share on other sites More sharing options...
Colton.Wagner Posted October 7, 2009 Author Share Posted October 7, 2009 It had nothing to do with you it was all my fault. I fixed it by changing the table name. Your a life saver. How do I mark this as solved? Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932344 Share on other sites More sharing options...
MadTechie Posted October 7, 2009 Share Posted October 7, 2009 `values` not 'values' Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932345 Share on other sites More sharing options...
MadTechie Posted October 7, 2009 Share Posted October 7, 2009 Bottom left "topic solved" Quote Link to comment https://forums.phpfreaks.com/topic/176637-solved-xml-parsing-error/#findComment-932355 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.