count of new messages

[phpsycho]

okay I got two tables..  mail & messages

 

so basically when someone sends a new message it creates a row in "mail" and inserts a row with message data into "messages".

 

basically I want to display the number of messages that are new sent to the current user. "mail" holds subject, to, and from. "messages" holds to, from, and mail_id. mail_id refers to the id of the row in "mail".

 

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to` int(11) NOT NULL,
  `from` int(11) NOT NULL,
  `mail_id` int(11) NOT NULL,
  `message` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=40 ;

CREATE TABLE IF NOT EXISTS `mail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `from` varchar(255) NOT NULL DEFAULT '',
  `to` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL,
  `time_update` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=233 ;

 

so I know how to use mysql_num_rows(); but..

say inside "mail" someone sends a reply it inserts into "messages" with a new row and then updates "mail" and sets `status`='1' (unread)

 

I wanna get the number of messages unread that is sent to the current user (`to` inside of "messages" not "mail")

 

I hope someone understands what im saying.. I know its very confusing lol sorry about that

[phpsycho]

still haven't got this working. does anyone understand what I explained?

[HDFilmMaker2112]

How are you determining what user to get the messages for?

I would think it should be straight forward like this, if I'm understanding correctly.

SELECT * FROM messages WHERE status=1 AND user_id=USERID

[phpsycho]

nah see the `status` is in the "mail" table and the `to` is in the "messages" table

[HDFilmMaker2112]

SELECT mail.*,messages.* FROM messages JOIN mail WHERE mail.status=1 AND messages.to=

[priti]

1.

You want all un-read mails?

select * from mail where status=1

 

2.

unread email of some user

select m.*

From mail m,message ms

where ms.mail_id=m.id and ms.user=userid and m.status=1

 

[user id is in which table.... I don't find it in message ?]

 

3.

today's unread email

select * from mails where status=1 and time_update=DATE_FORMAT(now(), '%Y-%m-%d %T')

 

[phpsycho]

ehh FilmMaker's is almost good. I just need to select how many rows in "messages" where `messages`.`mail_id` = a `mail`.`id` in "mail" where `mail`.`status`='1'

[HDFilmMaker2112]

SELECT COUNT(messages.mail_id) FROM messages JOIN mail WHERE mail.status=1 AND messages.mail_id=mail.id 

[phpsycho]

hmmm alright that works now

$nh = mysql_num_rows(mysql_query("SELECT mail.*,messages.* FROM `messages` JOIN `mail` WHERE `mail`.`status`='1' AND `messages`.`to`='{$_SESSION['id']}' AND `messages`.`mail_id`=mail.id"));

 

but... came to a glitch. I want to show if there is a new message the the current user. right now it will show if there has been any messages sent to the current user (any at all) where messages.mail_id=mail.id and mail.status=1

so basically if current user sends message to userid=40 and mail_id=12 then it sets mail.status=1 where mail.id=12

and userid=40 sends message back it marks mail.status=1 again so it will still think the current user has a new message.

any idea how to fix that flaw in my system?

[HDFilmMaker2112]

hmmm alright that works now

$nh = mysql_num_rows(mysql_query("SELECT mail.*,messages.* FROM `messages` JOIN `mail` WHERE `mail`.`status`='1' AND `messages`.`to`='{$_SESSION['id']}' AND `messages`.`mail_id`=mail.id"));

 

but... came to a glitch. I want to show if there is a new message the the current user. right now it will show if there has been any messages sent to the current user (any at all) where messages.mail_id=mail.id and mail.status=1

so basically if current user sends message to userid=40 and mail_id=12 then it sets mail.status=1 where mail.id=12

and userid=40 sends message back it marks mail.status=1 again so it will still think the current user has a new message.

any idea how to fix that flaw in my system?

 

You're out of my range now... Hope I don't make admins mad here, but go to the MySQL section on http://forums.devshed.com/mysql-help-4/

And look for some help from the mod "r937". Pretty much solved any complex SQL Query I've ever had to write.

[phpsycho]

I got it lol took me a while to organize the thoughts. I just had to add a field in "messages" called `status` and add in some querys to set it to 1 and update to 0 when mail is opened.

thanks guys!