Simple SQL - count function

Question

My MySQL table is simple. I have 3 fields:

• an index
• a url
• a title

making up 20 records.

I'm trying to count the number of rows so that I can paginate with PHP. But count(*) is returning more than the number of rows that are there, 142 total. I get the same results counting the index.

What am I missing?

edit

Sorry for the previous lack of information, quite frankly I'm embarrassed by all this and feel pretty stupid. But here goes.

Field: i, int(11), PRI, NOT NULL, auto_increment
Field: url, text
Field: title, mediumtext

The sql:

select count(*) from $table

The php:

require_once('highlights_db.php');

$query = "select count(*) from highlights";

$connection = mysql_connect($host, $user, $pass);
mysql_select_db($db);
$result = mysql_query($query, $connection);
$pages = mysql_fetch_array($result);
$pages = floor($pages);

for($i = 0; $i < $pages; $i++){
    echo "<a href=\"\" class=\"page_dot\"></a><br />\n";   
}

Answer 1

Try COUNT(i) or something similar.

Answer 2

How do you know so sure that there are 20 rows?

This query:

select * from $table

should return the same number of rows as this number tells you:

select count(*) from $table`

If it doesn't, your table has probably been updated in the meanwhile.

Answer 3

Would you happen to have duplicate records in your table and comparing COUNT(*) with the number of records returned by some sort of SELECT DISTINCT ?
That's about the only explanation I can see until you post the actual queries and their resultsets... (both the "count" one and the "list" one that made you see a discrepancy)

Answer 4

So your calling this from PHP? Can you provide a more complete code sample for our review. Your sql statement looks ok but I need a little more code before I can provide you an answer.

Answer 5

"$pages = floor($pages);" looks odd.Shouldn't it be:$pages = floor($pages);?

I.e.my guess is this is a PHP problem, not SQL.