New to SQL need advice! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New to SQL need advice!

Author

Topic

sale666
Starting Member

2 Posts

Posted - 2012-01-10 : 06:57:46

Hello I just registered to this forum I'm not sure if my question is related more to SQL or PHP... Anyway I started learning SQL because I wanted to learn how to make users log in to my page that I was creating that bumped me to PHP to make scripts (still lerning PHP) however I managed to make a register/log in page with dreamweaver and their auto scripts included to help you out do stuff faster Im still looking how to do that on my own. However my initial idea was make a website where me and my friends can leave messages to users like on forums you pick a user and you send him a message it would be like SMS you pick you send he gets it! So far I managed to make "id" "username" "password" in SQL, created database, created table users are adding in as I wanted to so all works fine (self tested) but I don't know how to make a script that can take a user out of that SQL ID 55 for example and send message to ID 78! Hope you understood what I ment.
Thank you for your time!
Saša

Kristen
Test

22859 Posts

Posted - 2012-01-10 : 08:49:28

You need a table for "Users" (which it sounds like you already have).

Then you need a table for "Messages". When a user "Sends" another user a message you INSERT a row/record into the Message table.

The message table will need columns/field for:

Message ID (This could be IDENTITY type column which will auto-number 1,2,3,...)
Created - Date/Time the record was created
Sender User ID
Recipient User ID
Subject maybe?
Message Text
Status - e.g. "New message", "Message has been read", "Message has been marked for deletion" - you may have other Status codes e.g. "Sender recalled the message", "Archive", etc.

So when a user "sends" a new message they must choose the Recipient ID, and enter the Subject / Message text. You INSERT that as a new row with status= "NEW"

When a user logs in you can show them their NEW messages:


SELECT [Subject]
FROM [Messages]
WHERE [RecipientID] = @MyUserID
AND [Status] = 'NEW'
ORDER BY [Created], [ID]

when they read a message you would do:


SELECT [Subject], [Message]
FROM [Messages]
WHERE [RecipientID] = @MyUserID
AND [ID] = @MyMessageID

UPDATE [Messages]
SET Status='READ'
WHERE [RecipientID] = @MyUserID
AND [ID] = @MyMessageID
AND Status='NEW' -- NOTE: This will only actually update the row the first time the message is read, after that "0 rows" will be updated

sale666
Starting Member

2 Posts

Posted - 2012-01-10 : 11:24:23

Thank you Kristen that is a lot of help to me!
I will be trying to set this up and will keep all in this post so I do not spam the forum!
Will reply how it goes!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources