where clause - and / or

[Bantam]

Hi,

I'm having a few issues with a script i'm making, I'm trying to make it so anyone new who log's in does not see whats been said before they login.

I'm using the following sql command:

SELECT *
FROM whos, messages
WHERE (
value = '$user' OR value = 'public'
) AND (
time >= loggedon AND visitor = user)
ORDER BY messid DESC
LIMIT 30

now this command does pull messages from the database but it pulls all the messages previous to logging in, here's my table structure:

messages screen:

CREATE TABLE `messages` (
  `messid` int(11) NOT NULL auto_increment,
  `user` varchar(50) NOT NULL default '',
  `message` text NOT NULL,
  `value` varchar(50) NOT NULL default '',
  `time` int(11) NOT NULL default '0',
  PRIMARY KEY  (`messid`)
)

and my who's online table:

CREATE TABLE `whos` (
  `onlineid` int(11) NOT NULL auto_increment,
  `visitor` varchar(100) default NULL,
  `timevisit` int(11) NOT NULL default '0',
  `loggedon` int(11) NOT NULL default '0',
  PRIMARY KEY  (`onlineid`)
) 

I use time() to put the time into timevisit, loggedon and the field name time in my messages table.

Can anyone see where I am going wrong?

[paulnaj]

Hi Bantam,

I think you've got a possible problem here with your table structures.

To join a 'detail' table [whos] to a 'main' table [messages], you need to have a field that is common to both.

Normally, this would be the 'user' id, which you called 'onlineid' in [whos], I think. What is the filed 'user' in the [messages] table? Are you using that to contain the same value as 'onlineid' ? If not, what's in 'user'?

Paul.