Selecting only text between HTML tags in MYSQL db

[benjudy]

I have the following HTML code stored in a MYSQL table:

 

<ul>

  <li>this is the first list item</li>

  <li>second list item</li>

  <li>and another list item</li>

</ul>

 

What I need to do is display only the text of the first list item:

this is the first list item

 

So I want to end up with just that string of text in a variable.

 

I think this could be done one of two ways.

 

1) Write a fancy sql SELECT statement that would look for the HTML tags and only select what is between them.

 

I think this would be nice because I would not be SELECTing more than I need from the db. Unfortunately I'm not too swift at writing fancy sql statements yet so I'm not even sure if this can be done.

 

2) Put the raw db contents into a variable, then use PHP to search for the <li> and </li> tags and place whatever's between them into another variable.

 

I've looked into things like regular expressions and explode in PHP, but I'm just not getting it. I think there is an easy solution that is just beyond my reach. Any help would be appreciated!

[revraz]

Maybe something like this?

 

$text = "<ul>
  <li>this is the first list item</li>
  <li>second list item</li>
  <li>and another list item</li>
</ul>";
echo strip_tags($text);

 

Then just see how you can get the first line.

[benjudy]

revraz,

 

Thanks for the reply. I have not used strip_tags before, thanks for pointing that out. But I don't think that will get me where I want to be.

 

To be more precise, I only want all of the characters in between the first set of list item HTML tags. There may be more HTML code in there, such as <strong> tags and so forth -- I don't want to strip those out.

 

So all I care about is what's in that first list item. I want to leave out all of the other code including all of the list items that follow.

 

If I do strip_tags it seems to me there would then be no way to figure out where the text of the first list item ends, so I can lose the rest.

 

This might clarify what I think I'm trying to do. Here's some pseudocode:

 

$text = "<ul>
  <li>this is the first list item</li>
  <li>second list item</li>
  <li>and another list item</li>
</ul>";

/*this is what I don't know how to do*/
$text_to_display = begin after (first instance of "<li>"), stop before (first instance of "</li>");

print("$text_to_display");

 

But I think a better way to do this might be with my sql SELECT statement, if possible.

[simcoweb]

Another way to do this is to NOT put your HTML in the database. Instead, place the HTML in the echo area of your PHP and simply call the field with the text into the spot in your HTML you'd like to have it placed.

 

Example:

<?php
$sql = "SELECT * FROM table_name"; (just an example query, not meant to be actual)
$results = mysql_query($sql) or die(mysql_error());
echo"<ul>";
while($row = mysql_fetch_array($results) {

echo" <li>" . $row['field_name'] . "</li>";
  }
echo "</ul>\n";

[benjudy]

simcoweb, thanks for the reply! That is a great suggestion... unfortunately I don't have that capability. The HTML is entered into the db by users who submit content from a CMS. The CMS uses a WYSIWYG editor, so the data going into the db is HTML formatted. Nothing I can do about that unless I want to rebuild the entire CMS somehow.... (not really an option) 

[jeffjohnvol]

perhaps something like

 

select substr(datafield,locate('<li>',datafield,1)+4,locate('<li>',datafield,locate('<li>',datafield)+4) - locate('<li>',datafield,1)+4 ) from table

limit 0,1

 

where datafield is the fieldname you are looking for.  You may need to play with it to get it right.