FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Extract a paragraph or specified character/word from mysql





pashmina
Is it possible to extract only the first paragraph or the specified number of character/word with mysql query or with php.
I have seen this tutorial somewhere, but can't remember where
imagefree
if you have a paragraph in a variable $string, you can use this code to extract a part (first character) of it:

Code:
$first_character = substr($string, 0 , 1 )


0 means 1st character in string (add 1 to start from next character and so on),

1 means take just one character.
the use of substr() is really complex under different circumstances when you are using it for the first time. Read the manual : http://php.net/substr


try it.
jmraker
To get the first sentence in MySQL, you can use the MySQL function SUBSTRING_INDEX
SELECT SUBSTRING_INDEX(field, '. ', 1) AS field1 FROM table

It shouldn't return the '. ' at the end of field1

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
pashmina
thank you for the headsup

so far I have got this much

Code:
<h4><a href="article.php?aid=<?php echo $row_rs_search['aID']; ?>"><?php echo $row_rs_search['aTitle']; ?></a><br />
        <?php echo substr(strip_tags($row_rs_search['aDesc']),0,200); ?><a href="article.php?aid=<?php echo $row_rs_search['aID']; ?>">[...]</a><br />
      -<em><?php echo $row_rs_search['aAuthor']; ?></em></h4>


However the 200 character limits the last word of the paragraph to be complete. Is there a way to strip the last word? OR, is there another method to get the sentence only.
I have heard of explode and implode, but don't know how to use them.
jmraker
Code:
<?php

$para = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec tincidunt. Integer vulputate feugiat neque. Suspendisse venenatis, ligula in sodales dapibus, mauris risus dictum sapien, sit amet ultrices libero felis vitae tortor. Donec cursus nulla eu nulla. Donec vehicula laoreet quam. Nullam vestibulum felis eget massa. Curabitur sodales. Etiam lobortis est in ante. Nam ultrices felis ac mi. Curabitur aliquet, diam non dictum laoreet, risus elit lacinia nunc, nec dictum sapien tellus sed turpis. Vestibulum vehicula nibh at turpis. Suspendisse sem quam, tempus ut, vestibulum vehicula, posuere a, nunc. Nunc congue odio vitae dolor semper cursus. Suspendisse placerat magna id purus varius tincidunt. Nulla venenatis, est vitae feugiat feugiat, justo risus feugiat neque, sit amet volutpat tortor lectus in ante. Donec porta.";

$str = wordwrap($para, 200, "\0");
$arr = explode("\0", $str);

//print_r($arr);
echo $arr[0];
?>


This will do a wordwrap at 200 width, then put each line into an array where $arr[0] is the first line that's 193 letters long and ends at the end of a word, the next word would make it over 200 letters. Line 2 is at $arr[1], if it exists.

This is not the best solution if the paragraphs it has to word wrap is extremely long, but you can shorten it with a substr to around 250 letters long, then word wrap it.
rockacola
jmraker wrote:
Code:
<?php

$para = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec tincidunt. Integer vulputate feugiat neque. Suspendisse venenatis, ligula in sodales dapibus, mauris risus dictum sapien, sit amet ultrices libero felis vitae tortor. Donec cursus nulla eu nulla. Donec vehicula laoreet quam. Nullam vestibulum felis eget massa. Curabitur sodales. Etiam lobortis est in ante. Nam ultrices felis ac mi. Curabitur aliquet, diam non dictum laoreet, risus elit lacinia nunc, nec dictum sapien tellus sed turpis. Vestibulum vehicula nibh at turpis. Suspendisse sem quam, tempus ut, vestibulum vehicula, posuere a, nunc. Nunc congue odio vitae dolor semper cursus. Suspendisse placerat magna id purus varius tincidunt. Nulla venenatis, est vitae feugiat feugiat, justo risus feugiat neque, sit amet volutpat tortor lectus in ante. Donec porta.";

$str = wordwrap($para, 200, "\0");
$arr = explode("\0", $str);

//print_r($arr);
echo $arr[0];
?>


This will do a wordwrap at 200 width, then put each line into an array where $arr[0] is the first line that's 193 letters long and ends at the end of a word, the next word would make it over 200 letters. Line 2 is at $arr[1], if it exists.

This is not the best solution if the paragraphs it has to word wrap is extremely long, but you can shorten it with a substr to around 250 letters long, then word wrap it.


I was facinated by this solution Smile
Normally I would:
- trancate paragraph to 200 character long
- explode by using space as seperator
- remove last element in array
- implode back to string
This would give me 'approximated' 200 character long blurb.


Also I wouldn't do 'programming' in SQL eg/ LOWER, CONCAT.. etc. But that's just my preference.
Stubru Freak
rockacola wrote:
jmraker wrote:
Code:
<?php

$para = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec tincidunt. Integer vulputate feugiat neque. Suspendisse venenatis, ligula in sodales dapibus, mauris risus dictum sapien, sit amet ultrices libero felis vitae tortor. Donec cursus nulla eu nulla. Donec vehicula laoreet quam. Nullam vestibulum felis eget massa. Curabitur sodales. Etiam lobortis est in ante. Nam ultrices felis ac mi. Curabitur aliquet, diam non dictum laoreet, risus elit lacinia nunc, nec dictum sapien tellus sed turpis. Vestibulum vehicula nibh at turpis. Suspendisse sem quam, tempus ut, vestibulum vehicula, posuere a, nunc. Nunc congue odio vitae dolor semper cursus. Suspendisse placerat magna id purus varius tincidunt. Nulla venenatis, est vitae feugiat feugiat, justo risus feugiat neque, sit amet volutpat tortor lectus in ante. Donec porta.";

$str = wordwrap($para, 200, "\0");
$arr = explode("\0", $str);

//print_r($arr);
echo $arr[0];
?>


This will do a wordwrap at 200 width, then put each line into an array where $arr[0] is the first line that's 193 letters long and ends at the end of a word, the next word would make it over 200 letters. Line 2 is at $arr[1], if it exists.

This is not the best solution if the paragraphs it has to word wrap is extremely long, but you can shorten it with a substr to around 250 letters long, then word wrap it.


I was facinated by this solution Smile
Normally I would:
- trancate paragraph to 200 character long
- explode by using space as seperator
- remove last element in array
- implode back to string
This would give me 'approximated' 200 character long blurb.


Also I wouldn't do 'programming' in SQL eg/ LOWER, CONCAT.. etc. But that's just my preference.


SQL programming is better, because that way, when you want the first few words of a very long text, it doesn't have to extract the whole text, but only the first 200 characters.
So, extract the first 200 letters from the database. After that, you can do this:

Code:
$textarr = explode(" ", $text);
array_pop($textarr);
$text = implode(" ", $textarr);
pashmina
Code:
<?php

$para = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec tincidunt. Integer vulputate feugiat neque. Suspendisse venenatis, ligula in sodales dapibus, mauris risus dictum sapien, sit amet ultrices libero felis vitae tortor. Donec cursus nulla eu nulla. Donec vehicula laoreet quam. Nullam vestibulum felis eget massa. Curabitur sodales. Etiam lobortis est in ante. Nam ultrices felis ac mi. Curabitur aliquet, diam non dictum laoreet, risus elit lacinia nunc, nec dictum sapien tellus sed turpis. Vestibulum vehicula nibh at turpis. Suspendisse sem quam, tempus ut, vestibulum vehicula, posuere a, nunc. Nunc congue odio vitae dolor semper cursus. Suspendisse placerat magna id purus varius tincidunt. Nulla venenatis, est vitae feugiat feugiat, justo risus feugiat neque, sit amet volutpat tortor lectus in ante. Donec porta.";

$str = wordwrap($para, 200, "\0");
$arr = explode("\0", $str);

//print_r($arr);
echo $arr[0];
?>


@jmraker
Thank you for this solution

@Stubru Freak

Code:
$textarr = explode(" ", $text);
array_pop($textarr);
$text = implode(" ", $textarr);


I am working on adding your code as well
pashmina
after looking around for a while I found another solution as well.

Code:
<?php

/***
 * @snarf the first n words from a string
 *
 * @param string $string
 *
 * @param int $num
 *
 * @param string $tail
 *
 * @return string
 */

function first_words($string, $num, $tail='&nbsp;...')
{
        /** words into an array **/
        $words = str_word_count($string, 2);

        /*** get the first $num words ***/
        $firstwords = array_slice( $words, 0, $num);

        /** return words in a string **/
        return  implode(' ', $firstwords).$tail;
}

 /*** a string ***/
 $string = 'Heather was hoping to hop to Tahiti to hack a hibiscus to hang on her hat';

 /*** get the first 5 words ***/
 echo first_words( $string, 5);

?>


also with MySQL query
Code:
SELECT
animal_id,
animal_name,
SUBSTRING_INDEX(animal_description,' ', 15) AS first_fifteen
FROM animals
jmraker
The problem with many paragraph summary functions is that they fail with HTML like a routine to get the first 4 letters of '<b><u>a</u>bcdef</b>' would return '<b><' instead of '<b><i>abcd</i></b>'. Where just printing a '<b><' would ruin the page

I made a function that does that (but it has to be valid well formed HTML)
http://www.ecgdev.com/jmraker/truncate.php

It uses preg_split('/[<>]/', $html, -1, PREG_SPLIT_NO_EMPTY|PREG_SPLIT_OFFSET_CAPTURE)
as a basic HTML parser.
Related topics
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.