Excerpts of data

[tchallies]

I am working on a project that involves PHP and MYSQL. I am currently pulling information from a TEXT field (could be LONGTEXT). I would like to be able to create an excerpt from the data.

So...say I run this query:

SELECT review FROM bookreview WHERE id=1

That will give me a rather lengthy review. What I would like to do is display only the first X number of words or characters (preferably words), add a ... and then link it to the rest of the story.

My question is how to cut short "review" after X words or characters. I presume it can be done in either PHP or MYSQL but I have no idea how.

I am uncertain if this is important, but this query is going to loop at least several times so I need to be able to work whatever code you give me into a loop so I can pull multiple reviews.

[jusdrum]

It would be best to do it in MySQL, that way it's not returning all of that data to PHP and you end up just throwing most of it out. Use the SUBSTRING(str,from,len) function in MySQL, like so:

select SUBSTRING(Review,0,100) as Excerpt from table

[Stormfx]

In PHP:

// Done the queries, the variable $text holds the result

$if (strlen($text > 100)) {
$text = substr($text, 0, 100).'...';
}

^^ He beat me to the MySQL part :)

[tchallies]

Thanks to those who replied. I got the following working:

SELECT SUBSTRING( excerpt, 1, 100 ) AS excerpt
FROM review

Now...is it possible to have it done by words instead of characters? I'd rather have it cut off at the end of a word rather than in the middle of a word...

[Stormfx]

Well, you could just trim off the last word, if it's cut off.

i.e.
// $text is the result from the query. Note that I'm guessing here, the actual code may vary, but to give you the idea...

$text = trim(substr($text, 0, strrchar($text, ' ')));

Basically, you're just getting the text from the beginning(0) to the last occurence of a space (' ').

[tchallies]

I am not able to have any success with the PHP way of doing this.

My SQL is as follows: SELECT * FROM review ORDER BY timestamp DESC LIMIT 3

while ($row = mysql_fetch_array($sql)) {
excerpt_shorten = $row["excerpt"];
if (strlen($excerpt_shorten >= 100)) {
$excerpt_shorten = '...';
}
}

This is not working. $excerpt_shorten ends up being the same as $excerpt regardless of the length of the longtext field from SQL.

[Stormfx]

In your code above, you're forgetting to actually reduce the length of the string. But, in conjunction with that and the prior question, here's a quick fix for ya:

while ($row = mysql_fetch_array($sql)) {
$excerpt_shorten = $row["excerpt"];
if (strlen($excerpt_shorten >= 100)) {
$excerpt_shorten = substr($excerpt_shorten, 0, 100);
$excerpt_shorten = trim(substr($excerpt_shorten, 0, strrchar($text, ' '))).'...';
}
}

$excerpt_shorten should now be around 100 chars long and without the last word cut off. One other note, you're overwriting the excerpt_shorten value with the subsequent calls.

Hope that helps.
Storm

[tchallies]

I have been unable to get this to work. Have you tried this before with the code you provided? It just doesn't seem to get it done...

[Stormfx]

Like I said,

One other note, you're overwriting the excerpt_shorten value with the subsequent calls

If you're calling more than one row, you can't overwrite the excerpt_shorten value. You could then do this:

while ($row = mysql_fetch_array($sql)) {

// get the exceprt data
$exc = $row["excerpt"];

// check to see if it's longer than 100 characters...
if (strlen($exc >= 100)) {
$exc = substr($exc, 0, 100);
}
// get rid of the last word or partial word and add '...'
$excerpts[$row['timestamp']] = trim(substr($exc, 0, strrchar($text, ' '))).'...';
}

Which would give you a new array called $excerpts with the timestamp as the key and the excerpt as the value.

Yes, I did try my code :) I always do. Even made a dummy database for the test :)

[tchallies]

I must be doing something wrong. As far as I can see, the length of $exc is never being chopped. It always thinks it is less than 100 characters, even though it is far longer. It's a LONGTEXT field with several hundred characters. Here is my complete code...perhaps there is something wrong with it elsewhere...

<?
include ("connect.php");
$queryString = "SELECT * FROM review ORDER BY timestamp DESC LIMIT 3";
$sql = mysql_query($queryString);

$alternate = "2";
while ($row = mysql_fetch_array($sql)) {
$excerpt1 = $row["excerpt"];
$yorn = $row["yorn"];
$titleid = $row["titleid"];
$authorid = $row["authorid"];
$reviewerid = $row["reviewerid"];
// get the exceprt data
$exc = $row["excerpt"];
// check to see if it's longer than 100 characters...
if (strlen($exc >= 100)) {
$exc = substr($exc, 0, 100);
}
// get rid of the last word or partial word and add '...'
$excerpts[$row['timestamp']] = trim(substr($exc, 0, strrchar($text, ' '))).'...';
}

A couple of questions.

First, what variable do I want to echo back? It is $excerpts?

Second, what is $text?

Third, is strrchar correct or should it be strrchr?

[Stormfx]

Hmm, Ok. When you get the row, you have to either assign the data or print the data out before the next iteration, otherwise, all of those variables inside the loop are overwritten. I did notice a slight typo that wouldn't show up in the result.

Try this code:

<?php
include ("connect.php");
$queryString = "SELECT * FROM review ORDER BY timestamp DESC LIMIT 3";
$sql = mysql_query($queryString);

$alternate = "2";
while ($row = mysql_fetch_array($sql)) {
$excerpt = $row["excerpt"]; // Raw $excerpt
$titleid = $row["titleid"];
$reviewerid = $row["reviewerid"];
$authorid = $row["authorid"];
$yorn = $row["yorn"];

// Is $exceprt longer than 100 chars?
if (strlen($excerpt) > 100) {
$excerpt = substr($excerpt, 0, 100); // Yep, trim it down.
}

// Get rid of the last word or partial word and add '...'.
$excerpt = trim(substr($excerpt, 0, strrpos($excerpt, ' '))).'...';

// Print the data out to make sure it's working.
$output = 'Title ID: '.$titleid.'<br />';
$output.= 'Excerpt: '.$excerpt.'<br />';
$output.= 'Reviewew ID: '.$reviewerid.'<br />';
$output.= 'Author ID: '.$authorid.'<br />';
$output.= 'Yorn: '.$yorn.'<br /><br />';

echo $output;
}
?>

[tchallies]

Woohoo! Finally! Thank you so much for your help on this.

[Stormfx]

You're welcome. Sorry it took so long :)