Solved

Grouping query results based on time proximity for Facebook-like notification feeds

Posted on 2009-04-07
2
404 Views
Last Modified: 2012-05-06
I have a social networking site where I just started logging certain user actions to a table.

Actions such as when they update their mood, edit their profile and update new photos.

I want to integrate a sort of news feed to a user's profile where it will show these updates for users they've added as their favorites.

The issue I'm running into is that if a user updates their profile four times in a half an hour, I'd like to only show that as one update.

Same  goes for photos.  If a user adds four photos within an hour, I want that to group that as one item.

The structure for the update log (update_log) is as follows:
update_id (auto)
userid INT
update_type (1=profile update, 2=photo added, 3=mood updated)
pictureid INT (joins to userphotos table with file information)
mood_a INT (joins to lookuptable that holds first part of selectable mood)
mood_b INT (joins to lookuptable that holds second part of selectable mood)
mood_c INT (joins to lookuptable that holds third part of selectable mood)
update_time INT

Typical records for the three scenarios are as follows:
Profile update:
(auto), 1,  1, 0, 0, 0, 0, 1238598393
Photo added:
(auto), 1, 2, 10000, 0, 0, 0, 1238598393
Mood updated:
(auto), 1, 3, 0, 5, 10, 15, 1238598393

I'm pretty sure this can't be done with a query as I need the individual photo file information to display thumbnails.

What's the best way to organize and sort through this information in PHP?

My brain is failing me on this.

Below is the MySQL query I wrote to grab all the pertinent data I need to display this feed for updates in the past 48 hours and attached to this post is some sample data returned from that query,
SELECT l.update_id,

l.userid,

l.update_type,

t.description,

p.filename,

l.mood_a,

a.description AS mood_a_description,

l.mood_b,

b.description AS mood_b_description,

l.mood_c,

c.description AS mood_c_description,

l.update_time

FROM update_log l

JOIN update_types t

ON l.update_type = t.code

LEFT JOIN userphotos p

ON l.pictureid = p.pictureid

LEFT JOIN lookuptables a

ON l.mood_a = a.code AND

a.tableid = 'mood_a'

LEFT JOIN lookuptables b

ON l.mood_b = b.code AND

b.tableid = 'mood_b'

LEFT JOIN lookuptables c

ON l.mood_c = c.code AND

c.tableid = 'mood_c'

WHERE FROM_UNIXTIME(update_time) > now() - INTERVAL 48 HOUR

ORDER BY l.update_time DESC

Open in new window

testdata.txt
0
Comment
Question by:HarpuaFSB
2 Comments
 
LVL 11

Accepted Solution

by:
bansidhar earned 500 total points
ID: 24094375
Here is some Idea you can use.

Use
ORDER BY update_type.update_time DESC

here you will get all the records ordered by the type first and inside that ordered by time. Now you can loop through the records and add items to an array. So you can control which items goes to which list. Some Pseudo code:

$updates = array();
$last_hr=0;
$last_item='';
while ($row=mysql_fetch_assoc($result)){
   if ($row['update_time'] - $last_hr > $required_time_limit || $row['update_type'] != $last_item){
      $last_hr = $row['update_time'];
      $last_item = $row['update_type'];
   }
   $updates[$last_item][$last_hr][] = $row;
}

Don't expect this code to work off-the-shelf you have to improvise on this.
0
 

Author Comment

by:HarpuaFSB
ID: 24098306
Tweaking the code in your suggestion yielded me some good code that works great for profile and mood updates.

I get a nice list of profile updates per user that omit ones made within an hour of each other and mood updates that are made within a minute of each other (kind of like a flood protection).

However, I'm still trying to figure out how to handle photo additions (update_type = 2).

What I want to do is group all photo uploads per user that have been uploaded within an hour of each other to display as one "update" line item.

I'm thinking adding a record to the array that links to another array?

Any suggestions?

I've attached the current working code for mood and profile updates.
<?php

	$sql = "SELECT DISTINCT l.userid, ";

	$sql .= "u.displayname, ";

	$sql .= "l.update_type, ";

	$sql .= "t.description, ";

	$sql .= "p.pictureid, ";

	$sql .= "p.filename, ";

	$sql .= "p.albumid, ";

	$sql .= "l.mood_a, ";

	$sql .= "a.description AS mood_a_description, ";

	$sql .= "l.mood_b, ";

	$sql .= "b.description AS mood_b_description, ";

	$sql .= "l.mood_c, ";

	$sql .= "c.description AS mood_c_description, ";

	$sql .= "l.update_time ";

	$sql .= "FROM update_log l ";

	$sql .= "JOIN users u ";

	$sql .= "ON l.userid = u.userid ";

	$sql .= "JOIN update_types t ";

	$sql .= "ON l.update_type = t.code ";

	$sql .= "LEFT JOIN userphotos p ";

	$sql .= "ON l.pictureid = p.pictureid ";

	$sql .= "LEFT JOIN lookuptables a ";

	$sql .= "ON l.mood_a = a.code AND ";

	$sql .= "a.tableid = 'mood_a' ";

	$sql .= "LEFT JOIN lookuptables b ";

	$sql .= "ON l.mood_b = b.code AND ";

	$sql .= "b.tableid = 'mood_b' ";

	$sql .= "LEFT JOIN lookuptables c ";

	$sql .= "ON l.mood_c = c.code AND ";

	$sql .= "c.tableid = 'mood_c' ";

	$sql .= "WHERE l.userid IN (SELECT favoriteuserid FROM userfavorites WHERE userid = " . $_SESSION["userid"] . ") AND ";

	$sql .= "FROM_UNIXTIME(update_time) > now() - INTERVAL 48 HOUR ";

	$sql .= "ORDER BY l.update_type, ";

	$sql .= "l.userid, ";

	$sql .= "l.update_time DESC";

	$recordset = mysql_query($sql);

	$update_log = array();

	$profile_time_limit = 3600;

	$mood_time_limit = 60;

	$last_update_time = 0;

	$last_userid = 0;

	while ($row = mysql_fetch_assoc($recordset))

	{

		if ($row["update_type"] == 1)

		{

			if (($last_update_time - $row["update_time"]) / $profile_time_limit > 1 || ($last_update_time - $row["update_time"]) / $profile_time_limit < 0) 

				{ $update_log[] = $row;	}

			else if ($last_userid != $row["userid"])

				{ $update_log[] = $row;	}

		}

		else if ($row["update_type"] == 3)

		{ 

			if (($last_update_time - $row["update_time"] > $mood_time_limit) || ($last_update_time - $row["update_time"] < 0))

				{ $update_log[] = $row;	}

			else if ($last_userid != $row["userid"])

				{ $update_log[] = $row;	}

		}

		$last_update_time = $row["update_time"];

		$last_userid = $row["userid"];

	}

	usort($update_log, "sort_array");

?>

	<table>

		<tbody>

<?php

	// test dump of array contents

	$count = 0;

	for ($count = 0; $count < sizeof($update_log); $count++)

	{

?>

		<tr>

			<td><?php echo $update_log[$count]["userid"]; ?></td>

			<td><?php echo $update_log[$count]["displayname"]; ?></td>

			<td><?php echo $update_log[$count]["update_type"]; ?></td>

			<td><?php echo $update_log[$count]["description"]; ?></td>

			<td><?php echo $update_log[$count]["pictureid"]; ?></td>

			<td><?php echo $update_log[$count]["filename"]; ?></td>

			<td><?php echo $update_log[$count]["albumid"]; ?></td>

			<td><?php echo $update_log[$count]["mood_a"]; ?></td>

			<td><?php echo $update_log[$count]["mood_a_description"]; ?></td>

			<td><?php echo $update_log[$count]["mood_b"]; ?></td>

			<td><?php echo $update_log[$count]["mood_b_description"]; ?></td>

			<td><?php echo $update_log[$count]["mood_c"]; ?></td>

			<td><?php echo $update_log[$count]["mood_c_description"]; ?></td>

			<td><?php echo $update_log[$count]["update_time"]; ?></td>

			<td><?php echo date("Y-m-d H:i:s", $update_log[$count]["update_time"]); ?></td>

		</tr>

<?php

	}

?>

		</tbody>

	</table>

<?php

	function sort_array($x, $y)

	{

		if ($x["update_time"] == $y["update_time"])

			{ return 0; }

		else if ($x["update_time"] < $y["update_time"])

			{ return 1; }

		else

			{ return -1; }

	}

?>

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now