Data-mining users in a screenful of code


Select like-minded users from a local community website.


  1. A Drupal website with the votingapi module enabled and at least a few dozen votes by registered users.
  2. A working installation of the R language.

Exract data

For each user, select all other users that voted on same node and comments:

SELECT v1.uid uid1, v2.uid uid2, name1, name2,
  v2.entity_id entity_id, v1.value value1, v2.value value2
FROM votingapi_vote v1
JOIN (votingapi_vote v2, users u1, users u2)
 ON (v1.uid != v2.uid AND v1.entity_id=v2.entity_id
   AND v1.entity_type=v2.entity_type AND v1.uid=u1.uid AND v2.uid=u2.uid)
WHERE v1.uid 

This produces a table

OK… Here's another way to add random signatures to Gmail. Now using Google Apps Script.

It almost works. It will update signatures that you store in a Google Sheet at regular intervals. The only problem is… You have to be a Google Apps for Business administrator to run this thing, because the only way to update a Gmail signature programmatically is via the Google Apps for Business Admin SDK. Ha-ha. Another problem is tha the signature only gets updated when you refresh the page. But that's a minor thing ;-)

Check it out and make a copy to have access to the script.. And here's the code:

How to force Drupal users into answering a poll

Here, the poll is hardcoded at node/38566 and the patch applies to index.php in the root of Drupal installation.

Tested on Drupal 6.14, but shall work on many other versions as well.

--- index-original.php      2007-12-26 09:46:48.000000000 +0100
+++ index.php   2009-11-23 16:12:11.000000000 +0100
@@ -33,6 +33,12 @@
 elseif (isset($return)) {
   // Print any value (including an empty string) except NULL or undefined:
+  global $user;
+  $result = db_fetch_object(db_query('SELECT nid FROM {poll_votes} WHERE nid = 38566 AND uid = %d', $user->uid));
+  if ($user->uid > 0 && !$result && $_GET['q'] != 'node/38566') {
+    drupal_goto('node/38566', NULL, NULL, 301);
+  }
   print theme('page', $return);