Recursive tables and listing data

I have a table that refers to itself to store categories or locations of items.

Table defintion:

+------------+---------------------------+------+-----+---------+----------------+
| Field      | Type                      | Null | Key | Default | Extra          |
+------------+---------------------------+------+-----+---------+----------------+
| locationID | int(10) unsigned zerofill |      | PRI | NULL    | auto_increment |
| Location   | varchar(128)              |      | MUL |         |                |
| foundWhere | int(10) unsigned zerofill | YES  |     | NULL    |                |
+------------+---------------------------+------+-----+---------+----------------+

Test Data:

+------------+------------------------------+------------+
| locationID | Location                     | foundWhere |
+------------+------------------------------+------------+
| 0000000001 | Australia                    |       NULL |
| 0000000002 | Western Australia            | 0000000001 |
| 0000000003 | New South Wales              | 0000000001 |
| 0000000004 | Australian Capital Territory | 0000000001 |
| 0000000005 | Perth                        | 0000000002 |
| 0000000006 | Canberra                     | 0000000004 |
| 0000000007 | Sydney                       | 0000000003 |
| 0000000008 | Kalgoorlie-Boulder           | 0000000002 |
| 0000000009 | Bayswater                    | 0000000002 |
| 0000000010 | Bassendean                   | 0000000002 |
| 0000000011 | South Australia              | 0000000001 |
| 0000000012 | Britain                      |       NULL |
| 0000000013 | London                       | 0000000012 |
| 0000000014 | Queensland                   | 0000000001 |
| 0000000015 | United States of America     |       NULL |
| 0000000016 | San Francisco                | 0000000015 |
| 0000000017 | Victoria                     | 0000000001 |
| 0000000018 | Melbourne                    | 0000000017 |
+------------+------------------------------+------------+

I'm using ADOdb for the database abstraction layer with mYSQL as the back-end.

I have a couple of questions:

1) How can I create a list of categories like:

• Melbourne -> Victoria -> Austrlia


• Perth -> Western Austrlaia -> Australia


• London -> Britain

2) How can I retrieve the categories that contain a certain item of text, eg: "CAN"?

3) How could I add a location to more than one other upper level location (this is for another problem, but may as well use the opportunity to ask)?


Many thanks in advance for the assistance

Ady