Problem inserting XML parsed data in MySQL tabel

Question

I am trying to parse XML using PHP DOM and then insert this data in MySQL tables, i am using the following code for this:

<?php
$xmlDoc = new DOMDocument();
$xmlDoc->load("testrtap11.xml");
mysql_select_db("zeeshan_database1", $con);

$x=$xmlDoc->getElementsByTagName('RECORD');
$z=$xmlDoc->getElementsByTagName('TITLE');
$w=$xmlDoc->getElementsByTagName('PRIMARY_AUTHOR');
$y=$xmlDoc->getElementsByTagName('JOURNAL_CONFERENCE');

for($i=0; $i<=$x->length-1; $i++)
{
    $sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES('$z->item($i)->nodeValue','$w->item($i)->nodeValue','$y->item($i)->nodeValue')";

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "1 record added";
}

mysql_close($con)
?>

The data being entered is not correct. i even tried storing my xml parse values in a variable and then using that variable to insert data, even that does not works. I used the variable like this:

for($i=0; $i<=$x->length-1; $i++)
{
    $zz=$z->item($i)->nodeValue);
    $ww=$w->item($i)->nodeValue);
    $yy=$y->item($i)->nodeValue);
    $sql="INSERT INTO Persons (FirstName, LastName, Age) VALUES('$zz','$ww','$yy')";

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
    echo "1 record added";
}

My xml looks like this:

<RTAP>
  <RECORD>
     <TITLE>               </TITLE>
     <PRIMARY_AUTHOR>      </PRIMARY_AUTHOR>
     <JOURNAL_CONFERENCE>  </JOURNAL_CONFERENCE>
  </RECORD>
</RTAP>

Kindly help me, what should I do in this

Best Zeeshan

Can you please include the data that you were expecting to be stored and the data that was actually stored? — Lawrence Barsanti
– Lawrence Barsanti, Commented Jun 18, 2009 at 16:29

Greg · Accepted Answer · 2009-06-18 18:53:35Z

2

Try something like this:

foreach ($xmlDoc->RECORD as $record)
{
    $sql = 'INSERT INTO Persons (FirstName, LastName, Age) VALUES('
        . '"' . mysql_escape_string($record->TITLE->nodeValue) . '", '
        . '"' . mysql_escape_string($record->PRIMARY_AUTHOR->nodeValue) . '", '
        . '"' . mysql_escape_string($record->JOURNAL_CONFERENCE->nodeValue) . '")'

    if (!mysql_query($sql,$con))
        die('Error: ' . mysql_error());

    echo "1 record added";
}

You don't actually say what's wrong with the data that's inserted...

edited Jun 18, 2009 at 18:53

answered Jun 18, 2009 at 16:32

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zeeshan Rang Over a year ago

I tried using the select command: $result = mysql_query("SELECT * FROM Persons"); while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br />"; } And this is the output i got: (0)->nodeValue (0)->nodeValue (1)->nodeValue (1)->nodeValue (2)->nodeValue (2)->nodeValue (3)->nodeValue (3)->nodeValue

Zeeshan Rang Over a year ago

Greg what is the $record that you have mentioned in your code? is it like $z->item($i)->nodeValue that i am using in mine code. But the thing is your statement is not working for some reason-- i cant not figure out..

Zeeshan Rang Over a year ago

Hey greg thanks, your code did work out after just a few changes in it. thanks alot. Zeeshan

Greg Over a year ago

Oops, I'd made a complete mess of the quotes

Zeeshan Rang · Accepted Answer · 2009-06-18 17:28:34Z

0

I tried using the select command on my original code that i mentioned above: $result = mysql_query("SELECT * FROM Persons"); while($row = mysql_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "
"; }

And this is the output i got: (0)->nodeValue (0)->nodeValue (1)->nodeValue (1)->nodeValue (2)->nodeValue (2)->nodeValue (3)->nodeValue (3)->nodeValue

answered Jun 18, 2009 at 17:28

Zeeshan Rang

20k29 gold badges75 silver badges104 bronze badges

Collectives™ on Stack Overflow

Problem inserting XML parsed data in MySQL tabel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related