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

I have some code which retrieves user comments fro开发者_如何转开发m 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>"; 
    }

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

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";
  ...