Limiting the amount of text returned from a query

[russkern]

What is the best way to display a limited amount of text returned from a MySql query?

I have a News articles table with a headline and text... I query the database and return both, but I only want to display say the first 30 words of the article text and append it with "... MORE" as a link to the complete article.

The last part is easy, it's the limiting of the number of words displayed that has me stumped.

Russ

[eelixduppy]

You could do something like this directly with MySQL:

$query = "SELECT [url=http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring]SUBSTR[/url](text,0,30) AS short_text FROM table";

[eelixduppy]

>> say the first 30 words of the article

I quickly read over your post the first time and did not see that you wanted the first 30 words. To get the words you are going to need to count the spaces; if you have 29 spaces you have 30 words. This, however, will require more work than I personally think is needed to just display a preview of an article. So instead of using 30 characters like I have in my answer above, you might want to use something like 200 characters. This should give a little better preview of the text.

Also, I seem to have forgotten my manners: Welcome to WebmasterWorld! :)

[alexdunae]

You might want to try using a regular expression.

Run your query as usual (without truncating) and then truncate the string in PHP/Ruby/whatever.

This should work:

^(.[^\s]*){0,30}

where '30' is the number of words you would like.

[mooger35]

This is what I use...

function trunc($phrase, $max_words)
{
$phrase_array = explode(' ',$phrase);
if(count($phrase_array) > $max_words && $max_words > 0)
$phrase = implode(' ',array_slice($phrase_array, 0, $max_words)).'...';
return $phrase;
}

[russkern]

Thanks for all the responses... I'll give it a shot tonite and see what works best for my needs...

Thanks for the welcome as well... hopefully I'll be able to contribute a bit more in the future rather than just leech off everyone else :)

Russ

[henry0]

I use the following to assure that a word is not broken

<?php

function strtrim($str, $maxlen=100, $elli=NULL, $maxoverflow=15) {
global $CONF;

if (strlen($str) > $maxlen) {

if ($CONF["BODY_TRIM_METHOD_STRLEN"]) {
return substr($str, 0, $maxlen);
}

$output = NULL;
$body = explode(" ", $str);
$body_count = count($body);

$i=0;

do {
$output .= $body[$i]." ";
$thisLen = strlen($output);
$cycle = ($thisLen < $maxlen && $i < $body_count-1 && ($thisLen+strlen($body[$i+1])) < $maxlen+$maxoverflow?true:false);
$i++;
} while ($cycle);
return $output.$elli;
}
else return $str;
}

?>