PHP & mySQL - Parse/Separate Single HTML Field Text Into 2 Variables

Question

Thank you in advance for taking the time to work through my issue.

I am using a PHP script to query a mySQL database. Unfortunately, the product description, ingredients, and additional information (the paragraph at the very end) are all stored in one single field, along with the name (the name is redundant and can just be ignored). All of the text is surrounded in HTML code. I do not want to keep or store any HTML code, but it may be useful as delimiters.

Important: the HTML is stored encoded, so

<p>

is stored as

&lt;p&gt;

Here is an example of HTML code that is stored in the mySQL database (this is the exact way it is stored. As I mentioned earlier, the HTML is encoded.):

&lt;table border=&quot;0&quot; cellpadding=&quot;2&quot; cellspacing=&quot;2&quot;&gt;
&lt;tbody&gt;
    &lt;tr valign=&quot;top&quot;&gt;
        &lt;td&gt;
            Item:&lt;/td&gt;
        &lt;td&gt;
            Olive Loaf - Baked - &lt;b&gt;Gluten Free!&lt;/b&gt;&lt;/td&gt;
    &lt;/tr&gt;
    &lt;tr valign=&quot;top&quot;&gt;
        &lt;td&gt;
            Description:&lt;/td&gt;
        &lt;td&gt;
            A blend of beef and pork along with our unique spices to create a base mix. To this mix we add plenty of olives and form it into a loaf, we then smoke this over natural hardwoods for a unique Koegel flavor.&lt;/td&gt;
    &lt;/tr&gt;
    &lt;tr valign=&quot;top&quot;&gt;
        &lt;td&gt;
            Ingredients:&lt;/td&gt;
        &lt;td&gt;
            Beef and Pork, Water, Spanish Olives (Olives, Pimentos, Sodium Alginate, Guar Gum, Calcium Chloride, Water, Salt, Lactic Acid), Nonfat Dry Milk, Corn Syrup, Salt, Red Sweet Peppers (bell peppers, water, citric acid.), Spices, Dextrose, Dehydrated Onions.&lt;/td&gt;
    &lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;

&lt;p&gt;
    &lt;strong&gt;Each loaf weighs approximately 6 lbs.&lt;/strong&gt;&lt;/p&gt;

Here is the same code snippet with the HTML decoded (this is NOT how it is stored in the mySQL database. I provided this just for a visual.):

<table border="0" cellpadding="2" cellspacing="2">
<tbody>
    <tr valign="top">
        <td>
            Item:</td>
        <td>
            Olive Loaf - Baked - <b>Gluten Free!</b></td>
    </tr>
    <tr valign="top">
        <td>
            Description:</td>
        <td>
            A blend of beef and pork along with our unique spices to create a base mix. To this mix we add plenty of olives and form it into a loaf, we then smoke this over natural hardwoods for a unique Koegel flavor.</td>
    </tr>
    <tr valign="top">
        <td>
            Ingredients:</td>
        <td>
            Beef and Pork, Water, Spanish Olives (Olives, Pimentos, Sodium Alginate, Guar Gum, Calcium Chloride, Water, Salt, Lactic Acid), Nonfat Dry Milk, Corn Syrup, Salt, Red Sweet Peppers (bell peppers, water, citric acid.), Spices, Dextrose, Dehydrated Onions.</td>
    </tr>
</tbody>
</table>
<p>
    <strong>Each loaf weighs approximately 6 lbs.</strong></p>

Basically, I want to ignore the name, save the Description and Additional Information (the last paragraph below the description) as $productDescription (perhaps add two newline characters after the description to separate the additional information), and save the ingredients as $productIngredients. I do not want to include the text "Description:" or "Ingredients:" ... just the information immediately after. As stated above, I am only interested in the raw text - I do not want to save any of the HTML code. All HTML code should be ignored when storing the information into the 2 variables.

Any help is well-appreciated!

Thank you,

-Jeff

EDIT

What's going on is the mySQL database is connected to an opencart website. On the admin side of the website, there are no separate fields for ingredients, description, etc. There's about everything else (weight, dimensions, SKU, model, etc.). This is because the opencart website is not exclusively for food. It can be used for electronics, in which case there would be no need for such fields. The store owner (not me) feeds all of this information into the description entry point. The HTML may give this away. The website is managed by another individual (I have direct contact to him if need be). I would prefer not to have him nor myself alter the opencart code to add the extra fields, unless it can be easily done.

Would it be easy to add additional fields to an opencart website? That way, the description field can be exclusive. Thanks for the replies.

I have feeling that you personlally not responsible for such a scaring design but I can't resist from -1-ing it. "product description, ingredients, and additional information are all stored in one single field" is a developer's living nightmare. — Your Common Sense
– Your Common Sense, Commented Jan 1, 2013 at 18:26
What's your preferred XML or DOM parsing library? Do you know how to use SimpleXML, DOM, or simple_html_dom? — Michael Berkowski
– Michael Berkowski, Commented Jan 1, 2013 at 18:31
While you are working with this code, you are well advised to take the time to extract these values and store them into their own columns. It won't be easy to automate the process of properly normalizing Ingredients into their own one to many table, however. — Michael Berkowski
– Michael Berkowski, Commented Jan 1, 2013 at 18:33
I am not responsible for the scary design. This is why I turned to SOF for guidance and suggestions. What's going on is the mySQL database is connected to an opencart website. On the admin side of the website, there is no separate fields for ingredients, description, etc. There's about everything else (weight, dimensions, SKU, model, etc.). This is because the opencart website is not exclusively for food. It can be used for electronics, in which case wouldn't need such fields. The store owner (not me) feeds all of this information into the description entry point. The HTML may give this away. — Jeff
– Jeff, Commented Jan 1, 2013 at 18:48
...continued from above: The website is managed by another individual (I have direct contact to him if need be). I would prefer not to have him nor myself alter the opencart code to add the extra fields, unless it can be easily done. I actually encode the collected data as JSON and it is read by an iOS application. Michael: I am not familiar with any of the things you listed. Not to restate myself, but would it be easy to add additional fields to an opencart website? That way, the description field can be exclusive. Thanks for the replies. — Jeff
– Jeff, Commented Jan 1, 2013 at 18:52

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

This problem is straightforward. I think it's a mistake to slag the person who did this design. He simply stored a snippet of entitized HTML in a database field. Now you're trying to extract specific information from it.

Obviously the first thing you do upon retrieving one of these items is de-entitize it (turn < into < etc). html_entity_decode does that.

Now it looks like the html you have is well-formed. That is, the <p> and </p> elements and so forth are matched correctly. That's good, because you can use an XML library to manipulate it.

This is sample code, from a project of mine. I haven't tried debugging it in your project.

 $xml= simplexml_load_string("<?xml version='1.0'?>\n" . html_entity_decode($mydata);
 $ns = $xml->getNamespaces(true);
 foreach ($ns as $key => $val) {
    $xml->registerXPathNamespace($key, $val);
 }
 unset($ns);

Now you have a simpleXML object containing your little article. You can use various APIs to extract the data you need. Take a look here for some examples.

http://php.net/manual/en/simplexml.examples-basic.php

I suspect you'll be able to work with this material fairly easily.

Note, in the comments some people have said that you should try to extract the interesting information from these HTML stanzas and put it into dedicated columns. That might be true, especially if you have to search on this data or update it on the fly.

But it's also OK to store stuff in XML/HTML. You can use FULLTEXT search if you need to search it.

Edit

You may need to wrap the XML stuff in a single <article> ... </article> stanza, like so. It's possible the last item, the paragraph about the weight of the loaf, is being rejected as extra.

 $xml= simplexml_load_string("<?xml version='1.0'?>\n<article>\n" .
    html_entity_decode($mydata) . "\n</article>\n";

Handling XML takes a bit of fooling around, at least for me.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 1, 2013 at 19:20

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeff Over a year ago

I'm getting a weird error when I try your solution:

Warning: simplexml_load_string(): Entity: line 4: parser error : Extra content at the end of the document in - on line 112 Warning: simplexml_load_string(): in - on line 112 Warning: simplexml_load_string(): ^ in - on line 112 Fatal error: Call to a member function getNamespaces() on a non-object in - on line 113

O. Jones Over a year ago

It looks like your article contains more than one toplevel stanza. See my edit.

Jeff Over a year ago

Okay, I ran into the entity 'nbsp' not defined... error but I created a function to remove  . Here is the code I am using: PasteBin and here is my output: PasteBin. I'm getting a Fatal error: Call to a member function getNamespaces() on a non-object in.

Jeff Over a year ago

Thanks again for assisting me through all of this.

Collectives™ on Stack Overflow

PHP & mySQL - Parse/Separate Single HTML Field Text Into 2 Variables

1 Answer 1

Edit

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related