Page 1 of 1

LIMIT isn't working prperly

Posted: Wed Apr 12, 2006 11:37 am
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. :)

Posted: Wed Apr 12, 2006 2:05 pm
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?

Posted: Wed Apr 12, 2006 8:36 pm
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. :)

Posted: Wed Apr 12, 2006 9:28 pm
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....

Posted: Thu Apr 13, 2006 10:02 am
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.

Posted: Thu Apr 13, 2006 2:42 pm
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 '%%'"?

Posted: Fri Apr 14, 2006 6:09 pm
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...