Re: limit on displaying a LONGTEXT filed from MySQL database
| From: | Hugh Bothwell | Date: | Mon, 23 Jun 2003 02:03:20 +0000 |
| Subject: | Re: limit on displaying a LONGTEXT filed from MySQL database | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-152486@lists.php.net to get a copy of this message | ||
"Artoo" <artoo_s@hotmail.com> wrote in message
news:20030623004121.35728.qmail@pb1.pair.com...
> How can I start searching for the first space in a string while starting
at
> say the 150th character? I'm trying to display the first 150 characters
of
> an article that is stored in a LONGTEXT filed of a MYSQL database, and
> should the 150th character be inside a word, I would want to finish
> displaying that word.
>
> For example supose the 150th character is the v in the word "privileges"
I
> would want to finish displaying the word and end with "privileges" rather
> then ending with"priv"
If you want to go to the next space, try
SELECT
@a:= LOCATE(' ', mytext, 150),
IF( @a > 0,
LEFT(mytext, @a ),
mytext
) AS returntext
FROM dbase
If you have some other small set of terminal
characters, you can extend it like
SELECT
@a:= LOCATE(' ', mytext, 150), @a:= IF(@a=0, 1000, @a),
@b:= LOCATE('.', mytext, 150), @b:= IF(@b=0, 1000, @b),
@c:= LOCATE(',', mytext, 150), @c:= IF(@c=0, 1000, @c),
@first:= MIN(@a, @b, @c),
IF( LENGTH(mytext) > 150),
LEFT(mytext,
IF(@first < 1000,
@first,
150
)
),
mytext
) AS returntext
FROM dbase
If you want more flexibility - which I would - I suggest
returning the first 170 characters or so, and truncate
more accurately in PHP.
SELECT
LEFT(mytext, 170) AS returntext
FROM dbase
<?php
function TruncateAfterWord($str, $len) {
if (strlen($str) <= $len)
return $str;
else {
preg_match( "/\A([A-Za-z]*)/", substr($str, $len), $match );
return substr($str, 0, $len) . $match[1];
}
}
?>
--
Hugh Bothwell hugh_bothwell@hotmail.com Kingston ON Canada
v3.1 GCS/E/AT d- s+: a- C+++ L++>+++$ P+ E- W+++$ N++ K? w++ M PS+
PE++ Y+ PGP+ t-- 5++ !X R+ tv b++++ DI+++ D-(++) G+ e(++) h-- r- y+