"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly. Specialization is for insects." (Robert A. Heinlein)

Friday, 30 January 2009

Names generators with MySQL

There are on the net several names generator. theese programs staring from an input name generate some kind of funny phrase.
Here is an example of a "nickname generator":
The algorithm behind these programs is quite simple: it's just matter of using your name as a key to select phrase components from one or more tables. Here is a simple way to do this using MySQL:
First let's create a couple of tables:
CREATE TABLE ADJECTIVES(
              TERM VARCHAR(30));

CREATE TABLE ANIMALS(
              NAME VARCHAR(30));
Then fill the tables with some value
INSERT INTO ADJECTIVES VALUES('Lazy');
INSERT INTO ADJECTIVES VALUES('Fat');
INSERT INTO ADJECTIVES VALUES('Crazy');
INSERT INTO ADJECTIVES VALUES('Ugly');

INSERT INTO ANIMALS VALUES('Bull');
INSERT INTO ANIMALS VALUES('Cow');
INSERT INTO ANIMALS VALUES('Monkey');
INSERT INTO ANIMALS VALUES('Chicken');
To implement the selection we can use MySQL RAND()  function. Rand function can generate a pseudo-random number sequence based on a seed integer passed as parameter. The same seed number ever generates the same sequence.
Another function HEX() let us easily convert our input string to a number we can use as seed.
The number generated from RAND() function can eventually be used to select a row in the table.
Let's put all together:
SELECT
(SELECT TERM FROM ADJECTIVES ORDER BY RAND(HEX('musante.i.ph')) LIMIT 1) AS Adjective,
(SELECT NAME FROM ANIMALS ORDER BY RAND(HEX(REVERSE('musante.i.ph'))) LIMIT 1) AS Animal;
(Update) since RAND() ever generates the same number sequence from the same seed number we must use a different seed for every word we need to extract. To do this we neeed to change the source string in order to obtain a different seed number. In my example I used the REVERSE() function to implement it (not the best choice indeed) but even simpler solutions, like adding a different constant to each, should work also well.
Here is the result:

Adjective Animal 
Crazy     Chicken

Does it fit with my blog? Probably no more than "Ultimate Monk"!
What makes the difference, in this kind of programs, is the database of names used. A wider database generates, of course , a less repetitive set of phrases but what really matters is a good choice of the words database.