CODEWITHSUNDEEP
phpmysql
Sergio
Sergio

Reputation: 1239

Mysql query: How can I get the number of the users who sent messages to my specific user with status =1?

I have a problem with (for me to complicated) MySql query.

Okay, here is what I need to do:

First I need to check messages that some specific user received

$mid=$_SESSION['user'];
$stat1=mysql_query("SELECT id, fromid, toid, subject FROM messages WHERE 
toid = '".$mid."'  AND subject != 'not readed' GROUP BY fromid ")
or die(mysql_error());
while ($h = mysql_fetch_array($stat1))
{
$whosend=$h['fromid'];

Second thing that I need to do is check the status of the users (deleted or not) who sent the messages ("fromid") to my specific user ("toid"). This I must do from another table:

$stat2=mysql_query("SELECT id, status FROM members WHERE id='".$whosend."' AND 
status ='1'")or die(mysql_error());
while ($s = mysql_fetch_array($stat))
{

Then my problems begin to show up.

How can I get the number of the users who sent messages to my specific user with status =1? Not the number of the messages but the total number of the users who sent them.

Is there any easier way to do this query? I tried with join tables like

$stat=mysql_query("SELECT memebers.id, memebers.status, messages.toid, 
messages.fromid,messages.subject,messages.id FROM members, messages
WHERE messages.toid='".$mid."' AND members.status ='7' ....

But even in this query I need to have id's of the user who sent messages before this query so there will be another query before this join tables.

Upvotes: 0

Views: 97

Answers (2)

Dave.Sol
Dave.Sol

Reputation: 251

SELECT id, fromid, toid, subject FROM messages WHERE toid = '".$mid."'  AND subject != 'not readed' GROUP BY fromid

can't be right as you have GROUP BY formid and no aggregation in the select clause.

Upvotes: 0

Aaron W.
Aaron W.

Reputation: 9299

So you are looking for the number of members with status = 1 that sent this other member ($mid) a message?

Something like this?

$sql = "select count(distinct messages.fromid) From messages Inner Join members on members.id = messages.fromid Where messages.toid = '" . $mid . "' AND members.`status` = 1";

Upvotes: 2

Related Questions