subqueries make life easy

often times while working with databases, you find the need to do some simple tasks (adding a column and populating it with a value from another table, etc). rather than write a script to do this, use mysql’s native subquery (and temporary table) functionality - it makes life much easier.

consider the case where you have two tables:

mysql> describe colors;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       | 
| color | varchar(10) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and

mysql> describe color_mapping;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| picture_id | int(11) | YES  |     | NULL    |       | 
| color_id   | int(11) | YES  |     | NULL    |       | 
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

let’s say you want to update the colors table to add a frequency column such that you know how often a given color is used. it turns out this is really easy using mysql:

alter table colors add frequency int;
update colors set frequency = (select count(*) from color_mapping \
   where color_mapping.color_id = colors.id);

and that’s it. also useful are temporary tables:

create temporary table color_frequencies select color_id, count(*) as cnt \
   from color_mapping group by color_id;

if you then attempt to desc color_frequencies, you’ll see a table with two columns - a color_id and a cnt column.

hopefully this will save some people some efforts writing scripts next time some simple database updates are needed :)

my previous keyboard

[center]note the v, n, and m keys (or lack thereof)[/center]

finally replaced my keyboard with a new one :)

quran plugin updated for ubiquity 0.5

i’ve done a quick port of the quran ubiquity plugin to work under the new version of ubiquity (0.5). as you may know, ubiquity 0.5 and beyond use a new parser (parser 2) that isn’t compatible with the old parser (parser 1 for 0.1.x versions of ubiquity).

you can get it here. make sure to select “automatically update this feed” so that you get any updates i may get around to making.

this version is pretty much identical to the older one, except that now, you can use “get-ayah,” “get ayah,” or “ayah” to get an ayah, and “search-quran” or “search quran” to do a search. i hope to support some of the new stuff from the pre-alpha version of the quran api soon insha’Allah (other translations, etc).

فول أحمد العظيم

inspired by the blog of sy and my previous roommate (who makes a mean paneer btw), i decided to start posting my chef de jour recipes for the benefit of all the bachelors out there. unfortunately, i don’t cook as much as i should (but i have been fairly lucky in terms of the output of my few attempts). so perhaps documenting my experiences as a master chef will help me to stop slacking and cook more.

what is this: this is a dish known as “فول” in arabic (pronounced ‘fool’). it’s a bean dish famous in the middle east as a breakfast or late night snack.

ingredients:

• 1 (or more) cans of fava beans

• 1 (or more) cans of sliced tomatoes

• lemon juice

• tahina (ground sesame seed)

these ingredients can mostly be obtained at any middle eastern or desi grocery store.

instructions:

• obtain a pot.
• open beans and put beans in the pot.
• add tomatoes to the pot.
• mix together well.
• add a little bit of tahina to the pot - enough to make it slightly lighter color, but not too much. maybe 2 or 3 tablespoons at most
• heat together and mix well.
• add lemon on top and serve (with pita bread usually).

for an upgraded experience: of course if you want to go all out, try replacing the canned tomatoes with fresh tomatoes, the canned beans with fresh ones that you must first leave in water for some time, and replace the lemon juice with real lemon. experts can try placing the aforementioned mixture with the fresh ingredients into a pressure cooker for better results.

the meaning of the title: the title, which means ahmed’s great fool, is a pun on the arabic comedy movie, “فول الصين العظيم”, in which the main character cooks fool in a cooking competition in china.

introducing waqt.org

today, i took the arabeyes php extension of itl, the yahoo geocoding api, and the geonames api and put together waqt.org.

it’s a fairly minimalistic prayertimes site. the code is available on github. note that the calculation method is currently hardcoded to use the isna method, but this is fairly easy to change.

amazing quran marketing

about two years ago, i posted about sheikh mohamed’s amazing fajr marketing tshirt. today, i am posting about the latest tshirt in the collection - read, understand, and practice quran.

sheikh is currently in egypt visiting his family and will go and make 3umrah insha’Allah… the bay area really isn’t the same without him. may Allah accept from them and grant him and his family the very best and bring them back safely - ameen.

quran ubiquity plugin

updated and released the first version of the quran ubiquity plugin! you can go here to install it.

essentially, it contains two commands -

1. search-quran - takes a parameter of what to search for and will show the results that match that particular query. hitting enter will bring up the search results page.
2. get-ayah - takes a parameter of which ayah (ex 2:2) and an optional parameter of the language/translation you want the ayah in (in english - muhsin khan, for example - note that ubiquity will provide suggestions for these). hitting enter will insert the text into the selection space.

this is uber-useful for muslims imho :p perhaps i will try to provide a screencast later on that shows how to use this for those who are still afraid to try it :)

update - rather than make my own screencast, i’ve decided to record a set of audio instructions on how to use it.

by the way - if you haven’t used ubiquity before, i highly recommend that you watch this video first. it explains what ubiquity is and gives you an idea of what it is useful for. to put it quite simply, ubiquity is amazing. it’s an indispensable tool for your firefox. watch the video :)

and here is the audio tutorial on the quran plugin for ubiquity.

enjoy!

website changes

after having pretty much the same theme since the launch of the site, i’ve switched to the excellent iNove wordpress theme. i’ve also cleaned up some links and replaced the previous code highlighter with the very nice syntax highlighter evolved plugin.

migrating jaiku to identi.ca or twitter

i recently decided to move my tech tips microblog to identi.ca (the original copy was on jaiku), as i felt it was a little more befitting, actively developed, etc (although jaiku is now open source).

anyway… so i wanted to migrate my posts over - so i wrote a php script to do it (it assumes your jaiku is public and reads it without hassling with oauth).

<?php
$sleepTime = 5;
$jaikuSource = "http://username.jaiku.com/json";
 
$mode = 'identi.ca';
$baseStatusUrl = 'http://identi.ca/api/statuses/update.json';
 
// thanks, php-twitter
if ($mode == 'twitter'){
   $baseStatusUrl = 'http://twitter.com/statuses/update.json';
   $headers = array('Expect:', 'X-Twitter-Client: ',
      'X-Twitter-Client-Version: ','X-Twitter-Client-URL: ');
}
 
$ctr = 0;
$entries = array();
 
print "destination account username: ";
$username = trim(fgets(STDIN));
print "password: ";
system('stty -echo');
$password = trim(fgets(STDIN));
system('stty echo');
print "\n";
 
$done = false;
$params = '';
while (true){
   $count = 0;
   $posts = fetchUrl($jaikuSource . $params);
   $json = json_decode($posts, true);
   $stream = $json['stream'];
   $lastEntry = null;
   foreach ($stream as $entry){
      if (isset($entry['comment_id'])) continue;
      $lastEntry = $entry;
      $count++;
      $entries[$ctr++] = $entry['title'];
   }
   if ($count == 0) break;
   $lastPostTime = $lastEntry['created_at'];
   $ts = split('-', $lastPostTime);
   $hd = split('T', $ts[2]);
   $min = split('Z', $ts[4]);
   $gmtime = gmmktime($hd[1], $ts[3], $min[0], $ts[1], $hd[0], $ts[0]) - 1;
   $params = "?offset=$gmtime";
}
 
for ($i = $ctr-1; $i>=0; $i--){
   $params = array('status' => $entries[$i]);
   if ($i != ($ctr-1)){
      print "sleeping $sleepTime seconds\n";
      sleep($sleepTime);
   }
   twitterApiCall($baseStatusUrl, $params);
   print "updated status to: " . $entries[$i] . "\n";
}
 
function fetchUrl($url){
   $ch = curl_init($url);
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
   $resp = curl_exec($ch);
   curl_close($ch);
   return $resp;
}
 
function twitterApiCall($url, $args = null){
   global $username, $password, $headers;
 
   // thanks, php-twitter
   $ch = curl_init($url);
   if (!is_null($args)){
      curl_setopt($ch, CURLOPT_POST, true);
      curl_setopt($ch, CURLOPT_POSTFIELDS, $args);
   }
   if ((!empty($username)) && (!empty($password)))
      curl_setopt($ch, CURLOPT_USERPWD, $username . ':' . $password);
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
   if (!empty($headers))
      curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
   $resp = curl_exec($ch);
   $info = curl_getinfo($ch);
   curl_close($ch);
   if ($info['http_code']!=200)
      print "error - got an http code of: " . $info['http_code'] . "\n";
}

make sure you edit $baseStatusUrl and $mode as necessary. enjoy!

bbc interview with idf spokeswoman

i usually start my weekdays by listening to the excellent bbc global news podcast. in today’s episode, bbc’s owen bennett-jones interviewed an idf spokeswoman regarding israeli troops’ admission to gaza abuses. i decided to cut it out and provide it here for you to listen to.

audio

is it just me, or do these people seem heartless? anyway, you can download the original podcast here.