Storing/retrieing a Associative array in MySQL

[williamZanelli]

Hi guys

 

I want to store an associative array [eg.]

 

$prices = array( "Tires"=>100, "Oil"=>10, "Spark Plugs"=>4 );

 

In MySQL and then be able to retrieve it as an array and use it..

 

Any ideas on how I could do this.. if someone could post sample code that would be fab.

 

Thanks in adavance

 

Will

[trq]

Depending on wether or not you wish to be able to search on the keys / values within this array it might be easiest to simply use serialize() / unserialize().

[williamZanelli]

Hi thorpe

 

Thanks for the reply.

 

I do actually need to search on the keys/values - I currently use in_array() fucntion to search

 

I also need to, at some stage loop through the array using foreach.. or maybe the while loop.

 

What would be the best way of coding this?

 

Thanks

 

Will

[trq]

In that case, the best method IMO would be to store each key / value in its own row. You will need to maintain your own id's so you can maintain each array relationship.

 

CREATE TABLE arrays (
  id INT AUTO INCREMENT PRIMARY KEY,
  array_id INT,
  array_key TEXT,
  array_value TEXT
);

 

Once your table is create, its simple to add arrays to the table.

 

<?php

  // connect to db.

  // get a new array_id
  function getarrayid() {
    if ($result = mysql_query("SELECT id FROM arrays ORDER BY id ASC LIMIT 1;")) {
      if (mysql_num_rows($result)) {
        $row = mysql_fetch_assoc($result);
        return $row['id']++;
      } else {
        return 1;
      }
    }
  }

  $array_id = getarrayid();
  $prices = array( "Tires" => 100, "Oil" => 10, "Spark Plugs" => 4 );
  foreach ($prices as $k => $v) {
    mysql_query("INSERT INTO arrays (array_id,array_key,array_value) VALUES ($array_id,'$k','$v');");
  }

?>

 

Now to retrieve the array with an id of 3.

 

<?php

  $prices = array();
  if ($result = mysql_query("SELECT array_key,array_value FROM arrays WHERE array_id = 3;")) {
    if (mysql_num_rows($result)) {
      while (mysql_fetch_assoc($result)) {
        $prices[$row['array_key']] = $row['array_value'];
      }
    }
  }

?>