MySQL/SQL retrieve first 40 characters of a text field?

Question

How can I retrieve a text field from mysql db table, but not the entire text, just the few 40 or so characters.

Can this be done in sql or do I need to do it using php?

basically what I am trying to do is show the first x characters and then let the user click on that to view the full content.

Answer 1

SELECT LEFT(MY_COLUMN, 40) FROM MY_TABLE

Function in the MySQL reference manual:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_length

Answer 2

SELECT LEFT(field, 40) AS excerpt FROM table(s) WHERE ...

See the LEFT() function.

As a rule of thumb, you should never do in PHP what MySQL can do for you. Think of it this way: You don't want to transmit anything more than strictly necessary from the DB to the requesting applications.

---

EDIT If you're going to use the entire data on the same page (i.e., with no intermediate request) more often than not, there's no reason not to fetch the full text at once. (See comments and Veger's answer.)

Answer 3

You could do this in SQL as the others shown already.

But, if you also want to show the full text if the user clicks on it, you also need to full text and it seems a waste to let the database send you the short and the full text. So you could grab the full text and write some code to show the short text and the full text when the user clicks on it.

$result = mysql_query('SELECT text FROM table');
$row = mysql_fetch_row($result);
echo '<div onclick="alert(\''.$row[0].'\');">'.substr($row[0], 0, 40).'</div>';

Ofcourse you could do something nicer when you click on it (instead of alert()). Also you could do some PHP checking now to see if the original is shorter than 40 characters and handle situations like this.