Which string() to use to display first 500 words of data.

Using string functions to display a "preview" of a larger document.

[erikcw]

I have a table of large documents in mysql [text]. I want to use some sort of string() to display only about the first paragraph (or 500 words) to the user. (a preview). Then at the end have a ...(click here for more) link to the rest of the document. Which functions should I consider?

Thanks!

[coopster]

You may want to consider letting MySQL read only that much information when you are querying the database. For example,

SELECT SUBSTRING(my_text, 1, 500) FROM mytable;

And/or you could use PHP to show only that portion of the string. A recent thread [webmasterworld.com] describes some options.

[rubenski]

This piece of PHP does the trick:

if(strlen($your_text) > 500 )
{
$totalChars = 500;
$your_text = substr($your_text,0,$totalChars)."...";
}

But Coopster is right, letting MYSQL select only 500 chars is faster.

[erikcw]

If I wanted to use this MySql query:

SELECT SUBSTRING(my_text, 1, 500) FROM mytable;

but also get the rest of the data from that table without referencing each column (*) how would I do that?

SELECT *, SUBSTRING(my_text, 1, 500) FROM mytable;

Thanks,

Erik

[Netizen]

I agree with coopster that this could be done in mysql. Take a look at the substring_index [dev.mysql.com] function.

mysql> SELECT SUBSTRING_INDEX('This is a few words', ' ', 2);

returns 'This is'

[Netizen]

As regards to retrieving the rest of the information from the table I always recommend selecting the rows needs explicitly. In this case there would be no need to return the full text of the article if this is just for a preview - this wastes bandwidth and increases server load.

SELECT substring_index(mytext,' ',500) as preview, otherfield1, otherfield2 from mytable;

is what I would do.

[coopster]

erikcw, you wanted 500 words, not 500 characters. SUBSTRING only returns portions of the string. The SUBSTRING_INDEX will grab the string based on a given delimiter, in this case spaces, to return only the 500 words you so desire. (Good thing you're here to clean up after me Netizen!)

I agree with Netizen, if you're only going to display the first 500 words, don't waste any more resource by grabbing the whole column again. Worry about that if the user actually follows the link ;)

[erikcw]

I have a slight problem, it is not outputting anything!

$query = "SELECT SUBSTRING_INDEX(testimonial,' ',500) FROM testimonials WHERE id = '$id'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

In the examples given, is mytext==field_name in the DB?

SELECT substring_index(mytext,' ',500) as preview, otherfield1, otherfield2 from mytable; 

Thanks!

[Netizen]

In the examples given, is mytext==field_name in the DB?

Yes, that's correct. I'm not sure why that query isn't returning anything - the text for the given id has spaces in it, I take it?

[coopster]

Maybe the query is returning data, you just aren't accessing it yet?

$query = "SELECT SUBSTRING_INDEX(testimonial,' ',500) AS preview 
 FROM testimonials WHERE id = '$id'"; 
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); 
while ($row = mysql_fetch_assoc [php.net]($result)) { 
print $row['preview']; 
}

[erikcw]

Got it working! Thanks guys!