And i get this error: Subquery returns more than 1 row
And bellow this bonus error, because of the error in the query:
Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in C:\wamp\www\*****\inc\functions_user.php
$sql = "SELECT user_id, user_firstname, user_lastname, user_country, user_joined,
(SELECT COUNT(counter.page)
FROM users, counter
WHERE counter.category = 'user_profile'
AND users.user_id = counter.page
GROUP BY users.user_id)
+
(SELECT COUNT(counter.page)
FROM users, counter
WHERE counter.category = 'post'
AND users.user_id = counter.page
GROUP BY users.user_id) as SumCount FROM users ORDER BY SumCount LIMIT 20";
Counter
id int(11) Nei Ingen AUTO_INCREMENT
ip varchar(20) latin1_swedish_ci Nei Ingen
category varchar(100) latin1_swedish_ci Nei Ingen
page varchar(200) latin1_swedish_ci Nei Ingen
time int(11) Nei Ingen Users
user_id int(10) UNSIGNED Nei Ingen AUTO_INCREMENT
user_rank tinyint(2) UNSIGNED Nei 1
user_email varchar(100) utf8_bin Nei Ingen
user_password varchar(40) utf8_bin Nei Ingen
user_birthday varchar(10) utf8_bin Nei Ingen
user_lang varchar(30) utf8_bin Nei en
user_country varchar(10) utf8_bin Nei Ingen
user_firstname varchar(100) utf8_bin Nei Ingen
user_lastname varchar(100) utf8_bin Nei Ingen
user_gender varchar(10) utf8_bin Nei Ingen
user_dateformat varchar(100) utf8_bin Nei d.m.Y
user_dateformat_long varchar(100) utf8_bin Nei d.m.Y @ H:i:s
user_timezone decimal(5,2) Nei 0.00
user_joined int(11) UNSIGNED Nei Ingen
user_lastlogin int(11) UNSIGNED Ja 0
user_posts mediumint(8) Nei 0
user_status varchar(100) utf8_bin Ja NULL
user_bodyweight decimal(5,2) Nei Ingen
user_place varchar(100) utf8_bin Nei Ingen
user_category varchar(250) utf8_bin Nei Ingen
user_weight_metric varchar(3) utf8_bin Nei kg
user_activationKey varchar(40) utf8_bin Ja NULL
user_avatarPath varchar(100) utf8_bin Ja NULL
user_blog_privacy tinyint(1) Nei 0
user_blogs_per_page tinyint(3) Nei 5
user_pm_per_page tinyint(3) Nei 10
user_comments_per_page tinyint(3) Nei 15
user_msg_from_comments tinyint(1) UNSIGNED Nei 1
user_show_blog_comments tinyint(1) UNSIGNED Nei 1
user_sort_kg_reps tinyint(1) Nei 1
user_sort_comments varchar(100) utf8_bin Nei Ingen
user_show_bw tinyint(1) Nei 1
user_show_training_in_blog tinyint(1) Nei 0
user_about text utf8_bin Nei Ingen
SELECT users.user_id
, users.user_firstname
, users.user_lastname
, users.user_country
, users.user_joined
, SUM(CASE WHEN counter.category
IN ('user_profile','post')
THEN 1 ELSE 0 END) AS SumCount
FROM users
LEFT OUTER
JOIN counter
ON counter.page = users.user_id
ORDER
BY SumCount LIMIT 20
may i ask why you are matching counter.page which is VARCHAR(100) against users.user_id which is INTEGER?
Yes, on the count function i add the user_id to counter.page and counter.category to determine if its profile or post or news_post etc. then the news id will be counter.page if you understand?
The query you gave did work, but it only gives me the user with most visit… hmm…
CREATE TABLE IF NOT EXISTS `counter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(20) NOT NULL,
`category` varchar(100) NOT NULL,
`page` varchar(200) NOT NULL,
`time` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=141 ;
CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_rank` tinyint(2) unsigned NOT NULL DEFAULT '1',
`user_email` varchar(100) COLLATE utf8_bin NOT NULL,
`user_password` varchar(40) COLLATE utf8_bin NOT NULL,
`user_birthday` varchar(10) COLLATE utf8_bin NOT NULL,
`user_lang` varchar(30) COLLATE utf8_bin NOT NULL DEFAULT 'en',
`user_country` varchar(10) COLLATE utf8_bin NOT NULL,
`user_firstname` varchar(100) COLLATE utf8_bin NOT NULL,
`user_lastname` varchar(100) COLLATE utf8_bin NOT NULL,
`user_gender` varchar(10) COLLATE utf8_bin NOT NULL,
`user_dateformat` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT 'd.m.Y',
`user_dateformat_long` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT 'd.m.Y @ H:i:s',
`user_timezone` decimal(5,2) NOT NULL DEFAULT '0.00',
`user_joined` int(11) unsigned NOT NULL,
`user_lastlogin` int(11) unsigned DEFAULT '0',
`user_posts` mediumint(8) NOT NULL DEFAULT '0',
`user_status` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`user_bodyweight` decimal(5,2) NOT NULL,
`user_place` varchar(100) COLLATE utf8_bin NOT NULL,
`user_category` varchar(250) COLLATE utf8_bin NOT NULL,
`user_weight_metric` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT 'kg',
`user_activationKey` varchar(40) COLLATE utf8_bin DEFAULT NULL,
`user_avatarPath` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`user_blog_privacy` tinyint(1) NOT NULL DEFAULT '0',
`user_blogs_per_page` tinyint(3) NOT NULL DEFAULT '5',
`user_pm_per_page` tinyint(3) NOT NULL DEFAULT '10',
`user_comments_per_page` tinyint(3) NOT NULL DEFAULT '15',
`user_msg_from_comments` tinyint(1) unsigned NOT NULL DEFAULT '1',
`user_show_blog_comments` tinyint(1) unsigned NOT NULL DEFAULT '1',
`user_sort_kg_reps` tinyint(1) NOT NULL DEFAULT '1',
`user_sort_comments` varchar(100) COLLATE utf8_bin NOT NULL,
`user_show_bw` tinyint(1) NOT NULL DEFAULT '1',
`user_show_training_in_blog` tinyint(1) NOT NULL DEFAULT '0',
`user_about` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=66 ;
CREATE TABLE IF NOT EXISTS `training` (
`training_id` int(20) NOT NULL AUTO_INCREMENT,
`user_id` mediumint(8) NOT NULL,
`title` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`date` int(11) NOT NULL,
`date_update` int(11) NOT NULL,
`comment` longtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`bodyweight` decimal(5,2) NOT NULL,
`place` varchar(100) NOT NULL,
`timespent_hours` int(3) NOT NULL,
`timespent_minutes` int(3) NOT NULL,
`disable_comment` tinyint(1) NOT NULL,
`meet` tinyint(1) NOT NULL DEFAULT '0',
`category` varchar(250) NOT NULL,
PRIMARY KEY (`training_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=191 ;
My first post
SELECT user_id, user_firstname, user_lastname, user_country, user_joined,
(SELECT COUNT(counter.page)
FROM users, counter
WHERE counter.category = 'user_profile' // Views on profile page
AND users.user_id = counter.page
GROUP BY users.user_id)
+
(SELECT COUNT(counter.page)
FROM users, counter
WHERE counter.category = 'post' // Views on training (posts)
AND users.user_id = counter.page
GROUP BY users.user_id) as SumCount FROM users ORDER BY SumCount LIMIT 20
counter.page= user_profile (user_profile=users.user_id)
counter.page = post (post=training.training_id)
When a user visit a profile one row get inserted in the counter table and counter.category is then = user_profile
When a user visit a post/training one row get inserted in the counter table and counter.category is then = post
Basically i want to count the rows from counter table where counter.category= user_profile AND counter.category=post (Wher profile and post are from the same user)
And as i mention in the last reply:
counter.page= user_profile (user_profile=users.user_id)
counter.page = post (post=training.training_id WHERE training.user_id=users.user_id)
SELECT *
, (SELECT count(counter.page)
FROM counter
WHERE counter.category='user_profile' AND counter.page=users.user_id
)
+ (SELECT count(counter.page)
FROM counter, training
WHERE counter.category='post' AND counter.page=training.training_id AND training.user_id=users.user_id
) AS result
FROM users
ORDER BY result DESC