37

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.

j08691
  • 204,283
  • 31
  • 260
  • 272
John
  • 1,059
  • 4
  • 12
  • 16

5 Answers5

60
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.)

Community
  • 1
  • 1
jensgram
  • 31,109
  • 6
  • 81
  • 98
  • The full version is required when the user clicks on it, so now you are transmitting more info: the short string **and** the full string! Thus breaking your own rule of thumb... – Veger Jan 17 '10 at 12:13
  • @Veger Not necessarily. You may be right, but OP just mentions a "click" - it could be a link to a page with the full text (e.g. a news item) in which case you don't want to fetch it all in order to just generate a list of teasers. In other words: Given the amount of detail I cannot give *the* best way to do it, just provide a rule of thumb (which are - in their nature - often, but not always, right). – jensgram Jan 17 '10 at 12:18
  • on the click the user will move to another page, basically on the home page of my website I will allow users to see the latest posts to a forum then if they click on it, they will go through to the forum post. So it is a lot like a news page where you click to go through to the whole article. – John Jan 17 '10 at 12:38
  • 1
    @John In that case, you are covered by the rule of thumb: Use `LEFT()` in your home page query. – jensgram Jan 17 '10 at 13:14
5
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_left

Cocowalla
  • 13,822
  • 6
  • 66
  • 112
3

try this...
SELECT LEFT(field name, 40) FROM table name WHERE condition for first 40 and
SELECT RIGHT(field name, 40) FROM table name WHERE condition for last 40

jeswin
  • 382
  • 4
  • 9
1

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.

Veger
  • 37,240
  • 11
  • 105
  • 116
  • Exactly! If the entire text is to be used more often than not, there is no reason *not* to fetch it all at once. – jensgram Jan 17 '10 at 12:16
  • Presumably though John wants the short text because the user will only want the short text for most rows and maybe only want the full text for a few rows? Either that or the user will click to view a new page with other information. – Cocowalla Jan 17 '10 at 12:26
0

Check this one as well,

 mysql_query('SELECT LEFT('your text/fieldname', 40) FROM tablename');
Mahendra Jella
  • 5,450
  • 1
  • 33
  • 38