98Wn79pM

1-- DB structure
2
3CREATE TABLE IF NOT EXISTS `user_data_fields` (
4  `id` int(11) NOT NULL AUTO_INCREMENT,
5  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
6  PRIMARY KEY (`id`),
7  UNIQUE KEY `name` (`name`)
8) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;
9INSERT INTO `user_data_fields` (`id`, `name`) VALUES
10(7, 'cell'),
11(6, 'city'),
12(2, 'firstname'),
13(3, 'lastname'),
14(1, 'role'),
15(4, 'street'),
16(5, 'zip');
17CREATE TABLE IF NOT EXISTS `user_users` (
18  `id` bigint(20) NOT NULL AUTO_INCREMENT,
19  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
20  `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
21  PRIMARY KEY (`id`)
22) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;
23INSERT INTO `user_users` (`id`, `username`, `password`) VALUES
24(1, 'lilleman', 'tihi'),(2, 'storeman', 'haha');
25CREATE TABLE IF NOT EXISTS `user_users_data` (
26  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
27  `user_id` bigint(20) DEFAULT NULL,
28  `field_id` int(11) DEFAULT NULL,
29  `data` text COLLATE utf8_unicode_ci NOT NULL,
30  PRIMARY KEY (`id`),
31  KEY `users_fields` (`user_id`,`field_id`)
32) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;
33INSERT INTO `user_users_data` (`id`, `user_id`, `field_id`, `data`) VALUES
34(3, 1, 1, 'admin'),
35(4, 1, 1, 'challenger'),
36(5, 1, 7, '0709771337'),
37(6, 1, 6, 'Farsta'),
38(7, 1, 2, 'Mikael'),
39(8, 1, 3, 'Goransson'),
40(9, 1, 4, 'Havsornsvägen 8 4tr'),
41(10, 1, 5, '999'),
42(11, 2, 1, 'admin'),
43(12, 2, 7, '238232'),
44(13, 2, 6, 'Norje'),
45(14, 2, 2, 'Nils'),
46(15, 2, 3, 'Nilsson'),
47(16, 2, 4, 'stollegatan 8'),
48(17, 2, 5, '12349');
49
50-- Query
51SELECT
52  UU.id,
53  UU.username,
54  UDF.name,
55  UUD.data
56FROM
57  user_users_data AS UUD,
58  user_users AS UU,
59  user_data_fields AS UDF
60WHERE
61  UU.id = UUD.user_id AND
62  UDF.id = UUD.field_id
63
64-- Now how do I limit the number of USERS, not rows?