SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subquery returns more than 1 row :(

    Hi

    Quick question

    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

    Code MySQL:
    $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
    Code 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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What is the table structure for both the "users" and "counter" tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    What is the table structure for both the "users" and "counter" tables?
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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..

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    yes, here you are.

    Code MySQL:
    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 ;

    Code MySQL:
    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 ;

    Code MySQL:
     
     
    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
    Code MySQL:
    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 ?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you also please describe in words what you want counted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you also please describe in words what you want counted?
    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. #11
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found a solution now

    Here is the final query:

    Code MySQL:
    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. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •