Checking If MySQL Rows Returned>10 and if so running some code, if not running a different piece of code

I have the code below which is supposed to check the database for entries with a certain username which is working but when I try to add some code to check if the rows returned is greater than 10 and then run some code to limit the number of rows to 10 and then if not run another piece of code which will display all the rows.

Code:

    mysql_select_db("blahblah", $con); //connect to database..
    $username =  basename(dirname(__FILE__));
    $username = mysql_real_escape_string($username);
    $checkres = mysql_query("SELECT COUNT link, notes, titles, color FROM links WHERE username='" . $username . "';");
    if ($checkres>10)
    {
    $resultsmall = mysql_query("SELECT link, notes, titles, color FROM links WHERE username='" . $username . "' LIMIT 10;");
    while ($rowsmall = mysql_fetch_array($resultsmall)) { //loop
      extract($rowsmall);
      $htmlsmall .= "
//code goes here to format results
                ";
    echo $htmlsmall; //display results...
    }
    }
    else {
    $result = mysql_query("SELECT link, notes, titles, color FROM links WHERE username='" . $username . "';");
    while ($row = mysql_fetch_array($result)) { //loop
      extract($row);
      $html .= "
            //code goes here to format results
                ";
    echo $html; //display results...
    }
    }
    mysql_close($con); //close db..

But it just displays two times the number of rows in the database instead of either limiting it or displaying them all. How would I fix this? The //code goes here for formatting isn't important it just formats the code so that it looks nice and displays it in a box...

Thanks!

EDIT:

I now have this code but it now doesn't display anything at all? Can someone help:

Code:

mysql_select_db("blahblah", $con); //connect to database..
$username =  basename(dirname(__FILE__));
$username = mysql_real_escape_string($username);
$sql = "SELECT SQL_CALC_FOUND_ROWS link, notes, titles, color FROM links WHERE username='" . $username . "' LIMIT 10";
$result = mysql_query($sql) or die("MySQL error: " . mysql_error());

$subsql = "SELECT found_rows() AS foundrows;";
$subresult = mysql_query($subsql) or die("MySQL error: " . mysql_error());
$found_rows = $subresult['foundrows'];

if($found_rows > 10)
{
  while($row = mysql_fetch_array($result))
  {
    extract ($row);
    $html .= "
      //getting values from columns and then formatting them goes here
    ";                                             

    echo "Only 10 is allowed.";
  }
} 
else
{
  while($row = mysql_fetch_array($result))
  {
    extract($row);
    $html .= "
      //getting values from columns and then formatting them goes here
    ";
  }
}
mysql_close($con); //close db..

Thanks!

EDIT 2 (12 April 14:03 2011 GMT):

I now have this code which has been kindly helped by Col. Shrapnel but it doesn't display anything for me and I don't know why, please could some help.

Code:

mysql_sel开发者_JS百科ect_db("blahblah", $con); //connect to database..
$username =  basename(dirname(__FILE__));
$username = mysql_real_escape_string($username);
$sql = "SELECT link, notes, titles, color FROM links WHERE username='$username' LIMIT 10";
$res = mysql_query($sql);
if (mysql_num_rows() == 10) {
while ($row = mysql_fetch_array($res)) {
  extract($row);
  $htmlsmall .= "

= "; $htmlfree .= "Only 10 is allowed."; echo $htmlsmall; echo $htmlfree; } } else { while ($row = mysql_fetch_array($res)) { extract($rowsmall); $htmlsmall .= "

  ";
echo $htmlsmall;
}
}

Now if I view the page source I can see these 2 errors:

<b>Warning</b>:  Wrong parameter count for mysql_num_rows() in <b>//url was here</b> on line <b>109</b><br /> 


<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>//url to file goes here</b> on line <b>125</b><br /> 

Line 109 is this: if (mysql_num_rows() == 10) {

Line 125 is this: while ($row = mysql_fetch_array($res)) {

(Those lines are in the code above)

Please could someone help me with this?

Thanks!

mysql_query() returns either a result statement handle, or boolean FALSE if the query failed. Your outer query is invalid:

SELECT COUNT ...

does not work, so the query fails, returns false, which jumps to the lower "unlimited" query.

However, you're going about the process of limiting things in a roundabout fashion that forces the query to run at least twice.

What you want is

$sql = "SELECT SQL_CALC_FOUND_ROWS link, notes, etc.... LIMIT 10";
$result = mysql_query($sql) or die("MySQL error: " . mysql_error());

$subsql = "SELECT found_rows() AS foundrows;";
$subresult = mysql_query($subsql) or die("MySQL error: " . mysql_error());
$found_rows = $subresult['foundrows'];

if ($found_rows > 10) {
   ... there's more than 10 rows available, though we're fetching only 10 because of the LIMIT
} else {
   ... 10 rows or less
}

The SQL_CALC_FOUND_ROWS is a MySQL extension that forces MySQL to calculate how many rows would have been fetched, if the LIMIT clause had not been present. You can get the results of this calculation with the found_rows() query function.

This way, you only run the main query once, do the very very quick found_rows() query, and off you go.

Given that you don't seem to be formatting your output any differently between the "more than 10" and "10 or less" version, except for the "only 10 allowed" line, how about this:

$sql = "...";
$result = mysql_query($sql) or die(mysql_error());

$rowcount = 0;
while ($row = mysql_fetch_assoc($result)) {
    $rowcount++;
    ... output a row ...
    if ($rowcount > 10) {
        echo "Only 10 allowed";
        break;
    }
}

There's no need for duplicated code, excess logic, etc... when something simple will do.

I try to add some code to check if the rows returned is greater than 10 and then run some code to limit the number of rows to 10

It makes no sense.
Just run your query with LIMIT 10 and you will get your data.

mysql_select_db("blahblah", $con); //connect to database..
$username =  basename(dirname(__FILE__));
$username = mysql_real_escape_string($username);
$sql = "SELECT * FROM links WHERE username='$username' LIMIT 10";
$res = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
while ($row = mysql_fetch_array($res)) {
  extract($rowsmall);
  $htmlsmall .= ""; //code goes here to format results
}
echo $htmlsmall;

That's all. If you still want to echo "Only 10 is allowed." (dunno why though) you can add these 3 lines below

if (mysql_num_rows($res) == 10) {
  echo "Only 10 is allowed.";
}

However I see not much point in it.

Also note that your program design is probably wrong, as you're apparently copying this file for the every user in your system

To get number of rows returned in result you must use mysql_num_rows.

if (mysql_num_rows($checkres)>10)