Pull first X words (not just characters) from mySQL

Question

I'd like to be able to pull the first X words from a database field for use in a preview. Basically if a field 's content was

 "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris malesuada."

I would like to echo

 "Lorem ipsum dolor sit amet... see more"

What's the best way to do this?

The only thing I know to do is pull the whole field in a query then do something like

$foo = [query_results];
$bar = explode(' ', $foo);
for($x=0, $x<6, $x++){
     echo $bar[$x];
};
echo "... see more"

Is there a better approach to this?

Answer 1

Method 1 (Better):

Nope, you could also use the substring function of mysql to fetch desired text directly from the field.

Prototype:

SUBSTRING(string,position)

Example:

SELECT SUBSTRING(field, 25) FROM table

Method 2:

You can also use PHP's substr function for the same purpose but first you will have to select whole field value from db.

$cutted = substr($row['fieldname'], 0, 25) . '.....'; // get 25 chars and append ....

In Your Case:

$str = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris malesuada.";
$parts = explode(" ", $str);
print $parts[0] . ' ' .$parts[1] . ' ' .$parts[2] . ' ' .$parts[3] . ' ' .$parts[4] . '.....See More';

Output:

Lorem ipsum dolor sit amet.....See More

Answer 2

SELECT SUBSTRING(Filed_Name,1,X) FROM Table where field2 = 0

you can replace x in substring claus by characters you need to display

Answer 3

well you could do it in your query using the substring function

SELECT CONCATENATE(SUBSTRING(myfield, 0, N), "... see more") FROM mytable

that will get you the first N letters...

if you really want to get the first N words and you define words as "delimited by spaces", you could still do it in the query as follows

SELECT CONCATENATE(SUBSTRING_INDEX(myfield," ", N), "... see more") FROM mytable

Answer 4

You definitely want to use SUBSTRING_INDEX which will return some number of characters until a specified count is achieved based on the occurrence of a delimiter. In your case the call would look like this:

 SELECT SUBSTRING_INDEX(text_field, ' ', 6) FROM ...

In particular, this will return up to six words where we define a word as a set of characters that are not spaces delimited by spaces.

Note: this will return punctuation attached to the last word, which may or may not be desired. It'd be simple enough to replace any punctuation characters at the tail of the string in PHP, but if you want to stay completely within SQL I think you can use TRIM. The syntax for that would be something like:

SELECT TRIM(TRAILING ',' FROM SUBSTRING_INDEX(text_field, ' ', 6)) FROM ...

There may be a better option for removing the trailing punctuation -- but perhaps that's another question (I'm still looking for a better solution than TRIM).

Answer 5

My philosophy is, don't make the database work too hard. In my experience, MySQL is much faster when you feed it a real simple query and then do the "real" work with PHP or whatever language you are using. I wouldn't follow any of the suggestions posted - grab the whole string and then shrink it down with your scripting language or choice. The only exception would be if the string could potentially be very large - e.g. 50KB or more. In that case, having the database shrink it down for you is perhaps faster. But really, unless you're doing thousands of these queries per second (and if you are you're doing it wrong - use some kind of caching), then I don't think there's any reason to have the database do this work for you.