Can't get the keys in a database to parse as part of an array

Question

I have an table in my database which so far has been great for storing stuff in more than one object. But i want to be able to transform it into a multi-object array thingy.

Here is the data which is related to this new 'object' (in mysql):

    uid     field              value
    page:1  shop[2].location    In Shops, Dundas Arcades,Middlesbrough, TS1 1HT
    page:1  shop[1].location    5a High Street, Stockton-on-tees, TS18 1UB
    page:1  name                Enter The Asylum
    page:1  contact.website     http://entertheasylum.co.uk
    page:1  contact.phone       0800 090 090

Now what i'm looking for is to via PHP transform it into something like (print_r output):

array(
   "name" => "Enter The Asylum",
   "shop" => array(
      array("location" => "In Shops, Dundas Arcades..."),
      array("location" => "5a High Street, Stockton-on-tees...")
   ),
   "contact" => array(
      "website" => "http://entertheasylum.co.uk",
      "phone" => "0800 090 090"
   )
 )

anybody got any ideas?

Joe

Answer 1

I can't help you with the original problem, but I'll suggest a proper way to use relational databases like MySQL. That is, you tables should have a structure like this:

Table page:

• id PK
• name
• phone
• website

Table page_location:

• id PK (this is technically not necessary, but nicer than having a primary key on position)
• page_id FK (references page.id)
• position
• location

Data would then look something like this.

Table page:

| id | name               | phone          | website                       |
| 1  | "Enter The Asylum" | "0800 090 090" | "http://entertheasylum.co.uk" |

Table page_position:

| id | page_id | position | location                                          |
| 1  | 1       | 1        | "5a High Street, Stockton-on-tees, TS18 1UB"      |
| 2  | 1       | 2        | "In Shops, Dundas Arcades,Middlesbrough, TS1 1HT" |

And some additional reading: Relational model

Answer 2

I've managed to do this via regex and php. Here is the magic function (it's part of my code and stuff):

function fetch_profile_info($id){
    $sql = "SELECT * FROM profile_info WHERE `uid`='".me($id)."'";
    $r = mysql_query($sql);
    $profile = array();
    while($row = mysql_fetch_array($r)){
     /* new: process it into objects etc */
     $field = $row['field'];
     $field = preg_replace('/\.([a-zA-Z]*)/i', "['$1']", $field);
     $field = preg_replace('/^([a-zA-Z]*)/', "['$1']", $field);
     eval("\$profile{$field} = \"".addslashes($row['value'])."\";");
    }
    return $profile;
}

it works! yay!