Get a random del.icio.us link

Posted by Jorge Bernal March 24, 2007

I use del.icio.us a lot. I have one tag topost to save those things I find on the Internet which I want to write about later, but I have around 100 right now, so I was interested in picking random links from that list.

Bad news you can’t do that neither from the del.icio.us interface nor the API provided, so I took a different approach. I downloaded MySQLicious, a set of PHP scripts to mirror your delicious links into a local MySQL database. Once you edit your connection parameters, it’s quite easy to use:

$ ./mirrorlog.php
Update may be needed. Checking now.

Updated http://members.ebay.com/ws/eBayISAPI.dll?ViewUserPage&userid=*starlightstarbright*
Deleted eacb873e503b1b407c2ec69e541b8838

1 updated.

1 deleted.

You can also put that command into a cron task to update regularly.

Then to get a random link, you can use the following script:

#!/usr/bin/php -q
<?php
// mirror.php MySQLicious implementation
// Mirrors del.icio.us bookmarks.
// v1.01 - 8/6/2006

// MySQL configuration
$MySQL_Host		= "127.0.0.1";
$MySQL_Database	= "delicious";
$MySQL_Table	= "posts";
$MySQL_Username	= "root";
$MySQL_Password	= "";

// import the MySQLicious code
$currentDir = dirname(__FILE__)."/";
require $currentDir."MySQLicious.php";

$dbh = mysql_connect($MySQL_Host , $MySQL_Username, $MySQL_Password)
            or die("Could not connect to database server.");
mysql_select_db($MySQL_Database, $dbh) or die("Could not use database.");
$res = mysql_query("SELECT url,description
  FROM $MySQL_Table
  WHERE tags LIKE '%topost%'
  ORDER BY RAND() LIMIT 1");
$row = mysql_fetch_array($res);
printf("%s\n%s\n", $row["url"], $row["description"]);

?>

or the following command:

$ mysql -e 'SELECT url,description FROM posts WHERE tags
    LIKE "%topost%" ORDER BY RAND() LIMIT 1\G' delicious
*************************** 1. row ***************************
        url: http://www.lifehacker.com/software/podcasts/search-audio-with-pluggd-223280.php
description: Search audio with Pluggd - Lifehacker

Bookmark this post: · Del.icio.us · YahooMyWeb · Spurl · Furl · Incoming links