SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2004
    Location
    Fort Lauderdale
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting counts of groups from mysql "messages" table. Union?

    I have a table:

    Code:
    CREATE TABLE `mailmessage` (
      `id` int(11) NOT NULL auto_increment,
      `to_id` int(11) NOT NULL,
      `from_id` int(11) NOT NULL,
      `subject` varchar(200) NOT NULL,
      `created_at` datetime default NULL,
      `content` text NOT NULL,
      `dateopened` datetime default NULL,
      `folderid` int(10) NOT NULL default '1',
      `opened` tinyint(1) default '0',
      `spam` tinyint(1) default '0',
      `parent_id` int(11) NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=latin1;
    This table tracks old and new messages assigned to a user_id and a folderid.

    I would like to create a SQL statement that gives me a list of all "old" and "new" messages per each folderid that the user has.

    Do I create a Union or is there a faster way to do this?

    Thank you in advance!

  2. #2
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By "old", you mean messages that have been opened, and "new" as ones that still have "0" under "opened"?
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    are there any other statuses besides "old" and "new"?

    if not, wouldn't that simply cover all the messages that the user has?

    no need for UNION or anything, just sort by folderid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2004
    Location
    Fort Lauderdale
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are no statuses besides "old" and "new". The information about which folder has how many old and new messages per user is necessary because those folders are located on the left side of the page and the messages per that folder are centered on the page, so that the user clicks on the folder and paginated messages show on the center with folder lists on the left.

    So it is a "component" which should have its own "sql" and the paginated messages should have its own....

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so did you try the ORDER BY suggestion?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Mar 2004
    Location
    Fort Lauderdale
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select 
    count(folderid),
    folderid,
    count(opened) 
    from 
    mailmessage 
    where to_id="1590" 
    group by folderid,opened;
    is what I got. How can I further group this so that in one column I have old count(where opened="1") ?

    Thank you

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2004
    Location
    Fort Lauderdale
    Posts
    522
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And finally this:

    Code:
    select count(folderid),folderid,opened from mailmessage where to_id="1590"  group by folderid,opened order by folderid asc;
    I think this will work....( it works now without having been tested )


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
  •