how to insert xml file in to mysql table

Question

I know this question is already asked by many people and I already follow some guide but sorry that till now I can't solve my problem. I have many XML files which are saved on local directory on my FTP server. Now I need to read every XML file line by line and put it on my MySQL database which I have already created. Here I attach my code, which is not run in my server and in the database I didn't see anything.

// Read filenames in current directory looking for XML files

$dir = "/path/to/filename";
if (is_dir($dir)) {
  if ($dh = opendir($dir)) {
    while (($file = readdir($dh)) !== false) {
      if (($file !== '.') && ($file !== '..') ) {
    $xml = simplexml_load_file($dir.$file);  
        //$RGSostituzione = $xml->attributes()->Sostituzione;

    }
  }
  closedir($dh);
  }
}

// Loop through each XML file in the current directory
  $query = array();

    foreach($dir as $filename) {

        $mess =simplexml_load_file("/path/to/filename".$filename);

        $time = mysql_real_escape_string($mess->time);
        $Id = mysql_real_escape_string($mess->Id);
        $From = mysql_real_escape_string($mess->From);
        $Subject = mysql_real_escape_string($mess->Subject);
        $address = mysql_real_escape_string($mess->address);
        $Body = mysql_real_escape_string($mess->Body);


        $query[] = "('$time', '$Id', '$Subject','$address','$Body')";


    } if(!empty($query)) {
        $query = implode(",",$query);
mysql_query("INSERT INTO xml (time, Id, From, Subject,address,Body)VALUES {$query}") or die("Query 2 non valida: " . mysql_error()); 

}
//close connection 
mysql_close($conn);

?>

Edited _: my last request is from this given code-

 // Loop through each XML file in the current directory
      $query = array();

        foreach($dir as $filename) {

            $mess =simplexml_load_file("/path/to/filename".$filename);

after $mess i want to write a if statemnet inside the foreach loop ..if the mess dont read the path print a error? how should i call is statement dont get.if anyone can help me would be greate.

hi ,there is no one who can solved my this problem?till now i am hanging on the same problem. — Lutfun, Oct 17 '13 at 14:45
Try using `date()` http://php.net/manual/en/function.date.php — Harry, Oct 18 '13 at 09:27
hi i already use this but not working cause if you follow my query from my 1st question u can understand my problem is different.i mean i want to read my xml output line by line then want to put it on my mysql table.but the problem is how should i change my time query for put in mysql databse where my table is name (data).where i want ot insert my time xml which u saw given line. — Lutfun, Oct 18 '13 at 09:38
Using `date()` you can specify the format. Then you can use the `string` `date()` returns as the variable you use to insert into your MySQL database. — Harry, Oct 18 '13 at 09:46
ok but can u kindly explain or write code for me details cause i am not very expert in php and thats why i get confused.plz as u saw i write my query in my question and where i should write is date (). — Lutfun, Oct 18 '13 at 09:53
I'm sure you can figure it out. Read the PHP documentation and you will learn how to specify your format. Try searching SO for answers on this subject. http://stackoverflow.com/questions/2487921/convert-date-format-yyyy-mm-dd-dd-mm-yyyy — Harry, Oct 18 '13 at 09:56
hey ,morph i know may be if i use more time i could figure it out without nay help but sorry to say that i realy need to solve this soon and thats why i ask you for a help if possible.cause i hang out from last some days.then when i am see not able to solve alone and ask for help.if possible tell me — Lutfun, Oct 18 '13 at 10:11

score 0 · Answer 1 · edited Oct 18 '13 at 09:23

0

Can you be a bit more specific about the error you are getting?

The possibilities I see are,

1) Time can be is a string and you don't have it in quotes and the $query variable is blank. There is no value in $query variable which you passed in mysql_query 2)MySQL connection problem 3)MySQL/PHP running out of execution time

try adding $time in single Quote.

Again, It's not a good Idea to run MySQL query inside a loop. Create a Query inside the loop and run it after completing the loop. It should look almost like

Edited: Use this code and try. don't forget replace the path to the folder/files

error_reporting(E_ALL); 
ini_set('error_reporting', E_ALL);    
// Read filenames in current directory looking for XML files

    $dir = "/path/to/filename";
   $file_arr = array();
    if (is_dir($dir)) {
      if ($dh = opendir($dir)) {
        while (($file = readdir($dh)) !== false) {
          if (($file !== '.') && ($file !== '..') ) {
         $file_arr[] = $file;
        }
      }
      closedir($dh);
      }
    }

            $query = array();

            foreach($file_arr as $filename) {

                $mess =simplexml_load_file("/path/to/file/".$filename);

                $time = mysql_real_escape_string($mess->time);
                $Id = mysql_real_escape_string($mess->Id);
                $From = mysql_real_escape_string($mess->From);
                $Subject = mysql_real_escape_string($mess->Subject);
                $address = mysql_real_escape_string($mess->address);
                $Body = mysql_real_escape_string($mess->Body);


                $query[] = "('$time', '$Id', '$Subject','$address','$Body')";


            } if(!empty($query)) {
                $query = implode(",",$query);
        mysql_query("INSERT INTO xml (time, Id, From, Subject,address,Body)VALUES {$query}") or die("Query 2 non valida: " . mysql_error()); 

        }

edited Oct 18 '13 at 09:23

zessx

68,042
28
135
158

answered Oct 17 '13 at 09:35

Pradeesh Kumar

223
2
14

hi ,the problem is now i can see a blank page without any error that means my query does not work. what i get now my problem is just this part-when i try to loop my xml file in directory it does not works and insert in database that is not wokring also. now i chnage some code and again i insert here.so may be you got my problem.i follow with your code but it des not works – Lutfun Oct 17 '13 at 10:01
// Read filenames in current directory looking for XML files $dir = "/my file/name; if (is_dir($dir)) { if ($dh = opendir($dir)) { while (($file = readdir($dh)) !== false) { if (($file !== '.') && ($file !== '..') ) { $xml = simplexml_load_file($dir. $file); //$RGSostituzione = $xml->attributes()->Sostituzione; echo "letto
"; } } closedir($dh); } } – Lutfun Oct 17 '13 at 10:07
in set of this code can you write for me the query for loop the xml file and then put it on mysql database. cause i cant see any change. – Lutfun Oct 17 '13 at 10:09
1

Add These two lines on top of your code **error_reporting(E_ALL);** **ini_set('error_reporting', E_ALL);** and check what's the error you are getting – Pradeesh Kumar Oct 17 '13 at 10:55
hi , i try but there is no error i get it from this.i think i am not clear about my explanation. now the problem is i am able to read my xml file which i store in a one folder on my ftp server but the problem is in foreach -i mean now i wnat to to parse my xml file and put it on database.kindly solve the problem. – Lutfun Oct 17 '13 at 10:59
simplexml_load_file should contain the path of the folder also. it should be like ***simplexml_load_file("/path/to/file/".$filename);*** – Pradeesh Kumar Oct 17 '13 at 11:02
ok but do you mean here $query = array(); foreach($file_arr as $filename) { / $mess =simplexml_load_file("/path/to/file/". $filename); – Lutfun Oct 17 '13 at 12:11
I added that because calling a mysql_query inside the loop is costly in terms of resources. That means if you have 100 files, the query will run 100 times. if you make a large single insert query and call it outside the loop, it will save memory and time to execute. I have edited the answer to work according to your conditions. – Pradeesh Kumar Oct 17 '13 at 12:43
ok but in this case how should i solve my problem.i mean do you thing my new code is corect cause base on my work and follow your code i edeited but not sure it is ok or not? cause i did,nt saw any chnage on my database? – Lutfun Oct 17 '13 at 12:45
sorry i am not very expert and and i try to find a solution for my problem .if you can write for me the code it will be a kind help.foreach($file_arr as $filename) { here is the problem from your edited solution cause i dont have any file_array now. – Lutfun Oct 17 '13 at 12:47
I have edited the above answer. try using that. if it's still not working, print_r $query right after the loop and copy paste the result here. – Pradeesh Kumar Oct 17 '13 at 12:51
ok but sorry just follow this line foreach($file_arr as $filename) { $ file_arr is correct do you mean? – Lutfun Oct 17 '13 at 12:56
just use your edited code.their is no error i found but i dont have any result in my database? – Lutfun Oct 17 '13 at 12:59
Use the code I have just added. Logic is: 1) Scan the dir for files and make an array 2)Load files from array and create sql 3) Run query – Pradeesh Kumar Oct 17 '13 at 13:01
i already add your code and run it but in database i dont have any query or table which is created from my xml file.and also when i run my php page there is no change. – Lutfun Oct 17 '13 at 13:23
Insert a print_r after each loop. like, print_r($file_arr) after the while loop and print_r $query after the foreach loop. copy paste the result. – Pradeesh Kumar Oct 18 '13 at 04:45
hi pradeesh, now the test from the directory is ok but insert into my database it has little problem cause i alreday have one table which we create and the evrey xml line we have to put it the fixed table which we have .so in this case i have problem for this line my xml time is -30/09/2013 15.15.13 and the sql table should be 0000-00-00 00:00:00 something like this..now can u giv eme how could i modify my sql query for insert my xml into sql table – Lutfun Oct 18 '13 at 08:05

how to insert xml file in to mysql table

1 Answers1