We help IT Professionals succeed at work.

Reading Recursive XML with PHP and inserting mysql

phparmy
phparmy asked
on
Medium Priority
443 Views
Last Modified: 2012-05-06
Hello

i have a xml document like below
<category_list>
<category id="1">
       <title>Category 1</title>
       <category id="2">
             <title>Category 1.1</title>
       </category>
       <category id="3">
              <title>Category 1.2</title>
              <category id="4">
                  <title>Category 1.2.1</title>
              </category>
       </category>
</category>
</category_list>

i want to parse this xml and write it to mysql. in mysql i have category table and have two columns

category_id,parent_id,category_name

after parsing and inserting values two table result will be like below

category_id,parent_id,category_name
1,                   0,                  Category 1
2,                   1,                  Category 1.1
3,                   1,                  Category 1.2
4,                   3,                  Category 1.2.1

Parsing method is not important but if it be Simple_xml or DOM it will be nice.

Thanks Please Help


Comment
Watch Question

Top Expert 2008
Commented:
Try this:
$xml = <<<XML
<category_list>
<category id="1">
       <title>Category 1</title>
       <category id="2">
             <title>Category 1.1</title>
       </category>
       <category id="3">
              <title>Category 1.2</title>
              <category id="4">
                  <title>Category 1.2.1</title>
              </category>
       </category>
</category>
</category_list>
XML;
 
function get_cat($node,$parent=0) {
  echo "insert into category set 
    category_id={$node['id']}, 
    parent_id=$parent, 
    category_name='{$node->title}'" . '<br />';
  $parent = $node['id'];
  foreach($node->category as $cat) 
    get_cat($cat,$parent); # recursive
}
 
$p = new SimpleXMLElement($xml);
get_cat($p->category);

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Top Expert 2008

Commented:
It will of course only echo the insert statements, if it looks good, replace "echo" with mysql_query() and remove the '<br />'.

Author

Commented:
Thanks but its not work for below if there is Category 2, Category 3 its only works for Category 1

$xml = <<<XML
<category_list>
<category id="1">
       <title>Category 1</title>
       <category id="2">
             <title>Category 1.1</title>
       </category>
       <category id="3">
              <title>Category 1.2</title>
              <category id="4">
                  <title>Category 1.2.1</title>
              </category>
       </category>
       <category id="5">
                   <title>Category 1.3</title>
       </category>
</category>
<category id="6">
      <title>Category 2</title>
</category>
<category id="7">
      <title>Category 3</title>
</category>
</category_list>
XML;
Top Expert 2008
Commented:
Then you have multiple nodes with parent=0 . Call it from a loop:

$p = new SimpleXMLElement($xml);
foreach($p->category as $node)  
  get_cat($node);
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.