Member Avatar for zacharysr

I am making a online social networking site, and have messging section, i want it to make it show only 1 messge from each user, So if you have a message from

for ex
Username "bob"
id "32"

and you have 5 messages from him

Then you have 20 messages from
username: heather
id: 63

then you have 12 messages from
username: tony
id: 21

You will only have 3 messages in your inbox 1 from each of these users. And each one displays 120 characters from the last message they sent.

How could i do this, then when they click on the message it will take them some where else to see there whole conversation?

this is the code i have for getting the information from the databse and echo it out.

$ID = $_COOKIE['idCookie'];
            $query = mysql_query("SELECT * FROM  `private_messages` WHERE to_id LIKE  '$ID' AND To_Deleted LIKE '0' ORDER BY id DESC"); 
            echo "<table width='100%' border='1'>";
            $num = mysql_num_rows($query);
            if ($num > 0){
            while($row = mysql_fetch_assoc($query)){
            $id = $row['id'];
            $subject = $row['subject'];
            $toid = $row['to_id'];
            $read = $row['opened'];
            $fromid = $row['from_id'];
            $message = $row['message'];
            $id = $row['id'];
            $check_pic = "members/$fromid/image01.jpg";
            $default_pic="members/0/default.png";
                if(file_exists($check_pic)){
                $user_pic ="<img src=\"$check_pic\" width=\"50\" heign='50'>";
                }else{
                    $user_pic ="<img src=\"$default_pic\" width=\"50\" heign='50'/>";
                    }
                    if ($toid==$myid){
                        $query2 = mysql_query("SELECT * FROM `members` WHERE id LIKE '$fromid'");
                        while ($row = mysql_fetch_assoc($query2)){
                        $firstname = $row['firstname'];
                        $lastname = $row['lastname'];
                        if ($read =='1'){
                        $read = "Read";
                        }else{
                        $read="<a href='messages.php?Read=$id'>Mark as read</a>";
                        }
                        echo "<tr><td width='52'>$user_pic</td><td>From: <a href='/profile.php?id=$fromid'>$firstname $lastname</a> &nbsp; - &nbsp; <a href='messages.php?removeid=$id'><font color='red'>Delete</font></a> &nbsp; - &nbsp; $read
                        </br><hr>$message</td></tr>";
            //}
            }
            }
            }
            }else{
            echo "Your inbox is empty.";
            }
            echo "</table>";
  • 3 Contributors
  • 4 Replies
  • 197 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by zacharysr

Dani AI

Generated

Goal: show one latest message per sender with a 120-character preview and a link to open the full conversation. 's LIMIT 1 would return only a single inbox row overall, and was right to flag using equality for id checks instead of LIKE. The robust way is to pick the newest message id for each sender and then pull those rows — this avoids nondeterministic GROUP BY behavior and scales better than running one query per row.

A reliable approach (works with modern MySQL and PHP PDO/mysqli) is: 1) run a grouped subquery that gets MAX(id) per from_id for the current user, 2) join that back to private_messages to retrieve the full message row, and 3) join members for display data. Example PDO pattern:

/* fetch latest message from each sender (PDO) */
$sql = "
SELECT pm.*, m.firstname, m.lastname
FROM private_messages pm
INNER JOIN (
  SELECT from_id, MAX(id) AS maxid
  FROM private_messages
  WHERE to_id = :me AND To_Deleted = 0
  GROUP BY from_id
) latest ON pm.id = latest.maxid
LEFT JOIN members m ON m.id = pm.from_id
ORDER BY pm.id DESC
";
$stmt = $pdo->prepare($sql);
$stmt->execute([':me' => $myId]);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $snippet = (mb_strlen($row['message']) > 120)
        ? mb_substr($row['message'], 0, 120) . '...'
        : $row['message'];

    echo '<tr><td><img src="members/' . intval($row['from_id']) . '/image01.jpg" width="50" /></td>'
       . '<td>From: ' . htmlspecialchars($row['firstname'].' '.$row['lastname'])
       . ' - <a href="messages.php?with=' . intval($row['from_id']) . '">Open</a><br>'
       . htmlspecialchars($snippet) . '</td></tr>';
}

Practical tips: add an index on (to_id, from_id, id) for performance; use prepared statements to avoid SQL injection; use mb_substr for UTF-8-safe previews; mark the whole conversation read by updating all rows for that from_id/toid pair rather than a single message; consider introducing a conversation/thread id if the app will expand. This addresses the points raised by and while replacing per-row member queries and deprecated mysql* usage with a single efficient query.

Recommended Answers

All 4 Replies

Member Avatar for almostbob

SELECT * FROM private_messages WHERE to_id LIKE '$ID' AND To_Deleted LIKE '0' ORDER BY id DESC limit 1

Member Avatar for zacharysr

Almostob, that would only show 1 altoghether, i want to show one from each member, so say you have 20 friends messaging you, and say you have 2 messages from each member, instead of 40 messages showing in your inbox, you would only have 20 in your inbox.

Member Avatar for simplypixie

Your queries are wrong - you should be using = NOT LIKE when selecting by matching id (or any other data). LIKE is for finding similar data.

With regard to selecting one message for each batch of messages:

$query = mysql_query("SELECT * FROM  `private_messages` WHERE to_id='$ID' AND To_Deleted=0 ORDER BY id DESC LIMIT 1");
Member Avatar for zacharysr

Simplypixie, i do not wnat to limit the inbox to 1 post altogether, i want it to be kinda like facebook, where they show 1 message from each person who sent you a message, Then when you click on it it will show you your whole conversation....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.