1,105,271 Community Members

using php insert XML data into mysql

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

LastMitch
Deleted Member
 
0
 

@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),
Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

LastMitch
Deleted Member
 
2
 

@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

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

LastMitch
Deleted Member
 
2
 

@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

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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...

LastMitch
Deleted Member
 
2
 

@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.

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

LastMitch
Deleted Member
 
2
 

@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?

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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),
LastMitch
Deleted Member
 
2
 

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?

Member Avatar
ddymacek
Posting Whiz
324 posts since Jun 2010
Reputation Points: 15 [?]
Q&As Helped to Solve: 68 [?]
Skill Endorsements: 0 [?]
 
0
 

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')

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

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
Deleted Member
 
2
 

@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

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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" />
Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 
<?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.

Member Avatar
ismael ahm@d
Junior Poster in Training
61 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
diafol
Where are my eyes?
12,940 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,845 [?]
Skill Endorsements: 90 [?]
Moderator
Featured
Sponsor
 
0
 

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;
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article