Parsing HTML file to Database

locorecto

Hello Guys, thanks for reading my question. I have as set of html files with some information which I need to insert into a MySQL database. The data consist of Dates and Historical facts which happened on those dates.

<p>
    <strong>June 26, 1721</strong> Following the recommendation of
    Rev. Cotton Mather, Dr. Zabdiel Boylston of Boston completes the first
    inoculation against smallpox in the U.S., injecting his own son and two of his
    slaves. 
</p>
<p>
    <strong>1736</strong> In New York, the city almshouse, located on Broadway
    near Park Row, opens an infirmary with six beds. This infirmary grows into
    Bellevue Hospital.
</p>
<p>
    <strong>May 11, 1751</strong> Benjamin Franklin and Dr. Thomas
    Bond receive a charter from the Pennsylvania legislature to open the first
    hospital in the American colonies for the sick poor and the insane. 
</p>
<p>
    <strong>1770</strong> Kings College awards the first M.D. degree in the
    colonies to Robert Tucker.
</p>
<p>
    <strong>June 13, 1771</strong> New York Hospital, the second in
    the colonies after the Pennsylvania Hospital, receives a royal charter from
    King 
</p>
<p>
    George III under the name Society of the Hospital in the City of
    New York in America, later changed to Society of New York Hospital. 
</p>
<p>
    <strong>Oct. 12, 1773</strong> The Public Hospital for Persons of
    Insane and Disordered Minds is established in Williamsburg, Virginia. It was
    the first building in North America devoted solely to the treatment of the
    mentally ill. 
</p>
<p>
    <strong>1791</strong> The Society of New York Hospital opens at a site on Broad*way
    between Duane and Worth Streets.
</p>

I have used the following php script to insert the corresponding data into the DB.

$dom_doc = new DOMDocument();
        $html_file = file_get_contents('HIA.htm');

    $dom_doc->loadHTML( $html_file );

    $tags_p = $dom_doc->getElementsByTagName('p');  

    foreach($tags_p as $key=>$tag) {
        $tag_value = $tag->nodeValue;
            $date = $tag->getAttribute('strong');
            $query = "INSERT INTO Milestones(Date, Text) VALUES('$tag_value', '$date')";
           mysql_query($query)or die('Value '.$Value.' and Date'.$date. 'could not be inserted. '.myslq_error() );
           }
echo "Done";

Here is the problem. When I go into the DB the Date fields are empty. Also the Text fields include the date at the beginning of the text as follow

June 26, 1721 Following the recommendation of Rev. Cotton Mather, Dr. Zabdiel Boylston of Boston completes the first inoculation against smallpox in the U.S., injecting his own son and two of his slaves.

I would like to have the date formatted into the Date field as m/d/Y, and have the Text field with only the text of the historical fact and not the date.

I appreciate your help in advance.

johanafm

First off, I recommend not using $tag as identifier when dealing with elements, since there is a difference between tag and element. The tag is the tag only, nothing else. A tag can be the start or end tag: <I_am_a_start_tag>, </I_am_an_end_tag>. An element is the two tags and everything in between them. I.e. start tag, nodevalue, end tag.

Nodevalue contains everything belonging to the element, but other elements are not included, since they are markup, not the actual data. That is, for

<outer>First part of outer's node valye <inner>inner's nodevalue, but also middle part of outer's nodevalue</inner> last part of outer's nodevalue</outer>

which means that you can not just take the p nodevalues unless you want the strong nodevalues to be included.

What you need to do is go through each p's childNodes. If it's a strong node, it's your date and you deal with that separately, if it's a text node, you concatenate it together with (possibly) other text nodes of p, but in your case you just need to take contents of the text node coming after the strong element. Do note however that this p element contains 3 child nodes: text, strong, text

<p>
   <strong></strong>
   text
</p>

while this contains only two: strong and text

<p><strong></strong>text</p>

locorecto

Thanks for the suggestion.

I got it to work.