PHP/MySQL different value for each row

Question

I've searched and I've tried many different things but I haven't been able to come up with anything to work the way I want. What I have is a direct message system but I want to show next to the users name how many new messages they have from that person if it's more than 0. Everything I've tried just shows one value for all of the conversations. In my database I have a column that is if the user who received the message read it, if they had it turns the 'no' into a 'yes', otherwise it shows 'no'.

Thanks in advance for your help!

EDIT: One thing for my database...Only the first message in the conversation has both the to_id and from_id after that all the other messages in the conversation only has the from_id. So what I'm trying to do is count how many where the from_id is not your user_id, so the non-signed in user user_id. If that makes sense.

Here is my code accessing the database:

// Get the conversations

$get_conversations = "SELECT dm.convo_id, dm.message_id, dm.to_id, dm.from_id, dm.user2read, u.name, u.user_id" .
                    " FROM users u" .
                    " JOIN direct_messages dm" .
                    " ON dm.from_id = u.user_id" .
                    " OR dm.to_id = u.user_id" .
                    " WHERE u.user_id = " . $_SESSION['user_id'];
                    " GROUP BY dm.convo_id" .
                    " LIMIT 1";



// Run the conversation query
$convo_result = mysql_query($get_conversations);

And then some HTML code And more php in the body tags

<?php
while ($teg = mysql_fetch_array($convo_result)) {
    if($teg) {
        $to_id2 = $teg['to_id'];
        $from_id2 = $teg['from_id'];


        if($from_id2 == $_SESSION['user_id']) {
            $id_of_other_person = $to_id2;
        } else if($from_id2 != $_SESSION['user_id']) {
            $id_of_other_person = $from_id2;
        }

        // Get the name of the other person
        $get_other_name = "SELECT name FROM users WHERE user_id = " . $id_of_other_person;

        // Run the query on the other person's name
        $query_name = mysql_query($get_other_name);
        if($query_name) {
            $yelp = mysql_fetch_array($query_name);
            $name_of_other_person = $yelp['name'];
        }

        $get_user2read = "SELECT dm.convo_id, dm.message_id, dm.to_id, dm.from_id, dm.user2read, u.name, u.user_id" .
                        " FROM users u" .
                        " JOIN direct_messages dm" .
                        " ON dm.from_id = u.user_id" .
                        " OR dm.to_id = u.user_id" .
                        " WHERE u.user_id = " . $_SESSION['user_id'];
                        " AND user2read = 'no'" .
                        " GROUP BY dm.convo_id" .
                        " LIMIT 1";



        $query_user2read = mysql_query($get_user2read);
        $alg = mysql_fetch_array($query_user2read);

?>

<?php if(COUNT($alg['user2read']) > 0 && $alg['user2read'] == 'no') : ?>

<h3>You have <?php echo COUNT($teg['user2read']); ?> new messages - from <a href="#"><?php echo $name_of_other_person; ?></a></h3>
<?php else : ?>



<?php endif; ?>

<?php

}

}
?>

I would post in image of my direct_messages table but I don't have enough reputation... Here is a diagram of my table:

Field   Type    Collation   Attributes  Null    Default Extra   Action
    convo_id    bigint(20)          No          
    message_id  int(11)         No          
    to_id   int(11)         No          
    from_id int(11)         No          
    message varchar(5000)   latin1_swedish_ci       No          
    timestamp   timestamp           No  CURRENT_TIMESTAMP       
    user1read   varchar(3)  latin1_swedish_ci       No          
    user2read   varchar(3)  latin1_swedish_ci       No

EDITED: I erased the backslashes

Sidenote: There's no mention of session_start(); anywhere. You are using it, right? ;-) If not, then you must include it inside all your pages. — Funk Forty Niner
– Funk Forty Niner, Commented Jan 16, 2014 at 1:14
Plus this and other similar looking ($alg\['user2read'\]) the way you're placing the backslashes, that doesn't seem right to me. If you're trying to escape the quotes, you need to do \' — Funk Forty Niner
– Funk Forty Niner, Commented Jan 16, 2014 at 1:17
Yes I'm using session_start(); And I did not put the backslashes in the code, it was pasted in when I pasted the code, for some odd reason. I edited and deleted the backslashes — nikito2003
– nikito2003, Commented Jan 16, 2014 at 1:22
Why do you have LIMIT 1, but then you have a while loop over the results? Don't you want to return all the user's conversations? — Barmar
– Barmar, Commented Jan 16, 2014 at 1:43
The query that I have the LIMIT 1 is not in a while loop. I have the first SELECT statement to get the conversations and then I was using the second one to get the number of new messages where user2read = 'no' — nikito2003
– nikito2003, Commented Jan 16, 2014 at 1:54

Barmar · Accepted Answer · 2014-01-16 01:40:54Z

1

This is not correct:

<h3>You have <?php echo COUNT($teg['user2read']); ?> new messages - from <a href="#"><?php echo $name_of_other_person; ?></a></h3>

count() is used for arrays, but the elements of $teg are just the column values from the table. If you want to count records grouped by a column, you need to use the MySQL COUNT() function in your query. Or in this case, since you only want to count messages that fit a specific criteria, use SUM():

$get_conversations = "SELECT dm.convo_id, dm.message_id, dm.to_id, dm.from_id, u.name, u.user_id, SUM(dm.user2read = 'no') unread_count" .
                    " FROM users u" .
                    " JOIN direct_messages dm" .
                    " ON dm.from_id = u.user_id" .
                    " OR dm.to_id = u.user_id" .
                    " WHERE u.user_id = " . $_SESSION['user_id'] .
                    " AND dm.user2read = 'no' " .
                    " GROUP BY dm.convo_id" .
                    " LIMIT 1";

Then you can use $teg['unread_count'] to get the count of unread messages in that conversation.

I don't think you need the inner query $get_user2read. All the information is in $get_conversations now. It was wrong, because it wasn't getting the row specific to the conversation in that iteration of the loop.

edited Jan 16, 2014 at 1:40

answered Jan 16, 2014 at 1:33

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

nikito2003 Over a year ago

I get an internal server error when I replace $get_conversations with this one, I don't know what I'm doing wrong

nikito2003 Over a year ago

Okay, that helped me out. The problem I'm having now is that it's only showing one conversation

Barmar Over a year ago

Did you see my comment above? Why do you say LIMIT 1 if you want more than 1 conversation?

nikito2003 Over a year ago

Yeah, I got rid of the LIMIT 1 and now I have three conversations that all say 2 new messages.

Barmar Over a year ago

I wonder why you're returning fields like dm.message_id in the query. Since you're just returning one row for each conversation, that will just pick a random message ID from the conversation. What's the point?

|

Jon · Accepted Answer · 2014-01-16 01:26:46Z

0

This $yelp = mysql_fetch_array($query_name); $name_of_other_person = $yelp\['name'\]; looks wrong.

You need to loop the $yelp like you did $teg.

answered Jan 16, 2014 at 1:26

Jon

2,2462 gold badges17 silver badges19 bronze badges

2 Comments

nikito2003 Over a year ago

That works for me, I'm able to get the name of the sender, the problem I'm having is that I can't get how many new messages there are for each conversation

nikito2003 Over a year ago

That's what I'm using <?php echo COUNT($teg['user2read']); ?> for, to get the number of new messages but it only shows the same number for each one

Collectives™ on Stack Overflow

PHP/MySQL different value for each row

2 Answers 2

15 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related