Menu
I'd like to be able to pull the first X words from a database field for use in a preview. Basically if a field 's content was
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris malesuada."
I would like to echo
"Lorem ipsum dolor sit amet... see more"
What's the best way to do this?
The only thing I know to do is pull the whole field in a query then do something like
$foo = [query_results];
$bar = explode(' ', $foo);
for($x=0, $x<6, $x++){
echo $bar[$x];
};
echo "... see more"
Is there a better approach to this?
999
To select first word in MySQL query, you can use SUBSTRING_INDEX().
To fetch the first alphabet from the strings, use LEFT(). This method allows you to return characters from the left of the string.
In MySQL, we use SUBSTRING_INDEX() to split the string. It usually consists of three arguments i.e., string, delimiter, and position. The string value will be split based on the position.
LOCATE() function MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start.
You definitely want to use SUBSTRING_INDEX which will return some number of characters until a specified count is achieved based on the occurrence of a delimiter. In your case the call would look like this:
SELECT SUBSTRING_INDEX(text_field, ' ', 6) FROM ...
In particular, this will return up to six words where we define a word as a set of characters that are not spaces delimited by spaces.
Note: this will return punctuation attached to the last word, which may or may not be desired. It'd be simple enough to replace any punctuation characters at the tail of the string in PHP, but if you want to stay completely within SQL I think you can use TRIM. The syntax for that would be something like:
SELECT TRIM(TRAILING ',' FROM SUBSTRING_INDEX(text_field, ' ', 6)) FROM ...
There may be a better option for removing the trailing punctuation -- but perhaps that's another question (I'm still looking for a better solution than TRIM).
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
