Blog | Status Updates | About | Contact | Advertise

July 26, 2004 / Filed under: SQL

I’ve been asking around, and I have yet to receive an answer. I need to know how some blogs allow for only a brief "snippet" of their entry to be shown on the front page, much like I do.

The reason I want to know how other people do it, is because the way I do is faulty. There are some big problems with it, and I need to get it corrected fast. I don’t have time to insert a new entry – only to see that it’s messed up the entire "blog" section.

What am I talking about, you wonder...?

I’m talking about the SQL query that pulls out only a certain number of characters from my entries, and displays that on screen, like you see in my blog section. This is accomplished by using a query similar to this:

SELECT my_table.*, LEFT(my_table.entry, LENGTH(my_table.entry) - LOCATE(‘ ’, REVERSE(my_table.entry), LENGTH(my_table.entry) - 300 )) FROM ...

Now, I know there has to be an easier way. The WHITE text in the query above basically says to pull out the first 300 characters in my entry, but use a space as the "break point," and not in between a word. In other words, it won’t cut you off in the middle of a word – but it will cut you off in the middle of a sentence, which is fine.

The problem is the HTML that’s included in the entry. For example, sometimes this query spits out something like this for the browser to render:

<p>The problem with cell phones is the technology is constantly getting better, so their actual "use life" is about 1 and a half years, but usually less. Have you ever used a particular cell phone longer than that?</p>

<h3>Old may still be

Notice there’s no closing </h3> tag? Sure, that’s because the query was instructed to only pull out a certain number of characters, and quite bluntly – some characters don’t make the cut. And HTML is treated as plain characters, just like any other.

This has happened many times so far, on this site, and I’ve had to adjust that 300 number, to make it larger, or smaller, until all of a particular HTML tags would be showing. It’s happened with <img> tags, too.

I think, perhaps, I just need to understand MySQL better, when it comes to inserting data into a TEXT field type. Unless someone knows a quick way to resolve this...