Limited queried text

Anon

I like to limit the text I queried from a table field.

Eg.

Input field A is 'I like the penguin. I love eating. And I'm un-cool.'

Desired output is 'I like the penguin. I love eating...'

I don't know if there's function that can the limit the text of output or the query.

Typically, I'd query out field A and output is the whole field A. But this one exception.

Hope I made sense.

Anon

Manuals really are cute.
Most databases support the substring() and length() clauses.
You can use those to see if a field is longer than x chars, and retreive only y chars from it.
Then you can also use the concat function to append "..."
to the end of it.
This is all a one-query action.

Anon

Yep...something like this for MySQL

SELECT IF(LENGTH(your_text)>20,CONCAT(LEFT(20),'...'),your_text) FROM your_table;

I'm not sure if it's LEN or LENGTH, though. To lazy to look it up.. 🙂

www.mysql.com/documentation/mysql/bychapter

Chapter 7.4.11

I have that much memorized, though...

---John Holmes...

Anon

Actually, no reason to even use the IF statement. Regardless of whether or not the field is longer than 20 char's, it still works. I use the same exact function in one of my queries.

Anon

Thanks a lot guys !

Anon

Sorry guys,

your_text should replaced by ?

The field name that I'm quering from ?

I've had already queried the field that I wish to limit the length.

Lets put it this way, the queried field is $values['text_to_limit']

How shall I apply this to the query you guys suggest ?

Appreciate the help.

Anon

Sure it works, but you only want the "..." to be added if the string is over 20 chars.
I mean, you only want to have the "..." after the string if the string is truncated.

Anon

Exactly. Thank you.

Anon

Come on, this isn't that hard:

SELECT IF(LENGTH(text_to_limit)>20,CONCAT(LEFT(20),'...'),text_to_limit) FROM your_table;

your_table should of course be replaced with the name of your table. 🙂

---John Holmes...

Anon

I've had problems all night with this.
It's so frustrating because I need to be able to do this.

Lets say I have a field 'album' in a table 'new'. I have already selected everything from the table, but I need to parse the 'album' into the page. Normally this is done by " . $row["album"] . ". How would I limit this text?

Help would be very much appreciated

Anon

Mark, the answers you were previously getting were to alter your QUERY to the database. That way, when you retrieve $row["album"] it woul already contain exactly what you need. If your query is already complicated and you don't want to alter that, you will have to use your server-side language to grab the first 20 chars. I'm assuming you're using PHP, just visit php.net and lookup the string functions you need. I believe it's something like:

if (strlen($row["album"] > 20)) {
  $album = substr($row["album"], 0, 20) . ". . .";
} else {
  $album = $row["album"];
}

That should do it.