using php insert XML data into mysql

Question

ismael ahm@d 0 Newbie Poster

12 Years Ago

Hi all friends,

I am trying to insert XML data using DOM, i completed mostly but little bit left, issue in the <field></field> the data between that not inserted in db, please consider the below mentioned XML file...

<?xml version="1.0" encoding="UTF-8"?>
<form name="mc1.dpp" application-name="mc1.dpp" application-id="34" document-id="1342" producer="Penvision Formidable Application Server" export-time="2012-11-13T12:04:32+04:00" first-page="86.658.2.129" last-page="86.658.2.129">
  <submit email="123@abc.com"/>

  <field type="textfield" name="learningpermitno" normalized-score="1" resemblance-score="0.239">LOO4</field>
  <field type="textfield" name="applicantname" normalized-score="1" resemblance-score="0.627">Kaifi</field>

<userinfo>
<penid>AJX-AAQ-JFN-5Z</penid>
<email>123@abc.com</email>
<phone>123456</phone>
<firststroke>1352793331</firststroke>
</userinfo>

</form>

PHP Code:

<?php
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents('xmlfile.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx


foreach ($oDOM->getElementsByTagName('form') as $oBookNode)
{
    $sSQL = sprintf(
        "INSERT INTO timestamp_table (learningpermit, applicantname, penid, email, phone,firststroke) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
        mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('penid')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('email')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('phone')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('firststroke')->item(0)->nodeValue)
    );
    $rResult = mysql_query($sSQL);
?>

--
Kind Regards

mysql php xml

4 Contributors
20 Replies
2K Views
1 Day Discussion Span
Latest Post 12 Years Ago Latest Post by ismael ahm@d

LastMitch

12 Years Ago

@ismael ahm@d

I have tried both of method even used of field name to insert in db but still db not updated.

You can't echo out a <field> tags. Who taught you how to echo out a <field> tags? It's like say you echo out a <html> tag. You can only echo out the name of the variable. Do you understand now.

I assume you can get these from the database:

mysql_real_escape_string($oBookNode->getElementsByTagName('penid')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('email')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('phone')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('firststroke')->item(0)->nodeValue)

Since the issue is not the database then it's the <field> tags.

Try to used <input> tags

Instead of this:

<field type="textfield" name="learningpermitno" normalized-score="1" resemblance-score="0.239">LOO4</field>
<field type="textfield" name="applicantname" normalized-score="1" resemblance-score="0.627">Kaifi</field>

Try this:

<p>LOO4<input type="text" name="learningpermitno"></p>
<p>Kaifi<input type="text" name="applicantname"></p>

If you still want to used <field> tags. Then my suggestion read this it's the correct way of using it:

http://docs.joomla.org/SQL_form_field_type

But base on the code you provided it should be <input> tags not <field> tags

Edited 12 Years Ago by LastMitch because: grammer

LastMitch

12 Years Ago

@ismael ahm@d

So please if you have a solution for this so assist me.

If you still want to used <field> tags. Then read the link:

http://docs.joomla.org/SQL_form_field_type

It's the correct way of using it.

or you can used <input> tags

Edited 12 Years Ago by LastMitch because: grammer

LastMitch

12 Years Ago

@ismael ahm@d

its mean that should i recode my xml file? if yes, then i have hundreds of files and each file have 100 of lines code in which mostly i getting data between <field> tags

Did you read it correctly? You don't need to recode xml file?

All you need to add is a query.

<field name="myfield" type="sql" default="10" label="Select an article" query="SELECT id, title FROM #__content" key_field="id" value_field="title" />

In order to input the data by using form , you need to used <input> tags not <field> tags.

Edited 12 Years Ago by LastMitch because: grammer

LastMitch

12 Years Ago

@ismael ahm@d

In order to input the data in to the database by using form , you need to used <input> tags not <field> tags.

Then you can used <field> tags to fetch the xml data.

Does that make sense?

Edited 12 Years Ago by LastMitch because: grammer

LastMitch

12 Years Ago

so how i interchange the given <field> tag of xml into <input> in php file where i wrote:

This is input tags

<input type="text" name="learningpermitno">
<input type="text" name="applicantname">

When you submit that form learningpermitno & applicantname will be in the database.

On here it should look like this:

$sSQL = sprintf(
"INSERT INTO timestamp_table (learningpermit, applicantname, penid, email, phone,firststroke) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string($oBookNode->getElementsByTagName('learningpermitno')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('applicantname')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('penid')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('email')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('phone')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('firststroke')->item(0)->nodeValue)
);

Then on your <field> tags:

<field name="myfield" type="sql" default="10" label="Select an article" query="SELECT id, title FROM #__content" key_field="id" value_field="title" />

You need a query to select those xml from your database.

Does that make sense?

Edited 12 Years Ago by LastMitch because: grammer

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

LastMitch · Answer 1 · 2012-11-15T19:17:52+00:00

@ismael ahm@d

i completed mostly but little bit left, issue in the <field></field> the data between that not inserted in db, please consider the below mentioned XML file...

It's bit strange that you put 2 of these fields:

mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),

on here you have 2 <field> and the name is learningpermitno & applicantname:

<field type="textfield" name="learningpermitno" normalized-score="1" resemblance-score="0.239">LOO4</field>
<field type="textfield" name="applicantname" normalized-score="1" resemblance-score="0.627">Kaifi</field>

My question is why you put field in here:

mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),

it should be the name learningpermitno & applicantname instead:

mysql_real_escape_string($oBookNode->getElementsByTagName('learningpermitno')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('applicantname')->item(0)->nodeValue),

ismael ahm@d 0 Newbie Poster · Answer 2 · 2012-11-16T09:51:04+00:00

It's bit strange that you put 2 of these fields:

Thanks for your comments,
I have tried both of method even used of field name to insert in db but still db not updated,

    mysql_real_escape_string($oBookNode->getElementsByTagName('learningpermitno')->item(0)->nodeValue),
    mysql_real_escape_string($oBookNode->getElementsByTagName('applicantname')->item(0)->nodeValue),

--
Kind Regards

ismael ahm@d 0 Newbie Poster · Answer 3 · 2012-11-16T20:40:36+00:00

Dear,

Who taught you how to echo out a <field> tags?

the XML file which i am getting not formated or written by me, i got that file from other source, and now i am just want that insert given xml data into db, in which as you know that data between below <userinfo> tags inserted successfully

<userinfo>
<penid>AJX-AAQ-JFN-5Z</penid>
<email>123@abc.com</email>
<phone>123456</phone>
<firststroke>1352793331</firststroke>
</userinfo>

but now there is problem in <field> tags.
So please if you have a solution for this so assist me.
--
Kind Regards

ismael ahm@d 0 Newbie Poster · Answer 4 · 2012-11-16T20:52:41+00:00

Dear

http://docs.joomla.org/SQL_form_field_type

its mean that should i recode my xml file? if yes, then i have hundreds of files and each file have 100 of lines code in which mostly i getting data between <field> tags

Thank you...

ismael ahm@d 0 Newbie Poster · Answer 5 · 2012-11-16T21:07:10+00:00

Really sorry :( because first time using xml, and not understand on given article

can you please tell me where should i code that query in php file if wanna store the data of xml in db.

ismael ahm@d 0 Newbie Poster · Answer 6 · 2012-11-16T21:13:15+00:00

so how i interchange the given <field> tag of xml into <input> in php file where i wrote:

 $sSQL = sprintf(
"INSERT INTO timestamp_table (learningpermit, applicantname, penid, email, phone,firststroke) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),

ddymacek 15 Posting Whiz · Answer 7 · 2012-11-16T21:27:17+00:00

What values are you not getting?
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(0)->nodeValue),
to get the value of the second 'field' tag change item to (1)
mysql_real_escape_string($oBookNode->getElementsByTagName('field')->item(1)->nodeValue),
ran on my machine: output =:
sql = INSERT INTO timestamp_table (learningpermit, applicantname, penid, email, phone,firststroke) VALUES ('LOO4', 'Kaifi', 'AJX-AAQ-JFN-5Z', '123@abc.com', '123456', '1352793331')

ismael ahm@d 0 Newbie Poster · Answer 8 · 2012-11-16T21:28:57+00:00

I accept that i am not getting about xml and you doing best to sort out, but why you are not make it easy for me as you know that i am playing first time with xml, even i have sent you my all code in the begining of post.

Thanks and kind Regards

diafol · Answer 9 · 2012-11-16T21:31:38+00:00

The <field> tags are valid XML. In fact you can have any <tagname> in XML. The name is immaterial. It's just that you need to reference tags correctly.

LastMitch · Answer 10 · 2012-11-16T21:33:23+00:00

@ismael ahm@d

why you are not make it easy for me as you know that i am playing first time with xml

I already gave you the outline from my previous thread

ismael ahm@d 0 Newbie Poster · Answer 11 · 2012-11-16T21:39:36+00:00

but I think you didn't clear about below mentioned code that where should i place in php file while insertion query run successfully.

    <field name="myfield" type="sql" default="10" label="Select an article" query="SELECT id, title FROM #__content" key_field="id" value_field="title" />

diafol · Answer 12 · 2012-11-16T21:41:21+00:00

<?php
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents('xmlfile.xml')); #See: http://msdn.microsoft.com/en-us/library/ms762271(VS.85).aspx
foreach ($oDOM->getElementsByTagName('form') as $oBookNode){
    echo $oBookNode->getElementsByTagName('field')->item(0)->nodeValue . "<br />";
    echo $oBookNode->getElementsByTagName('field')->item(1)->nodeValue . "<br />";
    echo $oBookNode->getElementsByTagName('penid')->item(0)->nodeValue . "<br />";
    echo $oBookNode->getElementsByTagName('email')->item(0)->nodeValue . "<br />";
    echo $oBookNode->getElementsByTagName('phone')->item(0)->nodeValue . "<br />";
    echo $oBookNode->getElementsByTagName('firststroke')->item(0)->nodeValue . "<br />";
}
?>

This modified code works fine for me. The fact that you had item(0) twice for 'field' meant that you were retrieving the same data.

If it doesn't work for you, have a look at your SQL.

In addition, don't use mysql if possible - mysqli and PDO have parameterized queries, which means that you can do away with escaping functions.

ddymacek seems to be able to get it to work too.

ismael ahm@d 0 Newbie Poster · Answer 13 · 2012-11-16T22:09:55+00:00

Thanks all of you!!!

Dear diafol,
yours advice for item(o),(1) exactly right, even i have added some extra tages between <userinfo> example: <penid>AJX-AAQ</penid>, <penid>AJX-AAQ-JFN-5Z</penid> and changed its item value accordingly.
but in the <field> still no luck even i have changed item values, even i put <fields> under <userinfo>.... please advice for better solution

diafol · Answer 14 · 2012-11-16T23:46:50+00:00

yours advice for item(o),(1) exactly right,

I think you'll find ddymacek first spotted it.
I can't see why your <field> value won't work. Try running the code I supplied to see if it echoes out the field data. Or alternatively, echo the SQL as ddymacek has done:

echo $sSQL;

ismael ahm@d 0 Newbie Poster · Answer 15 · 2012-11-17T11:09:06+00:00

Done it... :)

Thanks for your time that you consider that post.

Kind Regards