I have a PHP function to create those famouse AlphaID representations of integers as follows:
function alphaID( $input ) {
$index = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$base = strlen( $index );
$input += pow( $base, 4 );
$output = '';
for( $i = floor( log( $input, $base ) ); $i >= 0; $i-- ) {
$bcp = bcpow( $base, $i );
$start = floor( $input / $bcp ) % $base;
$output .= substr( $index, $start, 1 );
$input = $input - ( $start * $bcp );
}
return $output;
}
Encoding, for example, the maximum integer provided by PHP_MAX_INT constant (2147483647) would return cwuCBb
But I thought it was a little too slow in the whole application picture so I tried to create a MYSQL function so, theoretically, I wouldn't waste performance doing such conversion with PHP once data would come ready for me while querying.
The MySQL manual is not exactly friendly, but searching here and there I came up to this:
DROP FUNCTION IF EXISTS ENCODE_ALPHAID;
DELIMITER $$
CREATE FUNCTION ENCODE_ALPHAID( input integer ) RETURNS CHAR( 6 ) DETERMINISTIC
BEGIN
DECLARE output CHAR( 6 );
DECLARE letters CHAR( 62 );
DECLARE base TINYINT( 2 );
DECLARE iterator TINYINT( 2 );
DECLARE bcp CHAR( 9 );
DECLARE start TINYINT( 2 );
SET output = '';
SET letters = 'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET base = CHAR_LENGTH( letters );
SET input = input + POW( base, 4 );
SET iterator = FLOOR( LOG( base, input ) );
ENCODING: LOOP
SET bcp = POW( base, iterator );
SET start = FLOOR( input / bcp ) % base + 1;
SET output = CONCAT( output, SUBSTR( letters, start, 1 ) );
SET input = input - ( start * bcp );
SET iterator = iterator-1;
IF iterator < 0 THEN LEAVE ENCODING; END IF;
END LOOP ENCODING;
RETURN output;
END $$
DELIMITER ;
But when I ran it through the MySQL manager I use (DBNinja), apparently, nothing happens. After run the query it shows 0 row affected and I'm not sure if this is the expected output or not.
Right or not, the function didn't work either way because when I tried to use it in a query:
SELECT ENCODE_ALPHAID( `c`.`cid` ) from `table` c WHERE `c.user` = 1
I first received an error saying table.ENCODE_ALPHAID didn't exist. Then I noticed that, indeed, I created the function while in a different database of the one I was trying to use.
But I created it again, in the proper database, but the same error came up.
So here are the questions:
- What did I do wrong? This is the very first time I try to do something like that and I'm not exactly sure
- My implementation of the PHP code is a valid MySQL counterpart? Did I miss anything?
- Can I make this function "global"? I mean, available to any database I need it? Because for the application I'm working on, in order to keep the two subsystems involved separated, I created two databases.
Updated
Apparently the problem with the function not being created is on DBNinja's end, because I ran the statement directly in MySQL console and, although with the same output status of 0 rows affected, I was able to effectively use the function in a statement, so the function was created afterall.
However, something is wrong with the routine itself because when I tested it, the resulting string was... "one ahead".
For example, if I run the following PHP code:
echo alphaID( 2 );
It would produce baaac, but the MySQL function is returning baaad, which would be the AlphaID representation of the integer 3
Not only this is wrong, but I think it could also overflow the INT type used when reaching the maximum 2147483647