Output the number of messages a user has.

mband2000

Ok guys/gals

I have database for a membership community site that I'm working on building.. and I want members to have private messages. I will have a frame located in the top lefthand corner that will refresh every minute to display a number which will indicate how many UNread messages that user currently has. This is how my database will be structured.

^{^hope} that makes sense... oh and by the way.. should I structure my table in that way? do you think it's wise?

But anyhow... how can I get the page that refreshes to show the number of messages that user has not read yet? If you need me to clear anything up, feel free to ask.

LordShryku

How do you know if they read it yet? You should probably have a field for that as well. Then just update it the first time they read it. So then, you're number can just be a count against the table where that field says it's not read.

mband2000

ahhh .. so you're saying add a field that says something like "viewed" .. and set the default value to "0" .. and when it's read update that to "1"?

would something like that work?

LordShryku

Yeah, that would work...

mband2000

Ok.. I wanted to try and code this on my own... but I don't even know where to start.. can someone help!

Thank you VERY VERY much.

LordShryku

# connect
# select db
$sql = "SELECT count(*)
          FROM <table>
         WHERE username = '<username>'
           AND viewed = 0";
$res = mysql_query($sql) or die(mysql_error());
# fetch result
# display result

mband2000

OK one more, take a look at this.. what am I doing wrong.. it won't change the value from 0 to 1.

<?
$status = 1;

$status ="INSERT INTO messages (status) WHERE messID = '".$message."' VALUES ('$status')";
$statuschange = mysql_query($status);

?>

that's a part of my code.. I'm not getting any errors.. but nothing is changing in my database.

LordShryku

You need to do an update statement, not an insert...

mband2000

Ok I made the change to this..

	$status ="UPDATE messages (status) WHERE messID = '".$message."' VALUES ('$status')";

still no change.. but also no error messages.. hmmmm wonder why it's not updating..:queasy:

here is the field's property:

tinyint(2)
NOTNULL
Defult: 0

^{^could} this be a problem?

LordShryku

Are you even executing it?

$sql = "UPDATE messages
           SET status = '".$status."'
         WHERE messID = '".$message."'";
mysql_query($sql) or die(mysql_error());

mband2000

Yeah! I just figured it out right before I read your reply.. basically I was writting the SQL code backwards

I changed it to this and it works:

	$status ="UPDATE messages SET status=1 WHERE messID = '".$message."'";

But thank you so much for your help thus far!! If I had 50cents I would give it to ya.