[RESOLVED] How to Preview a Database Call

SportinStyle

For example, if I had a CMS article system (which I do), how would I limit the amount of words pulled out onto the main page. That is, EX:

The native speaking indians chose to watch from afar. The cowboys looked feral with their guns ablaze... (Read Full Story)

Any ideas?

Thanks!

Drakla

Well, you could pull up a limited amount of characters with MySQL's LEFT() function

SELECT LEFT(article, 120) as preview FROM...

But be wary if you've got any html stored that can cause problems, so you might have to tidy up what it returns... or you could return a limited number of words, with SUBSTRING_INDEX

The format is:
[INDENT]SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

This function is multi-byte safe.[/INDENT]
So you could use spaces as the delimiter

SELECT SUBSTRING_INDEX(article, ' ', 30) as preview FROM...

Again, stored html might have to be cleared from it.

SportinStyle

Thanks for the quick answer.

So, I would put that into query as

$query = "SELECT SUBSTRING_INDEX(thearticle, ' ', 30) as preview FROM article_table";

mysql_query($query) ;

Where the parameters would be (1, 2, 3) 1 = articlename , 2 = start of my pull, 3 = number of characters?

Thanks!

Drakla

If you used substring index you'd pass the number of words you wanted to return. Don't know what your database fields are but it would be something like this for the first 30 words

SELECT SUBSTRING_INDEX(thearticle, ' ', 30) as preview FROM article_table WHERE articlename = 'some thing';

Or if you had dates and wanted the most recent 5

SELECT SUBSTRING_INDEX(thearticle, ' ', 30) as preview FROM article_table ORDER BY somedatefield DESC LIMIT 5;

That kind of thing.

SportinStyle

Thanks a ton for that. Helped more than you know.