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:

A old but still useable phpbb2drupal conversion script

This one appears high in Google SERPs on phpbb2drupal.

{syntaxhighlighter brush:sql}
--- Written by Feodor (feodor [at]
--- Modified for drupal 4.5.2 and phpbb 2.0.11-2.0.13
--- by Alexander Mikhailian
--- This script makes an assumption that phpbb and drupal tables are kept in
--- one and the same database. Phpbb tables are expected to have the prefix
--- phpbb_ and drupal tables are expected to have the prefix drupal_
--- If phpBB forum use CP1251 (or another) encoding, the tables must be converted
--- into UTF8. If version of MySQL less then 4.1 "iconv" command can be used for
--- convertion of exported tables into UTF8.
--- Example:
--- iconv -fcp1251 -tutf8 phpbb2_utf-8.sql
--- Here is a list of phpbb tables used by script for import into Drupal:
--- phpbb_categories
--- phpbb_forums
--- phpbb_posts
--- phpbb_posts_text
--- phpbb_users
--- phpbb_vote_desc
--- phpbb_vote_results
--- You should probably edit the two variables below to match your result:

--- The name of the forums taxanomy as it appears on the site
SELECT @forum_title:='Forums';

--- Start importing users from this id. Do not forget that uid 1 is always
--- the Administrator in Drupal. Depending on whether you already created
--- the Administrator user in Drupal or not, you may want to change this
--- variable into 2.
SELECT @first_phpbb_user_id:=1; # uid 1 is always an administrator in Drupal


How to force Drupal users into answering a poll

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

<p>Tested on Drupal 6.14, but shall work on many other versions as well.</p>

{syntaxhighlighter brush:diff}
--- 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);