SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: query problem

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query problem

    Hi Everyone

    I have the following problem

    I have members table, whoisonline table and memberimages table.

    Here are the tables:

    PHP Code:

    CREATE TABLE 
    IF NOT EXISTS `members` (
      `
    memberidint(11NOT NULL AUTO_INCREMENT,
      `
    ethnicoriginidint(11) DEFAULT NULL,
      `
    nationalityidint(11) DEFAULT NULL,
      `
    residencecountryidint(11) DEFAULT NULL,
      `
    cityidint(11) DEFAULT NULL,
      `
    subscriptiontypeidint(11) DEFAULT NULL,
      `
    relationshipstatusidint(11) DEFAULT NULL,
      `
    educationlevelidint(11) DEFAULT NULL,
      `
    emailvarchar(255NOT NULL,
      `
    usernamevarchar(255NOT NULL,
      `
    passwordvarchar(255NOT NULL
      
    `sexvarchar(255NOT NULL
      
      
      CREATE TABLE 
    IF NOT EXISTS `whoisonline` (
      `
    recordidint(11NOT NULL AUTO_INCREMENT,
      `
    memberidint(11NOT NULL,
      `
    lastlogintimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `
    loggedfromipvarchar(255NOT NULL
      
      
      CREATE TABLE 
    IF NOT EXISTS `membersimages` (
      `
    imageidint(11NOT NULL AUTO_INCREMENT,
      `
    memberidint(11NOT NULL,
      `
    filenamevarchar(255NOT NULL,
      `
    dateuploadeddatetime NOT NULL,
      `
    uploadedfromipvarchar(255) DEFAULT NULL,
      `
    sorderint(11) DEFAULT NULL,
      `
    visibletinyint(4) DEFAULT NULL 
    I would like to get all members where sex is 2 which have images in membersimages and are online now.

    I manage to do the first, that is to get all members which sex = 2 and have images in membersimages table with this query

    PHP Code:

    select 
    from members where sex '2' AND exists (select memberid from membersimages where members.memberid membersimages.memberid
    but I have problem to get ONLY the users which are online. I try with someting like this:

    PHP Code:

    AND exists (SELECT FROM whoisonline WHERE TIMESTAMPDIFF(MINUTElastloginCURRENT_TIMESTAMP) <= 30
    but without any success.

    Can anyone help me with this?

    I always have problems with those kind of queries...Any book recommendation that will help me to learn those kind of queries will be deeply appreciated.

    Regards, Zoreli

  2. #2
    ********* Ornithologist AtomicPenguin's Avatar
    Join Date
    May 2002
    Location
    Vancouver, BC
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Zoreli!

    Does the whoisonline table only contain just that - who is currently online (i.e. do logged out / expired accounts get dropped from the table)?

    If so, you could probably do something like this: construct your query to *require* there to be entries in both tables, e.g.

    Code:
    SELECT *
    FROM   members m, whoisonline w
    WHERE  m.memberid = w.memberid
    Then tack on the additional clauses:

    Code:
    SELECT *
    FROM   members m, whoisonline w
    WHERE  m.memberid = w.memberid AND
           m.sex = 2 AND
           EXISTS (
                SELECT memberid 
                FROM membersimages 
                WHERE m.memberid = membersimages.memberid
           )
    I *think* that should work!

    - Ben
    A.P.

    generatedata.com - free JS/PHP/MySQL random test data generator.
    Form Tools - free PHP/MySQL form processor.

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    sitepoint
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm new myself and that timestamp function is new to me, but couldn't you do something like:
    Code MySQL:
    SELECT username FROM members
    INNER JOIN membersimages
    ON members.memberid = memberimages.memberid
    INNER JOIN whoisonline
    ON memberimages.memberid = whoisonline.memberid
    WHERE
    members.sex = 2
    AND
    TIMESTAMPDIFF(MINUTE, whoisonline.lastlogin, CURRENT_TIMESTAMP) <= 30)
    ;
    Half way to nowhere

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Ben

    It works, thanks...I will have to rewrite the query now, but it works.

    Thanks for your help

    Regards, Zoreli


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
  •