MYSQL select comments, limit 3, 'click to see all' and display on same page. PHP

Question

I have some code which retrieves user comments from my database:

$comments = mysql_query("SELECT * FROM comments WHERE ref = '$theID'LIMIT 0, 3;")
  or die(mysql_error());

while ($rowC = mysql_fetch_array($comments)) {
  echo "<p>On " .$rowC['date']. ", ";
  echo $rowC['username']. " said: <br/>";
  echo $rowC['comment'];
  echo "</p><hr/>";
}
if (mysql_num_rows($comments) == 0) { 
  echo "<p>(No comments have been made yet)</p>"; 
}

Comments are stored with a unique user reference in the database, and retrieved where they match the user id, this is called at the top of the page:

$theID = $_GET['id'];

What I am trying to do is limit the comments shown, and if there are more than 3, show a 'click to see more' type button or link which displays all the user comments on the same page in the same way as above.

UPDATE, i am now using this, Trying to implement Johan's suggestion, but still cant get it to display more when link is clicked:

  $comments = mysql_query("SELECT * FROM comments WHERE ref = '$theID' LIMIT 0, 4") or die(mysql_error());

    while ($rowC = mysql_fetch_array($comments)) {
      echo "<p>On " .$rowC['date']. ", ";
      $username = htmlspecialchars($rowC['username']). " said: <br/>";
        echo $username;
        $comment = htmlspecialchars($rowC['comment']);
        echo $comment;
      echo "</p><hr/>";
    }

    $num_rows = mysql_num_rows($result);
    if ($num_rows > 3) {
    $query = "SELECT * FROM comments WHERE ref = '$theID' LIMIT 4, 20";
    } echo "<p><a href=''>click to see more</a></p>";

    if (mysql_num_rows($comments) == 0) { 
      echo "<p>(No comments have been made yet)</p>"; 
    }

simply do it with PHP and jQuery – xkeshav May 08 '11 at 15:04 — xkeshav, May 08 '11 at 15:04

bitfox · Accepted Answer · 2011-05-08T16:00:15.860

0

Try this:

<?

if($_GET['allcomments'] == 1 && preg_match("/([0-9]+)/", $_GET['id'])){

   $comments = mysql_query("SELECT * FROM comments WHERE ref = '".$_GET['id']."'") or die(mysql_error());

   for($c=0; ($rowC = mysql_fetch_array($comments)) !== FALSE; $c++) {
       echo "<p>On " .$rowC['date']. ", ";
       echo htmlentities($rowC['username']). " said: <br/>";
       echo htmlentities($rowC['comment']);
       echo "</p><hr/>";
   }

}else{


$comments = mysql_query("SELECT * FROM comments WHERE ref = '$theID' LIMIT 0, 4;" ) or die(mysql_error());

for($c=0; ($rowC = mysql_fetch_array($comments)) !== FALSE; $c++) {
    echo "<p>On " .$rowC['date']. ", ";
    echo htmlentities($rowC['username']). " said: <br/>";
    echo htmlentities($rowC['comment']);
    echo "</p><hr/>";
    if($c == 3){
       echo "<p><a href='?allcomments=1&id=$theID'>click to see more</a></p>";
       break;
    }
}

if (!$c) { 
   echo "<p>(No comments have been made yet)</p>";
}

}
?>

I consider that $theID variable contains only digits from 0 to 9. If not, change the regular expression (preg_match).

edited May 08 '11 at 16:00

answered May 08 '11 at 14:58

bitfox

2,281
1
18
17

@bitfox, you have a XSS security hole in `echo $rowC['comment'];` – Johan May 08 '11 at 15:07
Tried this, displays link, but doesn't display anymore comments when clicked, when user ref in database is over 10, it still only displays 4 comments. – Ed Tibbitts May 08 '11 at 15:12
@Johan: About XSS security hole... I modified the code by adding htmlentities function. I supposed that the comments were sanitized before during the INSERT operation, so the data into database must be sanitized. @user736338: Ok... so, you want the visualization of all comments, too. No problem, I'll update the code in a few minutes :-) – bitfox May 08 '11 at 15:23
@bitbox, you forgot to sanitize the `username`:-) you're making assumptions that are not likely to be valid, looking at the problems in the OP's code. – Johan May 08 '11 at 15:34
@Johan: Now, also the username is sanitized. My assumptions start by consider previous checks which must be done in well written code :-) – bitfox May 08 '11 at 15:49

score 0 · Answer 2 · edited May 23 '17 at 12:19

Yikes: You have a couple of error/issues.

SQL-injection

$theID = $_GET['id'];

Fix this to

$theID = mysql_real_escape_string($_GET['id']);

To get rid of a gaping SQL-injection hole.
See: How does the SQL injection from the "Bobby Tables" XKCD comic work?

XSS vulnerability

Replace this code:

echo $rowC['username']. " said: <br/>";
echo $rowC['comment'];

With this

$username = htmlspecialchars($rowC['username']). " said: <br/>";
echo $username;
$comment = htmlspecialchars($rowC['comment']);
echo $comment;

See: Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?
And: When is it Best to Sanitize User Input?

Error in code
Change this:

$comments = mysql_query("SELECT * FROM comments WHERE ref = '$theID'LIMIT 0, 3;")

To this

$comments = mysql_query("SELECT * FROM comments WHERE ref = '$theID' LIMIT 0, 3")

Back to the question

if you change the query to:

SELECT * FROM comments WHERE ref = '$theID' LIMIT 0, 4"

Then you can check the number of results returned. If it's 4 then display the more... button.

Use this query to get 20 more results

$num_rows = mysql_num_rows($result);
if $num_rows > 3 {
  $query = "SELECT * FROM comments WHERE ref = '$theID' LIMIT 4, 20";
  ...

MYSQL select comments, limit 3, 'click to see all' and display on same page. PHP

2 Answers2