How to count all records, but only get (LIMIT) a specific number to display?

Question

I want to highlight only the first 10 rows, but I need to calculate the total number of rows affected by the query.

I did LIMIT 10and then counted with the obvious problem, which I was getting 10 as a bill.

What would be the right way to do this?

$data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC");
$count = mysql_num_rows($data);

while($row = mysql_fetch_array( $data )) 
    { 
    echo $row['Site'];
    }

Answer 1

MySQL has some support for these kinds of things. First include SQL_CALC_FOUND_ROWSin your SELECT:

SELECT SQL_CALC_FOUND_ROWS *
FROM Badges
WHERE UID = '$user'
ORDER by Date DESC
LIMIT 10 -- Or whatever

Then pull out your lines, and then immediately look FOUND_ROWS()like this:

SELECT FOUND_ROWS()

to get the number of rows matching your original query without considering the LIMIT clause.

MySQL, , .

Answer 2

: limit, .

$countQuery = 'SELECT COUNT(1) FROM Badges WHERE UID = ?';
$limitQuery = 'SELECT * FROM Badges WHERE UID = ? ORDER BY `Date` DESC LIMIT 0, 10';

Answer 3

2 : , - 10 :

$count = 0;
$query = "SELECT count(*) as count FROM Badges WHERE UID = '$user'";
$rs = mysql_query($query);   

if (mysql_errno() == 0)
{
    $r = mysql_fetch_object($rs);
    $count = $r->count;
}

if ($count > 0)
{
    $query = "SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC LIMIT 10";
    $rs = mysql_query($query);      

    if (mysql_errno() == 0)
    {            
        while ($r = mysql_fetch_array($rs))       
        {       
            echo $r['Site']; 
        }     
    }
}

Answer 4

$data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC");
$count = mysql_num_rows($data);
echo "No of Records is :" . $count;

10 ...

 $data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC LIMIT 0, 10");
    while($row = mysql_fetch_array( $data )) 
        { 
        echo $row['Site'];
        }