Query problem

Databases
1

Hi Everyone

I have the following problem

I have members table, whoisonline table and memberimages table.

Here are the tables:



CREATE TABLE IF NOT EXISTS `members` (
  `memberid` int(11) NOT NULL AUTO_INCREMENT,
  `ethnicoriginid` int(11) DEFAULT NULL,
  `nationalityid` int(11) DEFAULT NULL,
  `residencecountryid` int(11) DEFAULT NULL,
  `cityid` int(11) DEFAULT NULL,
  `subscriptiontypeid` int(11) DEFAULT NULL,
  `relationshipstatusid` int(11) DEFAULT NULL,
  `educationlevelid` int(11) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL
  `sex` varchar(255) NOT NULL
  
  
  CREATE TABLE IF NOT EXISTS `whoisonline` (
  `recordid` int(11) NOT NULL AUTO_INCREMENT,
  `memberid` int(11) NOT NULL,
  `lastlogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `loggedfromip` varchar(255) NOT NULL
  
  
  CREATE TABLE IF NOT EXISTS `membersimages` (
  `imageid` int(11) NOT NULL AUTO_INCREMENT,
  `memberid` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `dateuploaded` datetime NOT NULL,
  `uploadedfromip` varchar(255) DEFAULT NULL,
  `sorder` int(11) DEFAULT NULL,
  `visible` tinyint(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



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:



AND exists (SELECT * FROM whoisonline WHERE TIMESTAMPDIFF(MINUTE, lastlogin, CURRENT_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

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.

SELECT *
FROM   members m, whoisonline w
WHERE  m.memberid = w.memberid

Then tack on the additional clauses:

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
3

I’m new myself and that timestamp function is new to me, but couldn’t you do something like:


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)
;
4

Hi Ben

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

Thanks for your help

Regards, Zoreli