[SOLVED] query length

[onthespot]

Hey guys, i have searched high and low online for an answer to this but possibly am not searching for the correct thing.

 

I am looking to limit the amount of characters shown by a mysql_query.

For instance if various fields that are returned are all different lengths, I want just the first 10 characters to show and then ......, of course I will provide a link to click to reveal the rest.

 

$res=mysql_query("SELECT * FROM ".TBL_COMMENTS." ORDER BY commentdate DESC LIMIT 5") or die(mysql_error);

while($row=mysql_fetch_assoc($res)){

$comment=$row['comment'];

 

That is what I have so far, I have managed to bring up the 5 most recent comments. If you know how I could achieve this, I would bre grateful of a push in the right direction, thankyou

[ignace]

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

[aggrav8d]

What I understand you to mean is that you are pulling a set of comments from a database and you want to show a truncated version of those comments, with a link to show more.

$res=mysql_query("SELECT * FROM ".TBL_COMMENTS." ORDER BY commentdate DESC LIMIT 5") or die(mysql_error);
while($row=mysql_fetch_assoc($res)){
  $comment=$row['comment'];
  if(strlen($comment)>$your_max_length-3) {
    $x=$your_max_length/2;
    $short_c=substr($comment,0,$x-3).'...'.substr($comment,-$x);
    echo "<p onclick='javascript_reveal_full_comment();'>$short_c</p>";
    echo "<p class='hidden_full_comment'>$comment</p>";
  } else {
    echo "<p>$comment</p>";
  }
}

 

This is off the top of my head, but should do the trick.  You have to supply your own CSS and javascript.

[onthespot]

Hey, that is almost what Im after, but the click isnt to reveal more, its just to go to another page with the full comment. Don't think I need javascript?

[Philip]

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

 

SELECT SUBSTRING(comment, 10) as comment, (rest of your values you need) FROM ".TBL_COMMENTS." ORDER BY commentdate DESC LIMIT 5

[aggrav8d]

onthespot:  yeah, if you want to send them to another page then you don't need the javascript.  You can use kingphilip's solution to get the truncated string and use a regular query to get the full string on the other page.

[onthespot]

Thats really helpful, but its cutting the start of the comment?

Showing the last 10 characters, rather than the first 10

[Philip]

Oops, sorry haven't used it in a while in mysql. Try:

SELECT SUBSTRING(comment, 1, 10) as comment, (rest of your values you need) FROM ".TBL_COMMENTS." ORDER BY commentdate DESC LIMIT 5

[onthespot]

that works a treat, very much appreciated, thankyou