Member Avatar for diafol

Hi all. Have been trying to get my head around a problem in my custom messaging system. This is my msg table:

id (PK)
datetime_posted (datetime)
from_id (FK for user id)
to_id (FK for user id)
title (the title obviously)
msg (the body)
status (0 = inbox, 1 - removed)
read_status (0 = unread, 1 = read)

The system currently allows me to fwd, reply, remove and send new msgs. It also sends msgs to multiple recipients (no cc or bcc req'd). So if I send a msg to 3 users, 3 new records appear in the table (identical) except for the to_id field.

I have a ajaxified page that allows 'inbox' and 'sent' views. The inbox is fine, but the sent view shows every msg sent, so 3 msgs will show in response to a single msg to 3 recipients.

see screenshot (sorry in Welsh, but you'll get the idea)

I'm looking to create a html table with the following structure:

datetime
title
recipeints with 'read_status' following their names

Something like this: screenshot2

I've fiddled with GROUP BY and subqueries, but they look a bit bloated and take a while to run.

I'd be grateful for some SQL advice. Thanks.

Member Avatar for diafol
<?php
$q = "SELECT DISTINCT dated, from_id, title, msg FROM cymer_msgs ORDER BY dated DESC";
$r = mysql_query($q);
while($d = mysql_fetch_array($r)){
	$q2 = "SELECT to_id, read_id FROM cymer_msgs WHERE dated = '{$d['dated']}' AND from_id = '{$d['from_id']}'";
	$r2 = mysql_query($q2);
	$add = "";
	while($d2 = mysql_fetch_array($r2)){
		$add .= $d2['to_id'] . " ({$d2['read_id']}) "; // list user id with read or unread following
	}
	//temp output
	echo $d['dated'] . ": " . $d['title'] . " to: " . $add . "<br />";//datetime with title then list
}
?>

This seems to work, but I can't help feeling that there must be a more subtle solution. This bit of code is really horrible, takes me back a few years when I started out with php.

Any improvements would be gratefully received.

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.