[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

retrieving hierarchial data from xml and saving to database nested set model

Posted on 2009-02-12
3
Medium Priority
?
932 Views
Last Modified: 2013-12-12
hello

i am using nested set model for saving hierarchial data  to mysql.
how is nested set model you are creating table and saving left and right number for each item. Sample one in attachment.

CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);

INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

i have a xml file

$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;

i want to parse it and save to database method which i explained above. (SimpleXML or DOM method for parsing)

Thanks for your helps
hierarchical-data-3.png
hierarchical-data-4.png
0
Comment
Question by:phparmy
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 2000 total points
ID: 23638864
Try this:
$counter = 1;
function set_left_right($node) {
  global $counter;
  $node->left = $counter++;
  foreach($node->category as $cat) 
    set_left_right($cat); # recursive
  $node->right = $counter++;
}
function write_nodes($node) {
  echo "insert into nested_category set 
    category_id={$node['id']}, 
    name='{$node->title}'
    lft=$node->left, 
    rgt=$node->right" . '<br />';
  $parent = $node['id'];
  foreach($node->category as $cat) 
    write_nodes($cat,$parent); # recursive
}
$p = new SimpleXMLElement($xml);
foreach($p->category as $node)  
  set_left_right($node);
foreach($p->category as $node)  
  write_nodes($node);

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 23638873
You can delete line 15 and remove $parent parameter in line 17.
0
 

Author Comment

by:phparmy
ID: 23657485
Thank you. It helped me very much.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
Create a Windows 10 custom Image with custom task bar and custom start menu using XML for deployment.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 13 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question