Page 2 of 3
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 4:58 am
by VladSun
Did you try it in MySQL console (CLI)?
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 5:20 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 5:21 am
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

Re: Only 725 records being returned?
Posted: Fri Jan 23, 2009 5:43 am
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?
Re: Only 725 records being returned?
Posted: Fri Jan 23, 2009 5:48 am
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 ...
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 5:56 am
by NewDevGuy
Ok VladSun,
That make's sense, I'll run it through the CLI and post result.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 6:47 am
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
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 7:06 am
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

Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 7:18 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 7:22 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 7:24 am
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 ...
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 8:04 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 8:10 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 8:11 am
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.
Re: Only 725 of 753 records being returned by select all??
Posted: Fri Jan 23, 2009 8:17 am
by VladSun
Just in case - try
[sql]SELECT * FROM members ORDER BY MemID[/sql]