Jump to content

MySQL Issue, more than one value for one column!


reportingsjr
 Share

Recommended Posts

I'm having an issue, I wanted to make a chat.. But I also wanted the person on the chat to see messages directed at them, so they would be the only one to see it. (the user is logged in by the way) This is pretty much supposed to be like a pm system in a chat. Heres what i tried, you may be able to understand what I'm trying to do if you look at it:

SELECT * FROM `chat` WHERE `to` = 'everyone' AND `to` = 'username' ORDER BY id DESC

Anyone know if this can be done, and how?Thanks!

Link to comment
Share on other sites

What if instead, you used two columns? This would return all public messages:

SELECT * FROM `chat` WHERE `type` = 'public' ORDER BY id DESC

And this would return all private messages for a particular user:

SELECT * FROM `chat` WHERE `type` = `private` AND `to` = 'reportingsjr' ORDER BY id DESC

You could also use a bit (to keep the size of your database down) where 0 = public and 1 = private (or vice-versa). Could even name the "type" column to "IsPrivate".OR, if there was never a situation where a message could be both public and private, you could rely on one column like in your example:

SELECT * FROM `chat` WHERE `to` = 'everyone' ORDER BY id DESC

SELECT * FROM `chat` WHERE `to` = 'reportingsjr' ORDER BY id DESC

The only thing to worry about there is that nobody has a username of "everyone". :)To add another layer of abstraction to your table, you could use userIDs instead. If the chat is a private chat, then assign the userID column to the userID of the appropriate user. If it is a public one, then assign -1 to that column. Then, to get all the public messages, simply do a query for all items that have a userID of -1.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...