mysql query problem

[yy886]

mysql query problem

Sample data of my mysql.

id | id2 | sender | recipient | senderread | recipientread | Title
31 | 31  | A177   | B213, A256| yes        | yes           | Question123
32 | 31  | A256   | A177      | yes        | no            | Answer456
33 | 33  | A256   | A177      | yes        | no            | Question1337
34 | 31  | B213   | A177      | yes        | yes           | Answer456
35 | 31  | A177   | B213      | yes        | no            | R:Answer456

I am trying to get the output like below

Result 1 //listing the full conversation with user B213.

31 | 31  | A177   | B213, A256| yes        | yes           | Question123
34 | 31  | B213   | A177      | yes        | yes           | Answer456
35 | 31  | A177   | B213      | yes        | no            | R:Answer456

Result 2 //listing the full conversation that user A177 didnt read yet.

31 | 31  | A177   | B213, A256| yes        | yes           | Question123
32 | 31  | A256   | A177      | yes        | no            | Answer456

example.com/message.php?id=31

First step I am trying is trying to get the result if id or id2 is 31.
$query = mysql_query('SELECT * FROM pm where id="'.$id.'" or id2="'.$id.'");

Current user is A177, trying to pull the data if sender is A177 and recipientread = "no"...
$_SESSION['user'] = "A177";

$query = mysql_query('SELECT * FROM pm where id="'.$id.'" or id2="'.$id.'" and sender = "'.$_SESSION['user'].'" and recipientread = "no"');

And this is not working already...I am kinda lost now...can someone please give me a hand?
Thanks

[diafol]

I can't really see anything obviously wrong with that. BUT, your recipient column has a list rather than single values, so searching that requires a different syntax to recipient = '{$_SESSION['user']}'

The FIND_IN_SET may be the way to go?

[yy886]

ar i forgot to paste the next query...anyway my problem is i cannot seperate the result 1 and 2...

so ...can you point me some example? thanks

[yy886]

Trying to display result 1 but seem to be I am in the wrong direction...below is my latest attampted.

$query = mysql_query('SELECT * FROM pm where id="'.$id.'" or id2="'.$id.'" and sender = "'.$_SESSION['user'].'" and recipientread = "no" ORDER BY timestamp DESC ');


$query = mysql_query('SELECT * FROM pm where recipient = "'.$_SESSION['user'].'" or sender = "'.$_SESSION['user'].'" and recipientread = "no" GROUP BY id2="'.$id.'" ORDER BY timestamp DESC ');

[diafol]

This works for me:

SELECT * FROM pm WHERE id2=31 AND (sender='B213' OR FIND_IN_SET('B213', recipient))

With regard to ordering, ordering by the id would probably suffice, but timestamp should work as well.

[yy886]

Thanks!

And then I came accross another problem

http://img856.imageshack.us/img856/761/lightingtechnologyhomep.jpg

I am trying to count how many messages on each conversation...

I can get the counted number for each conversation but however I dont know how to put them together.

I was thinking to use two query but however this require me to join in tow while loop...which I dont think it can work that way (and I tested it but no luck so far/)

Here is my latest code.

    include('connect.php');
    //list latest unread msg
    $query = mysql_query('SELECT * FROM pm where sender="'.$_SESSION['user'].'" OR FIND_IN_SET("'.$_SESSION['user'].'", recipient) and recipientread = "no" GROUP BY id2 ORDER BY timestamp ASC ');
    $num_row = mysql_num_rows ($query);
    $msgcount = 1;
    if ($num_row > 0 ) {
    $i=0;

    //counting the message for each conversation
    $query3 = mysql_query('SELECT id2, count(*) FROM pm GROUP BY id2, sender="'.$_SESSION['user'].'" OR FIND_IN_SET("'.$_SESSION['user'].'", recipient)');
    while($num_row3=mysql_fetch_array($query3))  
    {  
    echo $num_row3['count(*)'];
    }

    while ($i < $num_row) {

    $unread_id = mysql_result($query,$i,"id");
    $title = mysql_result($query,$i,"title");
    $sender = mysql_result($query,$i,"sender");
    $r_time = mysql_result($query,$i,"timestamp");
    $newtime = date("Y-m-d H:i:s", $r_time);


    echo  '<tr>';
    echo       '<td style="word-wrap: break-word">'.$sender.'</td>';
    echo       '<td style="word-wrap: break-word">'.$title.'</td>';
    echo       '<td style="word-wrap: break-word">'.$num_row3['count(*)'].'</td>';
    echo       '<td style="word-wrap: break-word">'.$newtime.'</td>';
    echo       '<td style="word-wrap: break-word"><a href="read_pm.php?r_id='.$unread_id;
    echo       '"><button class="css3button">';
    echo       '<b>Unread MSG No.'.$msgcount.'</b>';
    echo       "</button></a></td>";
    echo  "</tr>";


    $msgcount++;
    ++$i; }} else { echo "No unread message"; }``

[diafol]

SELECT id2, COUNT(*) FROM pm WHERE id2=31 AND (sender='B213' OR FIND_IN_SET('B213', recipient)) GROUP BY id2

THat gives me

31 | 3

[yy886]

I figgured it out!

while(($num_row3=mysql_fetch_array($query3)) && ($i < $num_row))

and now the result is lining up!
http://img801.imageshack.us/img801/761/lightingtechnologyhomep.jpg

will do more test later on to see is this really working.

Thanks for the help diafol!

btw, I may have a few more questions during further coding...so I wont mark this question as solved.

[diafol]

No prob.

[yy886]

em...I think its a small mistake but I just couldnt locate it...

The last record give me 2 result but however I only have one record in the mysql...
http://imageshack.us/a/img189/761/lightingtechnologyhomep.jpg

http://imageshack.us/a/img16/1374/localhost8080127001proj.jpg

include('connect.php');
//list unread msg
$query = mysql_query('SELECT * FROM pm where recipientread = "no" AND FIND_IN_SET("'.$_SESSION['user'].'", recipient) GROUP BY id2 ORDER BY timestamp ASC ');
$num_row = mysql_num_rows ($query);
$msgcount = 1;
if ($num_row > 0 ) {
$i=0;

//count how many messages in the same conversation
$query3 = mysql_query('SELECT id2,sender,recipient, count(*) FROM pm WHERE sender="'.$_SESSION['user'].'" OR FIND_IN_SET("'.$_SESSION['user'].'", recipient)GROUP BY id2,sender,recipient');

while(($i < $num_row) &&  ($num_row3=mysql_fetch_array($query3))){  


$unread_id = mysql_result($query,$i,"id");
$title = mysql_result($query,$i,"title");
$sender = mysql_result($query,$i,"sender");
$r_time = mysql_result($query,$i,"timestamp");
$newtime = date("Y-m-d H:i:s", $r_time);


echo  '<tr>';
echo       '<td style="word-wrap: break-word">'.$sender.'</td>';
echo       '<td style="word-wrap: break-word">'.$title.'</td>';
echo       '<td style="word-wrap: break-word">'.$num_row3['count(*)'].'</td>';
echo       '<td style="word-wrap: break-word">'.$newtime.'</td>';
echo       '<td style="word-wrap: break-word"><a href="read_pm.php?r_id='.$unread_id;
echo       '"><button class="css3button">';
echo       '<b>Unread MSG No.'.$msgcount.'</b>';
echo       "</button></a></td>";
echo  "</tr>";


$msgcount++;
++$i; }} else { echo "No unread message"; }

[diafol]

SELECT id2,sender,recipient, count(*) FROM pm WHERE sender="'.$_SESSION['user'].'" OR FIND_IN_SET("'.$_SESSION['user'].'", recipient)GROUP BY id2,sender,recipient

That doesn't make much sense to me. You're grouping on so many fields, thast you're more likely going to get count=1 on each grouped record, effectively obviating the whole point of the count and group by. But on second thoughts, is that what you're after?

[yy886]

.......................

http://imageshack.us/a/img16/1374/localhost8080127001proj.jpg

damn...i see some mistake on the sql...could be caused by the early code error.

anyway..

if username match the record sender/recipient and then count them.

Let say...

1.message id=37 id2=37 sent by 7477 and recipent 8080 recipentread = yes

2.message id=38 id2=37 sent by 8080 and recipent 7474 recipentread = no

3.message id=39 id2=39 sent by 8080 and recipent 7474 recipentread = no

(id2 is used to group the same conversation.)

I am now loggin as 7474. On my message box it should have 2 unread message

Message No.1
Sender: 8080
Title: R:Testttt
2 message in this conversation.

Message No.2
Sender: 8080
Title: Test Test
1 message in this conversation.

Ive tried

    SELECT id2, count(*) FROM pm WHERE sender="'.$_SESSION['user'].'" OR FIND_IN_SET("'.$_SESSION['user'].'", recipient)GROUP BY id2

but on Message No.2 it give me 2 message in this conversation but clearly it only have one message.

[diafol]

Ah, OK so you want multiple 'conversations' - denoted by 'id2' (the parent id) where only you figure in them. You want to count the number of messages (sent by you and received by you). I would imagine that you also want to know how many of those sent to you are unread.

THat right? I'm going out for a little while, but I'll get my thinking cap on and come back later.

A quick idea though:

SELECT id2, sender,SUM(IF(recipientread = 'no', 1, 0)) AS unread,SUM(IF(recipientread = 'yes', 1, 0)) AS `read`, COUNT(*) AS total FROM pm WHERE sender="B213" OR FIND_IN_SET("B213", recipient) GROUP BY id2

I'll have to think abbout only counting the ones sent to you. At the mo it counts unread of the guy you sent to as well.

[diafol]

SELECT id2, sender,SUM(IF(recipientread = 'no' AND FIND_IN_SET("B213", recipient), 1, 0)) AS unread, COUNT(*) AS total FROM pm WHERE sender="B213" OR FIND_IN_SET("B213", recipient) GROUP BY id2

The above will give you the unread ones just for the user logged in. HOWEVER, your recipientread field is difficult to understand, as you can have multiple recipients, but only one value in the recipientread field. So if one recipient reads the message, it will appear to every other recipient that they have read it as well.

If this is the case, you may need to adapt your table design. BTW - what's the purpose of the senderread field? I'm assuming that the sender has read the message he's sent, so the field seems superfluous.

[yy886]

YOU ARE RIGHT! The senderread is really pointness as I completely forgot about to adjcent this value...and the field's should be originalsender.

My original idea is to make a small pm system for the people who registered in my site (not yet finish)

It will contain
Message box = List all messages (Seperate into sent and received)
Unread message noticfacation = a noticfacation on the quick menu telling user to check their unread message.
Unread message = back to Message box and unread message will be highlighted.
Read message = when a user clicked one of their message (any message that he sent/received)

After some thought and based on your advise, Ive come accross a newer sql table.

So when a person decide to send a message to multiple people then

A177 > B213, A256

B213, A256 > explode() function to seperate the recipient > loop though a foreach and send the message individually.

So it should be generating 3 messages.
The master message which is from A177. The master message is just for a record that who will received the message.

and then 2 messages go to B213 and A256

So

id | id2 | sender | recipient | mastermessage | recipientread | Title
31 | 31  | A177   | B213, A256| yes           | null          | Question123
32 | 31  | A177   | B213      | no            | no            | Question123    
33 | 31  | A177   | A256      | no            | yes           | Question123

34 | 34  | A256   | B213      | yes           | null          | Question456
35 | 34  | A256   | B213      | no            | no            | Question456

36 | 31  | A256   | A177      | no            | yes           | R:Question123
37 | 31  | A177   | A256      | no            | no            | R:R:Question123

If I am user A177. There should be no unread message.
When I goto the Message box I should be seeing

Received:
Message No.1
Sender: A256
Recipient: A177
Title: R:R:Question123
Meesage count:2

Sent:
Message No.1
Sender: A177
Recipient: B213, A256
Title: Question123
Meesage count:1

On user A256 unread message it should give 1 result.

Received:
Message No.1
Sender: A177
Recipient: B213, A256
Title: R:R:Question123
Meesage count:2

Sent:
Message No.1
Sender: A256
Recipient: A177
Title: R:Question123
Meesage count:2

On user B213, unread message it should give 2 results.

Received:
Message No.1
Sender: A256
Title: Question456
Meesage count:1

Message No.2
Sender: A177
Title: Question123
Meesage count:1

Will this logic work? Sorry for bothering you as I am still a newbie on php/mysql.

Thanks for the help!

[diafol]

This looks a bit wasteful. You can use MID() to use a similar setup to your original table:

id [as before]
id2 [as before]
sender_id [as before]
recipient_ids [as before - BUT there can be no spaces - JUST commas as a separator]
recipient_status [varchar, string e.g. 01110 where each number is 0 = unread, 1 = read for each recipient in turn]
title [as before]
(and any additional items)

Then you can use something like this to find the number of unreads:

SELECT id2, SUM(IF(MID(recipientread,FIND_IN_SET('B213',recipient),1) = 0, 1, 0)) AS unread FROM pm WHERE FIND_IN_SET('B213',recipient) GROUP BY id2

However, it's now getting a bit ugly.

[diafol]

Thinking about it you'll want a way to delete a conversation for each involved user too. So this may not be so clever. You could of course have something like 2 = deleted, but you need a field for the sender to show deleted from his/her conversations. So a different schema, similar to what you suggest may be better.

[yy886]

Thanks for the help.

I will restructure the sql table and make a more stright forward logic.

just one more question before I close this question.

Is the MID work like this?
"testMID"
id | num | num333
22 | 888 | 0101100
23 | 778 | 1001102

SELECT MID(num333,3,7) as 4num FROM testMID

Result
4num:
01100
01102

if so then I can improve my other sql table as well...

Looks like I still have a long road to go...

[diafol]

MID(string,position,length) is the same as SUBSTRING(string,position,length)

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring

However, as I mentioned, I'm not sure if this is such an elegant solution. It was just a way to resolve the issue of the multiple value problem in the recipients field.