CODEWITHSUNDEEP
phpmysql
ngplayground
ngplayground

Reputation: 21627

Only show comments with lengths less than, php mysql

I have made a function that randomly pulls in 3 comments from my database. the only problem is sometimes I have a comment with 150 words and the 2 next to it have a sentence or around 50 words.

Would it be possible to only show comments with a maximum of 70 words?

  function getIndexComments(){
    $query  = "SELECT * FROM tbl_comments ORDER BY RAND() LIMIT 0,3";
                    $result = mysql_query($query);

    while($row = mysql_fetch_array($result)){
        echo "<li><span>".$row['company']."</span>".
        "\"".$row['comments']."\"" .
        "</li>";
    }   
}

Upvotes: 0

Views: 213

Answers (7)

Jake Stubbs
Jake Stubbs

Reputation: 247

Alternatively, you could truncate this with PHPs substr() function.

Something like this should do it, I think (untested):

function getIndexComments(){
    $query  = "SELECT * FROM tbl_comments ORDER BY RAND() LIMIT 0,3";
    $result = mysql_query($query);

    while($row = mysql_fetch_array($result)) {
        $comments = preg_replace('/\s+?(\S+)?$/', '', substr($row['comments'], 0, 70) . '...';

        echo '<li><span>' . $row['company'] . '</span>"' . $comments . '"</li>';
    }   
}

This will truncate the $row['comments'] to 70 words and append a '...'.

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

This solution will truncate comments longer than 70 words and add a ... after the truncation.

SELECT company, CONCAT(SUBSTRING_INDEX(comments, ' ', 70), '...') AS comments
FROM tbl_comments
ORDER BY RAND()
LIMIT 3

However, if you actually want to filter out comments that have > 70 words from the result-set, you can use:

SELECT *
FROM tbl_comments
WHERE ((LENGTH(comments) - LENGTH(REPLACE(comments, ' ', ''))) + 1) <= 70
ORDER BY RAND()
LIMIT 3

^ What this essentially does is count the number of spaces in the comments field and adds 1 to that count => giving us word count... then we check if that count is over 70, and if so, filter it out.

Upvotes: 3

PEM
PEM

Reputation: 1978

A solution might be to use REGEXP '.{0, 70}' or something like that :)

Upvotes: 0

sel
sel

Reputation: 4957

Below is to truncate comments to length of 70 characters without chopping the words.

SELECT
  LEFT(str, cutpos) AS str1
FROM (
  SELECT
    comments AS str,
    70 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(comments , 70))), 0) - 1, 0) AS cutpos
   FROM tbl_comments
) s

Upvotes: 0

Peon
Peon

Reputation: 8020

Why do you want to eliminate comments just because they are too long? I suggest you do an output that limits the comments length in display. Meaning, add ... to the comment if it's too long.

Put them all in divs and add this to it style:

overflow: hidden;
white-space: nowrap;
text-overflow: ellipsis;
width: 200px;

Hope that helps.

Upvotes: 0

Gntem
Gntem

Reputation: 7165

you can leave CSS to handle the rendering using 

text-overflow:ellipsis;

this will add dots and with a fixed width/height of the container you can render as you wish

Upvotes: 0

Sherlock
Sherlock

Reputation: 7597

If you define a word as a string separated by a whitespace, something similar to this would work:

SELECT x FROM y WHERE SUM( LENGTH(x) - LENGTH(REPLACE(x, ' ', ''))+1) < 70

You could expand it by adding commas, periods, etc.

Upvotes: 0

Related Questions