Subquery returns more than 1 row :(

Databases
1

Hi

Quick question :slight_smile:

I am adding together 2 queries

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";

PHP


if(!$result = $db->sql_query($sql))
	{
		printError("SQL ERROR", mysql_error());
	}
	echo "<table>";
	$i=1;
while($row = mysql_fetch_array($result))
		{
			$visits = $row['SumCount'];
			$title = translate('visit_blog')." " . $row['user_firstname'] . " " . $row['user_lastname'] . ", ". translate(ucf(getCountryName($row['user_country'])) )." (" . translate('joindate') . " " . date("d.m.Y", $row['user_joined']) . ")";
			echo "
			<tr>
            <td>". $i++ .".</td>
			<td>
				<a style='color:#555555; font-weight:bold;' href='index.php?action=profile&amp;id=". $row['user_id'] ."' title='". $title ."'>
				<div style='float: left; clear: right; padding-right: 3px;'><img src='img/flags/16/" . strtolower($row['user_country']). ".png' alt=" . $row['user_country'] ." /></div>
				". $row['user_firstname'] . " " . $row['user_lastname'] ."</a> (". $visits .")
			</td>
            </tr>";
		}
	echo "</table>";

What am i doing wrong here? Is there some other way to make the query?

2

What is the table structure for both the “users” and “counter” tables?

3

Nei = no
Ja = yes
Ingen = none/nothing

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

4 
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?

5

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? :slight_smile:

The query you gave did work, but it only gives me the user with most visit… hmm…

6

I see a problem here, the post id isnt related to the user here, so have to be something like this.

counter.page = users.user_id AND training.training_id = counter.page WHERE training.user_id = users.user_id

I dont know exactly where to add this to the query then …
To get both profile view and post views (post are trainings added).

7

there’s a training table?

would you kindly do a SHOW CREATE TABLE for each of your tables

i know you copied the columns for two of them earlier, but SHOW CREATE TABLE also reveals foreign keys and indexes

8

yes, here you are.


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)

Is this more clear ? :slight_smile:

9

could you also please describe in words what you want counted?

10

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)

11

I found a solution now :slight_smile:

Here is the final query:


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
12

sorry for not getting back to you, i was busy

that query appears to be somewhat less efficient than it could be, but at least you got a solution – nice job

:slight_smile: