LIMIT isn't working prperly

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

LIMIT isn't working prperly

Post by evilmonkey »

Hello,
I have a big problem with LIMIT, and it's frustrating the heck out of me. Basically, I need to display 12 results at a time. My database has 18 entries (at the moment). This is a user search. Below are entries from my actual database, copied and pasted through phpmyadmin:

first without the limit:

Code: Select all

SELECT `id` , `age` , `username` , `gender`
FROM `users`
WHERE id >0
AND `username` LIKE '%%'
ORDER BY `popularity` DESC 

 Edit   Delete   2         20  	Vlad  	Male
	Edit 	Delete 	1 	19 	Vitali 	Male
	Edit 	Delete 	10 	21 	DESIGNER 	Male
	Edit 	Delete 	104 	19 	silverbelle 	Female
	Edit 	Delete 	108 	17 	imDrej 	Male
	Edit 	Delete 	111 	17 	jrstar 	Male          <====WATCH THIS ONE
	Edit 	Delete 	107 	16 	NY_PLAYA 	Male
	Edit 	Delete 	105 	19 	Mike 	Male
	Edit 	Delete 	103 	18 	Paul 	Male
	Edit 	Delete 	102 	18 	iDuke 	Male
	Edit 	Delete 	101 	24 	Sercher 	Male
	Edit 	Delete 	11 	18 	Liii 	Female
	Edit 	Delete 	9 	20 	TOO_HOT 	Female <====WATCH THIS ONE
	Edit 	Delete 	106 	18 	JonathanR 	Male
	Edit 	Delete 	8 	18 	vlad_test 	Female
	Edit 	Delete 	4 	18 	Arthur 	Male
	Edit 	Delete 	109 	18 	Jack87 	Male
	Edit 	Delete 	110 	18 	Mikebob 	Male

Total rows: 18
Now I try to use the limit:

Code: Select all

SELECT `id` , `age` , `username` , `gender`
FROM `users`
WHERE id >0
AND `username` LIKE '%%'
ORDER BY `popularity` DESC
LIMIT 0 , 12

  	Edit   	 Delete   	2  	20  	Vlad  	Male
	Edit 	Delete 	1 	19 	Vitali 	Male
	Edit 	Delete 	10 	21 	DESIGNER 	Male
	Edit 	Delete 	104 	19 	silverbelle 	Female
	Edit 	Delete 	108 	17 	imDrej 	Male
	Edit 	Delete 	107 	16 	NY_PLAYA 	Male
	Edit 	Delete 	105 	19 	Mike 	Male
	Edit 	Delete 	103 	18 	Paul 	Male
	Edit 	Delete 	102 	18 	iDuke 	Male
	Edit 	Delete 	101 	24 	Sercher 	Male
	Edit 	Delete 	11 	18 	Liii 	Female
	Edit 	Delete 	9 	20 	TOO_HOT 	Female <===WATCH THIS

Look: No jrstar!
Total Rows Returned: 12

SELECT `id` , `age` , `username` , `gender`
FROM `users`
WHERE id >0
AND `username` LIKE '%%'
ORDER BY `popularity` DESC
LIMIT 12 , 12

        Edit   	 Delete   	9  	20  	TOO_HOT  	Female <===see? It returns this again! And still no jrstar....
	Edit 	Delete 	106 	18 	JonathanR 	Male
	Edit 	Delete 	8 	18 	vlad_test 	Female
	Edit 	Delete 	4 	18 	Arthur 	Male
	Edit 	Delete 	109 	18 	Jack87 	Male
	Edit 	Delete 	110 	18 	Mikebob 	Male

Total Rows Returned: 6
So basically, it replicates one of the users, while swallowing another one. But it swallows a user from the middle of the table, which is very very weird. Can anyone give me a pointer?

Thanks. :)
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

The first number of a limit clause is the offset. Setting LIMIT 0,12 means: "Show me 12 rows starting at 1. Setting LIMIT 12,12 means: "Show me 12 rows starting at 12".

Since that 'TOO_HOT' entry is the 12th, it'll show up in both queries.

I've got no idea why jrstar is missing. Could you PM me an sqldump of that table so I can replicate it here and play around?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Here's the full dump of the "users" table, minus the emails and passwords for privacy reasons:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Apr 12, 2006 at 08:32 PM
-- Server version: 4.1.18
-- PHP Version: 4.3.10
-- 
-- Database: ``
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `users`
-- 

CREATE TABLE `users` (
  `id` int(11) NOT NULL default '0',
  `username` varchar(15) NOT NULL default '',
  `pass` varchar(32) NOT NULL default '',
  `email` varchar(32) NOT NULL default '',
  `birthday` bigint(40) NOT NULL default '0',
  `age` tinyint(4) NOT NULL default '0',
  `sex_pref` varchar(50) NOT NULL default '',
  `status` varchar(40) NOT NULL default '',
  `occupation` varchar(15) NOT NULL default '',
  `phys_height` varchar(10) NOT NULL default '',
  `phys_body_type` varchar(50) NOT NULL default '',
  `phys_eye_colour` varchar(50) NOT NULL default '',
  `phys_hair_colour` varchar(50) NOT NULL default '',
  `from_country` varchar(50) NOT NULL default '',
  `location` varchar(128) NOT NULL default '',
  `gender` varchar(10) NOT NULL default '',
  `about_self` text NOT NULL,
  `datejoined` bigint(40) NOT NULL default '0',
  `user_type` varchar(10) NOT NULL default 'regular',
  `user_status` varchar(10) NOT NULL default '',
  `has_pic` int(1) NOT NULL default '0',
  `popularity` int(11) NOT NULL default '0',
  `lastlogin` bigint(20) NOT NULL default '0',
  `postcount` int(11) NOT NULL default '0',
  `promoter` tinyint(4) NOT NULL default '0',
  `sessid` varchar(128) NOT NULL default '',
  UNIQUE KEY `id` (`id`),
  FULLTEXT KEY `about_self` (`about_self`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `users`
-- 

INSERT INTO `users` VALUES (1, 'Vitali', '', '', 540108000, 19, 'Straight', 'Single', 'Student', '6''1', 'Average built', 'Brown', 'Brown', 'Russia', 'Thornhill', 'Male', 'I\\''m the chief programmer and systems designer of tmeet. Being an all-around smart (haha) guy, I am currently explanding my horizons at the Co-Op Management program at the University of Toronto. \r\n\r\nI am currently single, and not looking (in fact, I\\''m never looking). If you look too hard, you\\''ll miss the obvious that\\''s right in front of you...always rember that. :)', 1091390996, 'admin', 'offline', 1, 5, 1144856121, 16, 0, '976cb20bc98b93f2345c035ac2b0fb8d');
INSERT INTO `users` VALUES (2, 'Vlad', '', '', 504943200, 20, 'Straight', 'Taken', 'Student', '5''11', 'Muscular Cut', 'Brown', 'Black', 'Ukraine', 'Richmond Hill', 'Male', 'Creative Director & Founder of tmeet\r\nI manage tmeet events and advertisements.', 1094402856, 'admin', 'offline', 1, 7, 1144884046, 9, 0, '');
INSERT INTO `users` VALUES (4, 'Arthur', '', '', 557470800, 18, 'Straight', 'Prefer not to say', 'tmeet', '5''11', 'Athletic', 'Brown', 'Black', 'Canada', 'Thornhill', 'Male', 'nu chivo nada', 1124236380, 'regular', 'offline', 1, 0, 1144880139, 40, 0, '9f37212ad550dec8c16b42da7398ff1c');
INSERT INTO `users` VALUES (8, 'vlad_test', '', '', 557470800, 18, 'Straight', 'Taken', '', '''', 'Muscular Cut', 'Brown', 'Black', 'Canada', 'Prefer not to say', 'Female', 'test user', 1141951299, 'regular', 'offline', 0, 0, 1144882390, 3, 0, '');
INSERT INTO `users` VALUES (9, 'TOO_HOT', '', '', 507103200, 20, 'Straight', 'Married', '', '5''9', 'Hour Glass', 'Hazel', 'Brown', 'Russia', 'Vaughan', 'Female', 'I am a T.V. Host for a reality series premiering in Spring 2007', 1143834047, 'regular', 'offline', 1, 1, 1144812332, 4, 1, '');
INSERT INTO `users` VALUES (10, 'DESIGNER', '', ', 457333200, 21, 'Straight', 'Single', '', '6''0', 'Athletic', 'Brown', 'Brown', 'Russia', 'Richmond Hill', 'Male', 'I am the guy who wrote this thing.', 1144255661, 'mod', 'offline', 1, 3, 1144860894, 4, 1, '');
INSERT INTO `users` VALUES (11, 'Liii', '', '', 558939600, 18, 'Prefer not to say', 'Dating', '', '5''2', 'Prefer not to say', 'Green', 'Purple', 'Israel', 'Thornhill', 'Female', '', 1144281343, 'regular', 'offline', 0, 1, 1144281343, 0, 0, '');
INSERT INTO `users` VALUES (101, 'Sercher', '', '', 377589600, 24, 'Straight', 'Taken', '', '5''9', 'Big', 'Brown', 'Brown', 'Russia', 'Thornhill', 'Male', '', 1144285308, 'regular', 'offline', 1, 1, 1144875135, 2, 0, '');
INSERT INTO `users` VALUES (102, 'iDuke', '', '', 557730000, 18, 'Prefer not to say', 'Single', '', '6''2', 'Skinny', 'Blue', 'Brown', 'Canada', 'Mississauga', 'Male', 'I\\''m a true <s>playa</s> pilot. I run a grassroots organization, and I also operate an aviation photography business.', 1144291493, 'regular', 'offline', 1, 1, 1144348630, 1, 0, '');
INSERT INTO `users` VALUES (103, 'Paul', '', '', 552114000, 18, 'Straight', 'Single', '', '5''8', 'Average built', 'Brown', 'Black', 'Canada', 'Richmond Hill', 'Male', '', 1144356594, 'regular', 'offline', 1, 1, 1144811621, 2, 0, '');
INSERT INTO `users` VALUES (104, 'silverbelle', '', '', 520578000, 19, 'Straight', 'Single', '', '5''5', 'Athletic', 'Brown', 'Brown', 'China', 'Scarborough', 'Female', '', 1144377496, 'regular', 'offline', 1, 2, 1144702563, 0, 0, '');
INSERT INTO `users` VALUES (105, 'Mike', '', '', 523342800, 19, 'Straight', 'Dating', '', '6''0', 'Muscular Cut', 'Blue', 'Blonde', 'Russia', 'Toronto', 'Male', 'Made in Russia', 1144440599, 'regular', 'offline', 1, 1, 1144804885, 2, 0, '');
INSERT INTO `users` VALUES (106, 'JonathanR', '', '', 547880400, 18, 'Prefer not to say', 'Prefer not to say', '', '5''0', 'Prefer not to say', 'Prefer not to say', 'Prefer not to say', 'Prefer not to say', 'Prefer not to say', 'Male', '', 1144445935, 'regular', 'offline', 0, 0, 1144445935, 0, 0, '');
INSERT INTO `users` VALUES (107, 'NY_PLAYA', '', '', 616741200, 16, 'Straight', 'Single', '', '5''9', 'Average built', 'Blue', 'Brown', 'Ukraine', 'USA', 'Male', '', 1144449192, 'regular', 'online', 1, 1, 1144888714, 0, 0, '');
INSERT INTO `users` VALUES (111, 'jrstar', '', '', 579070800, 17, 'Straight', 'Single', '', '5''7', 'Skinny', 'Hazel', 'Brown', 'Canada', 'Thornhill', 'Male', '', 1144815688, 'regular', 'offline', 0, 1, 1144815688, 0, 0, '');
INSERT INTO `users` VALUES (108, 'imDrej', '', '', 606290400, 17, 'Straight', 'Taken', '', '5''7', 'Big', 'Hazel', 'Brown', 'Macedonia', 'Toronto', 'Male', '', 1144508539, 'regular', 'offline', 1, 1, 1144508539, 0, 0, '');
INSERT INTO `users` VALUES (109, 'Jack87', '', '', 557902800, 18, 'Straight', 'Dating', '', '5''10', 'Average built', 'Brown', 'Brown', 'Romania', 'Richmond Hill', 'Male', 'Descending from the heavens, I firmly believe that I am God\\''s gift to women.', 1144537621, 'regular', 'offline', 1, 0, 1144730038, 2, 0, '');
INSERT INTO `users` VALUES (110, 'Mikebob', '', '', 552114000, 18, 'Straight', 'Single', '', '5''10', 'Average built', 'Brown', 'Black', 'Canada', 'Thornhill', 'Male', '', 1144637371, 'regular', 'offline', 1, 0, 1144637371, 1, 0, '');
As for the 12,12 limit, should 0,12 show me rows 0 through 11, and 12,12 show me rows 12 through 23?

Thanks. :)
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Here's another interesting tidbit:

Code: Select all

SELECT `id` , `age` , `username` , `gender`
FROM `users`
WHERE id >0
AND `username` LIKE '%%'
ORDER BY `popularity` DESC
LIMIT 1 , 12 

#using "Limit 1,12" instead of "Limit 0,12"

#okay, it skips Vlad
 Edit   	 Delete   	1  	19  	Vitali  	Male
	Edit 	Delete 	10 	21 	DESIGNER 	Male
	Edit 	Delete 	104 	19 	silverbelle 	Female
	Edit 	Delete 	108 	17 	imDrej 	Male
	Edit 	Delete 	107 	16 	NY_PLAYA 	Male
	Edit 	Delete 	105 	19 	Mike 	Male
	Edit 	Delete 	103 	18 	Paul 	Male
	Edit 	Delete 	102 	18 	iDuke 	Male
	Edit 	Delete 	101 	24 	Sercher 	Male
	Edit 	Delete 	11 	18 	Liii 	Female
	Edit 	Delete 	9 	20 	TOO_HOT 	Female
	Edit 	Delete 	111 	17 	jrstar 	Male   <==============how did he get all way down here?
This is really starting to weird me out....
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

My queries are returning exactly what you'd expect.

Code: Select all

mysql> select username,popularity from users where id > 0 order by popularity desc limit 0,12;
+-------------+------------+
| username    | popularity |
+-------------+------------+
| Vlad        |          7 |
| Vitali      |          5 |
| DESIGNER    |          3 |
| silverbelle |          2 |
| TOO_HOT     |          1 |
| Liii        |          1 |
| Sercher     |          1 |
| iDuke       |          1 |
| Paul        |          1 |
| Mike        |          1 |
| NY_PLAYA    |          1 |
| jrstar      |          1 |
+-------------+------------+
12 rows in set (0.00 sec)



mysql> select username,popularity from users where id > 0 order by popularity desc limit 12,12;
+-----------+------------+
| username  | popularity |
+-----------+------------+
| imDrej    |          1 |
| Arthur    |          0 |
| vlad_test |          0 |
| JonathanR |          0 |
| Jack87    |          0 |
| Mikebob   |          0 |
+-----------+------------+
6 rows in set (0.00 sec)
The results you are posting look like their getting massaged into a form. Try just running the query right in the DB and see if you get expected results.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

These are coming from phpmyadmin...I have no way of running them directly beause it's on a shared server...and your queries are the same as mine. This is very weird, could this be a screw-up in the mysql server? Could it be something with "username LIKE '%%'"?
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

*bump* Sorry guys, I have to figure this out, and I don't want to be in a position where I run the query with no limit and sort through the array using PHP. That's be inefficient...
Post Reply