2

I need a database guru. I'm trying to solve a problem that I'm pretty sure can be solved with a simple query I cant figure out. First, this is the table ve_messages:

MessageID | RecieverID | SenderID | Message | SentOn

I use a while statement to display the messages inside dynamic bootstrap panels but the query below will generate one panel every time the user receives a message which is good. But I need something more specific. I'm trying to gather all the messages from the same user inside one panel instead of creating a new one everytime so that the users can view all the messages that were sent so that they can follow the conversation. How can I do that? I tried GROUP BY SenderID but then I cannot access all the messages...

<div class="panel-group" id="accordion">
       <?php 
       $i=1;
        $inboxmsgQ="SELECT * FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID WHERE m.RecieverID= '$loginid_session' ";
        $inboxmsgresult=mysqli_query($db,$inboxmsgQ);
        while($inbox=mysqli_fetch_array($inboxmsgresult)){$i++?>
      <?php 
      $checkavatareview=mysqli_query($db,"SELECT * FROM ve_photos p WHERE p.UserID={$inbox['SenderID']} AND IsPrimaryPhoto='1' ORDER BY InsertDate LIMIT 1");
       $rowviewe = mysqli_fetch_array($checkavatareview,MYSQLI_ASSOC);
      ?>
    <div class="panel panel-default">
      <div class="panel-heading">
        <h4 class="panel-title">
          <a data-toggle="collapse" data-parent="#accordion" href="#collapseM<?=$i;?>"><?=$inbox['ProfileName'];?> | <?=$inbox['SentOn'];?></a>
        </h4>
      </div>
      <div id="collapseM<?=$i;?>" class="panel-collapse collapse">
        <div class="panel-body">
               <a href="profile.php?viewuserprofile=<?=$inbox['SenderID'];?>" >
              <?php if(mysqli_num_rows( $checkavatareview)==0):?>
          <img style="width: 15% !important;" class="tableimg" src="uploads/nophoto.png" alt="profilename">
              <?php elseif(mysqli_num_rows( $checkavatareview)>0):?>
           <img style="width: 15% !important;" class="tableimg" src="core/<?=$rowviewe['PhotoPath'];?>" alt="profilename">
           <?php endif;?>
          </a>
            <hr>
   <?=$inbox['Message'];?>
        </div>
      </div>
    </div>
    <?php };?>
  </div> 

1 Answer 1

1

If I'm following, you want to have one panel per user the current user has exchanged messages with, and show all messages from that person in that panel.

To get all people the current user has received messages from (broken into multiple lines for readability - you can put it in one line in your code):

SELECT SenderID, ProfileName
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
 GROUP BY UserId, ProfileName
;

Loop through these results to create your panels.

You'll need a second loop within each panel to show each message, using the results of this query:

SELECT *
  FROM ve_messages m JOIN ve_users u ON m.SenderID=u.UserID
 WHERE m.RecieverID= '$loginid_session'
   AND m.SenderID = {$inbox['SenderID']}
 ORDER BY SentOn DESC
;

You may actually want to change the name of the $inbox associative array, as it now just has the ID and profile names of senders.

The ORDER BY assumes that you'd want the most recent messages at the top; change DESC to ASC to flip that.

1
  • Saved my life bro!
    – Mars
    May 18, 2017 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.