antoshka — Fri, 23 Jan 2004 05:50:52 +0000

there is a method of indexing data (forgot what it's called) that can somewhat help you. here's a simplified version:

create a new field in the table. if an employee name starts with an A, assign 1 to the field. if it's a B, assign 2. and so forth. then, sort by this new field when SELECTing from the DB. it will not always yield precise results (depends), but still it's something.

you can play around with it. create several new fields, each for an encrypted data field. you can have several numbers there. for instance, look at this correspondence:

AA - 1
AB - 2
AC - 3
...
AZ - 26
BA - 27
BB - 28
BC - 29
BD - 30
...
BX - 50
BY - 51
BZ - 52
CA - 53
CB - 54
...

you see the pattern

Mark Hensler — Mon, 05 Jan 2004 18:19:42 +0000

The easiest way would be to not encrypt the data you need to sort by.

Or consider...

CREATE TABLE `tmp` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `str` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO tmp SET str=ENCODE("one", "key");
INSERT INTO tmp SET str=ENCODE("two", "key");
INSERT INTO tmp SET str=ENCODE("three", "key");
INSERT INTO tmp SET str=ENCODE("four", "key");
INSERT INTO tmp SET str=ENCODE("five", "key");

SELECT id, DECODE(str, "key") AS str FROM tmp ORDER BY str ASC;

+----+-------+
| id | str   |
+----+-------+
|  5 | five  |
|  4 | four  |
|  1 | one   |
|  3 | three |
|  2 | two   |
+----+-------+