Looping through an xml object and inserting data into a table with parent child relationship

Question

I have an xml object. I could loop through it this way to get values of following of its properties. I require to store these into database in a parent child relation.

foreach ($xml->entry as $status) {
      echo $status->author->name.''$status->content.''.$status->id; }

Depth of the tree is not finite. Also An example query to retrieve the data in order (parent-child...) once inserted would be great.

EDIT Depth of tree could be considered as three. This is an example of json returned from twitter.

     <script type="text/javascript"> var test_json = {\"id\":\"tag:search.twitter.com,2005:search\\/#DIYSe_F\",\"link\":[{\"@attributes\":{\"type\":\"text\\/html\",\"href\":\"http:\\/\\/search.twitter.com\\/search?q=%23DIYSe_F\",\"rel\":\"alternate\"}},{\"@attributes\":{\"type\":\"application\\/atom+xml\",\"href\":\"http:\\/\\/search.twitter.com\\/search.atom?q=%23DIYSe_F&rpp=100\",\"rel\":\"self\"}},{\"@attributes\":{\"type\":\"application\\/opensearchdescription+xml\",\"href\":\"http:\\/\\/search.twitter.com\\/opensearch.xml\",\"rel\":\"search\"}},{\"@attributes\":{\"type\":\"application\\/atom+xml\",\"href\":\"http:\\/\\/search.twitter.com\\/search.atom?q=%23DIYSe_F&rpp=100&since_id=7750301532557312\",\"rel\":\"refresh\"}}],\"title\":\"#DIYSe_F - Twitter Search\",\"updated\":\"2010-11-24T22:20:44Z\",\"entry\":[{\"id\":\"tag:search.twitter.com,2005:7559269595488256\",\"published\":\"2010-11-24T22:20:44Z\",\"link\":[{\"@attributes\":{\"type\":\"text\\/html\",\"href\":\"http:\\/\\/twitter.com\\/_smir\\/statuses\\/7559269595488256\",\"rel\":\"alternate\"}},{\"@attributes\":{\"type\":\"image\\/png\",\"href\":\"http:\\/\\/s.twimg.com\\/a\\/1289849896\\/images\\/default_profile_5_normal.png\",\"rel\":
            \":\"image\"}}],\"title\":\"#DIYse_F HIE_STRUCT: HIERARCHICAL STRUCTURE: hierarchical structure to display \\nmessages of Functions and Qualities
    types\",\"content\":\"<a href=\\\"http:\\/\\/search.twitter.com\\/search?q=%23DIYse_F\\\" onclick=\\\"pageTracker._setCustomVar(2, \'result_type\', \'recent\', 3);pageTracker._trackPageview(\'\\/intra\\/hashtag\\/#DIYse_F\');\\\"><b>#DIYse_F<\\/b><\\/a> HIE_STRUCT: HIERARCHICAL STRUCTURE: hierarchical structure to display \\nmessages of Functions and Qualities types\",\"updated\":\"2010-11-24T22:20:44Z\",\"author\":{\"name\":\"_smir (Smeer)\",\"uri\":\"http:\\/\\/twitter.com\\/_smir\"}},{\"id\":\"tag:search.twitter.com,2005:7552659368189952\",\"published\":\"2010-11-24T21:54:28Z\",\"link\":[{\"@attributes\":{\"type\":\"text\\/html\",\"href\":\"http:\\/\\/twitter.com\\/_smir\\/statuses\\/7552659368189952\",\"rel\":\"alternate\"}},{\"@attributes\":{\"type\":\"image\\/png\",\"href\":\"http:\\/\\/s.twimg.com\\/a\\/1289849896\\/images\\/default_profile_5_normal.png\",\"rel\":\"image\"}}],\"title\":\"#DIYse_F SEND_MSG: users can send messages of four types i.e.  \\n\\nFunction,Quality,Solution, and delivery\",\"content\":\"<a href=\\\"http:\\/\\/search.twitter.com\\/search?q=%23DIYse_F\\\" onclick=\\\"pageTracker._setCustomVar(2, \'result_type\', \'recent\', 3);pageTracker._trackPageview(\'\\/intra
    \\/hashtag\\/#DIYse_F\');\\\"><b>#DIYse_F<\\/b><\\/a> SEND_MSG: users can send messages of four types i.e.  \\n\\nFunction,Quality,Solution, and delivery\",\"updated\":\"2010-11-24T21:54:28Z\",\"author\":{\"name\":\"_smir 
(Smeer)\",\"uri\":\"http:\\/\\/twitter.com\\/_smir\"}},{\"id\":\"tag:search.twitter.com,2005:7548895705956352\",\"published\":\"2010-11-24T21:39:31Z\",\"link\":[{\"@attributes\":{\"type\":\"text\\/html\",\"href\":\"http:\\/\\/twitter.com\\/Babar_Shahzad\\/statuses\\/7548895705956352\",\"rel\":\"alternate\"}},{\"@attributes\":{\"type\":\"image\\/png\",\"href\":\"http:\\/\\/a1.twimg.com\\/profile_images\\/1090185625\\/29465_391454998679_533808679_3864564_6071800_n_normal.jpg\",\"rel\":\"image\"}}],\"title\":\"#DIYse_F READ_MSG: Users can read messages of all four types in  \\n\\ndifferent windows\",\"content\":\"<a href=\\\"http:\\/\\/search.twitter.com\\/search?q=%23DIYse_F\\\" onclick=\\\"pageTracker._setCustomVar(2, \'result_type\', \'recent\', 3);pageTracker._trackPageview(\'\\/intra\\/hashtag\\/#DIYse_F\');\\\"><b>#DIYse_F<\\/b><\\/a> READ_MSG: Users can read messages of all four types in  \\n\\ndifferent windows\",\"updated\":\"2010-11-24T21:39:31Z\",\"author\":{\"name\":\"Babar_Shahzad (Babar Shahzad Ch)\",\"uri\":\"http:\\/\\/twitter.com\\/Babar_Shahzad\"}}]};

and what does "store these into database in a parent child relation" mean? show us your schema. — bcosca, Nov 25 '10 at 09:38
@XCeptable - Please provide the xml. You mentioned infinite depth, sounds weird. Do you mean you have no idea how many level of children a parent can have? — ajreal, Nov 25 '10 at 09:39
"Depth of the tree will not be finite." I find that hard to believe. — Jaydee, Nov 25 '10 at 09:40
@Jaydee,ajreal, actually because these are messages data & users are allowed to enter a message as child of any message. So there is no prediction. But we need to start from a simple case, lets say 3 is depth. — XCeptable, Nov 25 '10 at 09:50
The schema is like there is one table, this one. This tables can have four fields, i.e. id,msg,msg_id, parent_id. Parent id is actually msg_id of the parent. And I am storing tweets in table. — XCeptable, Nov 25 '10 at 09:58
@Jaydee, I am getting xml from twitter, I do not know how to get their full xml structure, bec I need only two properties, So I just get those by looping through it. — XCeptable, Nov 25 '10 at 10:09
@XCeptable - just post some example of the xml from twitter, otherwise, without the schema, is quite hard to imagine — ajreal, Nov 25 '10 at 10:22
@XCeptable: Edit your question instead of adding critical data in these comments section, so people don't have to read the entire conversation to understand your question better. SO doesn't work like a forum. — bcosca, Nov 25 '10 at 10:28
@ajreal, I put example of json from twitter, xml is not somehow available but I hope json will also help. — XCeptable, Nov 25 '10 at 15:11

score 1 · Accepted Answer · edited May 23 '17 at 12:19

To save the tweets:

foreach ($xml->entry as $status) {
    // REMEMBER SANITIZE THE XML VALUES!
    $sql = "INSERT INTO 
                the_table (
                     msg
                    ,msg_id
                    ,parent_id
                )
            VALUES (
                 '{$status->content}'
                ,'{$status->id}'
                ,'{$status->in_reply_to_status_id}'
            )";
}

The recursive function to print the hierarchy is available here: How can I convert a series of parent-child relationships into a hierarchical tree? Using msg_id and parent_id as child an parent ids.

I only see one problem. If you don't have the parent tweet, this branch is not showed.

Looping through an xml object and inserting data into a table with parent child relationship

1 Answers1