Page 1 of 3

Only 725 of 753 records being returned by select all??

Posted: Thu Jan 15, 2009 6:35 am
by NewDevGuy
Hi,
I'm having a really unusual problem - I have 753 records in a table, but only the first 725 are being returned through a regular select * query, but if I do a select * where memberID > 725 all of the remaining records show up as normal. Even using the show command in phpMyAdmin to show 1000 records starting from 0, only 725 are showing up! Have tried running a few different queries on this, but the only one showing the remaining records (i.e. from 726-753) is the select * from members where memberID > 725; If anyone can help I'd greatly appreciate it.

Re: Only 725 records being returned?

Posted: Thu Jan 15, 2009 6:40 am
by jaoudestudios
Post your query that is giving you issues.

How many megs is your table in the database?

Re: Only 725 records being returned?

Posted: Thu Jan 15, 2009 6:48 am
by NewDevGuy
SELECT * FROM `members`;

It's showing up the first 725 members, but there are 753 members in the table.
And what's even weirder is that phpMyAdmin is saying that it's showing all 753 members, when in actual fact it's not, it's only showing 725 of them.

And the table size is only 31.7kb

Re: Only 725 of 753 records being returned by select all?

Posted: Thu Jan 15, 2009 8:42 am
by jaoudestudios
That is a little strange.

Can you do a database dump? and post your relevant code.

Re: Only 725 records being returned?

Posted: Thu Jan 15, 2009 8:47 am
by onion2k
NewDevGuy wrote:there are 753 members in the table.
Are you basing that on the fact the highest member id is 753?

Re: Only 725 records being returned?

Posted: Thu Jan 15, 2009 9:06 am
by NewDevGuy
onion2k wrote:
NewDevGuy wrote:there are 753 members in the table.
Are you basing that on the fact the highest member id is 753?
No, although I see why you would ask that...no, there are 753 actual entries in the table, which is why this problem is so strange. No matter how I run the select all query (constructing a SELECT * FROM members; in the SQL window of phpMyAdmin, selecting show all records starting from 0, LIMIT 1000 using the phpMyAdmin 'Show' button, etc.), only the first 725 records are shown - but when I specify that I want only the records above that point, using a WHERE clause (MemID > 725) the records 726 to 753 are returned.

Re: Only 725 of 753 records being returned by select all??

Posted: Fri Jan 16, 2009 4:21 am
by NewDevGuy
Still unresolved - anyone got any ideas?

Re: Only 725 of 753 records being returned by select all?

Posted: Fri Jan 16, 2009 4:40 am
by jaoudestudios
jaoudestudios wrote:Can you do a database dump?
So I can run it on my server to compare.

Re: Only 725 records being returned?

Posted: Fri Jan 16, 2009 6:22 am
by onion2k
NewDevGuy wrote:
onion2k wrote:
NewDevGuy wrote:there are 753 members in the table.
Are you basing that on the fact the highest member id is 753?
No, although I see why you would ask that...no, there are 753 actual entries in the table, which is why this problem is so strange. No matter how I run the select all query (constructing a SELECT * FROM members; in the SQL window of phpMyAdmin, selecting show all records starting from 0, LIMIT 1000 using the phpMyAdmin 'Show' button, etc.), only the first 725 records are shown - but when I specify that I want only the records above that point, using a WHERE clause (MemID > 725) the records 726 to 753 are returned.
I reckon I'm right. There are 725 records. That doesn't mean the highest id will be 725. Dump the data with SELECT * FROM `members` and look at the ids at the end. The last one will be 753 despite there only being 725 rows. It's just that there have been 28 rows deleted.

Re: Only 725 of 753 records being returned by select all??

Posted: Fri Jan 16, 2009 9:42 am
by Apollo
What does SELECT COUNT(*) FROM YourTable bring ya?

Re: Only 725 of 753 records being returned by select all??

Posted: Fri Jan 16, 2009 10:59 am
by NewDevGuy
Yep, I had already done a count on the result set - which returned 753. The code I used for this was:

$memcount = mysql_query("Select COUNT(*) from members");
echo mysql_result($memcount,0);

Which worked perfectly, showing me that there are 753 records in there, but the records from 725 on aren't returned using a SELECT *, which is why I think this problem is so strange.

Re: Only 725 of 753 records being returned by select all??

Posted: Fri Jan 16, 2009 12:08 pm
by RobertGonzalez
Are you viewing the result set in phpMyAdmin, another client app or the command line?

Re: Only 725 of 753 records being returned by select all??

Posted: Mon Jan 19, 2009 3:42 am
by NewDevGuy
Both the phpMyAdmin pages (i.e. creating a SELECT * query in the SQL window, and using the 'Show' command on the Browse Table window to Show all, LIMIT 1000), and via a client app with a simple SELECT * query. I have also written a seperate script which just counts the records in the table - this returns 753.

Re: Only 725 of 753 records being returned by select all??

Posted: Mon Jan 19, 2009 9:19 am
by RobertGonzalez
Can you run

[sql]SHOW CREATE TABLE `tablename`;[/sql]

so I can your table structure? I am also interested in the field names for something I want to try.

Re: Only 725 of 753 records being returned by select all??

Posted: Fri Jan 23, 2009 4:56 am
by NewDevGuy
Sorry about the delay replying - was called away unexpectedly for a few days. Here's the result of the Show Create query:

CREATE TABLE `members` (
`MemID` int(11) NOT NULL auto_increment,
`MemName` varchar(50) NOT NULL,
`MemDOB` date NOT NULL,
`MemCpoints` int(11) NOT NULL default '0',
`MemTpoints` int(11) NOT NULL default '0',
PRIMARY KEY (`MemID`)
) ENGINE=MyISAM AUTO_INCREMENT=753 DEFAULT CHARSET=latin1

Hope this helps with what you want to check out, because that problem's not going away. There are 753 records shown using Count(*), and also in phpMyAdmin's result of SELECT *, but only MemID 1 to 725 are displayed - to see the rest I have to write SELECT * FROM members WHERE MemID > 725; and it displays the rest of the members.