Hi,
How do I write a MySQL statement that will only get the X characters from a value? For instance, If I only want the first 250 characters of a post then put '...' and a link to the topic. Could someone show me the exact MySQL query to do this? I know how to limit the actual rows, but not the characters.
Thanks,
manoj
PS I want this to be something unrelated to SMF.. ::)
SMF does this by retrieveing the whole row from MySQL, and then uses PHP to trim it. Something like:
<?php
// Length to limit it to
$length = 250;
// Your query here
// $result = db_query(.........);
while ($row = mysql_fetch_assoc($request))
{
// If we want to limit the length of the post.
if (!empty($length) && strlen($row['body']) > $length)
{
$row['body'] = substr($row['body'], 0, $length);
// The first space or line break. (<br />, etc.)
$cutoff = max(strrpos($row['body'], ' '), strrpos($row['body'], '<'));
if ($cutoff !== false)
$row['body'] = substr($row['body'], 0, $cutoff);
$row['body'] .= '...';
}
// Do whatever else you want
}
?>
This code makes sure it doesn't cut it off in the middle of a word, or in a <br /> tag (well, any tag really) :)
Edit: If you want it in pure MySQL, the best I can think of is:
SELECT CONCAT(SUBSTRING(body, 0, 250), '...') AS trimmed_body
FROM table
WHERE something
Something like that, although it isn't as good as the PHP one ;)