row COUNT issue (beginner question)

[Sigg]

My table has 250 rows, but COUNT only seems to see 162 of them.  In phpMyAdmin, for instance, there is a notice in the browse tab: "Showing rows 0-161 (162 total, Query took 0.0030 sec)".  On one of my php pages, I get the same 162 result from this attempt to count the number of entries to my auto-incrementing "id" column :

$result = mysql_query('SELECT COUNT(id) AS id_count FROM MyTable)';

Can anyone suggest the kinds of things I should check/adjust to understand why about 90 rows are not being counted?  Many thanks in advance for any help anyone can offer.

[Sigg]

I should note that I get the same 162 result trying to COUNT any of the columns, not just "id".

Edited October 4, 2014 by Sigg

[mac_gyver]

make sure the database server, database, and table are the correct ones.

 

some possibilities -

 

1) you have multiple database servers and you are connecting to the wrong one.

 

2) you have multiple databases and you are selecting the wrong one. note: on case-sensitive operating systems, the database name is case sensitive, so Database_name and database_name would refer to two different databases.

 

3) same as for #2, but with respect to the table name, including the note about case-sensitivity of the table name.

 

how do you know you have a table with 250 rows? perhaps some rows didn't get imported/inserted? perhaps some rows got deleted?

[Sigg]

Table and database name are all working correctly, but when I browse the table in phpMyAdmin, I can see the 250 rows that I have entered already, but I have a sense that some were either originally entered or imported incorrectly. 

 

The data _looks_ ok now, but I wonder if there is some way I can diagnose the error. 

[Frank_b]

what do you get if you change the query to:

$result = mysql_query('SELECT COUNT(*) AS id_count FROM MyTable)';

What is the database storage type? (e.g. InnoDB or MyISAM)

[Barand]

Do any of your columns contain null values - these are ignored by aggregation functions eg

mysql> SELECT * FROM contact;
+----+-------------+-------------+-------------+
| id | name        | email       | phone       |
+----+-------------+-------------+-------------+
|  1 | aaaaaaaaa   | aaa@xyz.com | 12345674321 |
|  2 | bbbbbbbb    | b@abc.co.uk | NULL        |
|  3 | cccccccc    | ccc@zzz.com | 01214567890 |
|  4 | dddddd      | dd@dd.com   | NULL        |
|  5 | eeeeeeeeeee | eee@efg.com | NULL        |
|  6 | kkkkkkkkk   | kkk@aaa.com | NULL        |
|  7 | mmmm        | m@mmm.co.uk | NULL        |
+----+-------------+-------------+-------------+
7 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM contact;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(phone) FROM contact;
+--------------+
| COUNT(phone) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

[Sigg]

 

Do any of your columns contain null values - these are ignored by aggregation functions eg ...

 

I don't see any null values - and the column I am trying to count (my primary, auto-incrementing column - "id"), is set to non-null.  All of the other columns are set to NULL if there isn't a value, and they all appear to be filled-in correctly.  However,  I had attempted to import a .csv a week ago, and it didn't go well- I had to make adjustments. 

 

Everything is adjusted, and everything looks ok, but it's still giving an incorrect count.

 

I tried to change the "COUNT(id)" to "COUNT(*)", and it is still giving the same 162 total.  Thank you for your help!

Edited October 4, 2014 by Sigg

[Barand]

It would seem there are are only 162 records

[Sigg]

what do you get if you change the query to:

$result = mysql_query('SELECT COUNT(*) AS id_count FROM MyTable)';

What is the database storage type? (e.g. InnoDB or MyISAM)

 

Not sure what happened in my previous post. 

 

Changing the query to COUNT(*)  gives the same 162 count.  The storage-type is MyISAM.

Edited October 4, 2014 by Sigg

[Sigg]

Problem solved - there are 162 records; an import hadn't auto-incremented the id column value correctly, so id's 30-108 weren't there.