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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Did you try it in MySQL console (CLI)?
There are 10 types of people in this world, those who understand binary and those who don't
NewDevGuy
Forum Newbie
Posts: 15
Joined: Thu Jan 15, 2009 6:24 am

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

Post by NewDevGuy »

No. I figured that I'll be using phpMyAdmin to create most of my projects, and the results will be output through a browser, so whilst I can make it work through writing a seperate SQL query (>725) I was more interested in seeing why it isn't functioning as it should (either through the browser or using a number of methods in phpMyAdmin). So, running the same query through the CLI won't really answer the question I'm asking - i.e. why is phpMyAdmin not displaying the full record set returned from a simple 'select all' query.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

If we know it's a PHPMyAdmin problem and not a MySQL engine problem, it will be a lot easier for us to help you ;)
There are 10 types of people in this world, those who understand binary and those who don't
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Only 725 records being returned?

Post by mattpointblank »

onion2k wrote: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.
Seconding this. Give us a table dump?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Only 725 records being returned?

Post by VladSun »

mattpointblank wrote:
onion2k wrote: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.
Seconding this. Give us a table dump?
NewDevGuy said that "select count(*)" returned 753 ...
There are 10 types of people in this world, those who understand binary and those who don't
NewDevGuy
Forum Newbie
Posts: 15
Joined: Thu Jan 15, 2009 6:24 am

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

Post by NewDevGuy »

Ok VladSun,
That make's sense, I'll run it through the CLI and post result.
NewDevGuy
Forum Newbie
Posts: 15
Joined: Thu Jan 15, 2009 6:24 am

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

Post by NewDevGuy »

Right - weirdness ensued! The db was running on a hosting server which I had no access to the CLI on, so I exported it from the host and imported it to test on my local machine...and the exact same thing is happening! Right down to the fact that when I select ALL, member 1-725 show up, and when I specify (using a seperate query) all members with an ID greater that 725, numbers 726 to 753 show up. There is no difference between member 725 and member 726, and there should therefore be no break in the result set. And yet there is. Also, I'm using different versions of both MySql and phpMyAdmin (locally and remotely). Don't know if they'll help at all, but I'll pop the version numbers up as well:

Local MySql version: 5.0.51a-community-nt
Local phpMyAdmin ver. 2.11.4
Remote MySql version: 5.0.67-community
Remote MySql ver. 2.11.9.4
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Do you get the expected results with this query (run in the CLI):
[sql]SELECT MemID FROM members[/sql]
and please post the result here - including the MySQL messages - there is nothing secret in the results ;)
There are 10 types of people in this world, those who understand binary and those who don't
NewDevGuy
Forum Newbie
Posts: 15
Joined: Thu Jan 15, 2009 6:24 am

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

Post by NewDevGuy »

Ok, using the CLI (locally) MySql displays members up to number 725, and says just below the readout:

753 rows in set (0.00 sec)

If I use the SELECT * FROM members WHERE MemID > 725; (again, in the CLI) the remaining 28 records are returned, followed by:

28 rows in set (0.00 sec)

And just to prove there no problem with the records I popped in SELECT * FROM members WHERE MemID > 710 AND MemID < 754; When I ran this I got back members 711 to 753, followed by:

43 rows in set (0.00 sec)

And, just to be sure (which I already was) I popped in: SELECT COUNT(*) FROM members; which returned 753.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Which query did you run:
[sql]SELECT MemID FROM members[/sql]
or
[sql]SELECT * FROM members[/sql]

I want you to try the first one and post the results.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

NewDevGuy wrote:Ok, using the CLI (locally) MySql displays members up to number 725, and says just below the readout:

753 rows in set (0.00 sec)
Obviously you have duplicated or NULL MemID values ...
There are 10 types of people in this world, those who understand binary and those who don't
NewDevGuy
Forum Newbie
Posts: 15
Joined: Thu Jan 15, 2009 6:24 am

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

Post by NewDevGuy »

When I run SELECT MemID FROM members; I get MemID up to 753 returned, followed by: 753 rows in set (0.00 sec). MemID is the Primary Key, and set to auto-increment, so there's no duplicates or null values.
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

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

Post by mattpointblank »

Go through your entire result set, row by row, and count the ID numbers. Scan for a jump, eg:

1
2
3
--SNIP--
10
11
12

I bet there's a few.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

VladSun wrote:I want you to try the first one and post the results.
is it so hard to do it...

EDIT: Sorry - I dodin't notice you post above.
Last edited by VladSun on Fri Jan 23, 2009 8:18 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Just in case - try
[sql]SELECT * FROM members ORDER BY MemID[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply