Retrieving a Specific Value From A Serialized Array MySQL

Question

Database structure is as follows:

id|metaKey|metaValue
--+-------+-----------------------------------
55|product|a:8:{s:3:"sku";s:0:"";s:8:"products";a:3:{i:1;a:3:{s:6:"option";s:1:"1";s:5:"price";s:5:"14.95";s:9:"saleprice";s:0:"";}i:2;a:3:{s:6:"option";s:0:"";s:5:"price";s:0:"";s:9:"saleprice";s:0:"";}i:3;a:3:{s:6:"option";s:0:"";s:5:"price";s:0:"";s:9:"saleprice";s:0:"";}}s:11:"description";s:124:"Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.";s:8:"shiprate";s:1:"A";s:8:"featured";s:2:"no";s:4:"sale";s:3:"yes";s:10:"cart_radio";s:1:"0";s:6:"optset";s:0:"";}

This is from a wordpress site, I need to retrieve the first "price" value which is 14.95 what is the best way to retrieve a specific value from a serialized array stored in mysql?

Please format your question correctly so that we can all read it. — Mark Byers, Sep 28 '11 at 23:15
Inserting serialized values in a database is a [terrible design practice](http://stackoverflow.com/questions/7364803/storing-arrays-in-the-database/7364834#7364834). If you have any control over it, do it the right way. — NullUserException, Sep 28 '11 at 23:21
Yeah I wouldn't usually do it, it was a plugin that created it. — jamckp, Sep 28 '11 at 23:37

score 7 · Accepted Answer · answered Sep 28 '11 at 23:19

7

Create query, read data from SQL, unserialize() metaValue and access it like an array. You can also try to use regular expressions if you need to extract it inside mysql, but that's a not very good approach.

answered Sep 28 '11 at 23:19

romaninsh

10,606
4
50
70

score 2 · Answer 2 · answered Sep 28 '11 at 23:21

Use unserialize() to retrieve the metaValue data.

The following code can retrieve the first price value:

$result = mysql_query("SELECT * FROM tbl WHERE id=55");
$row = mysqLfetch_assoc($result);

$serial = $row['metaValue'];
$data = unserialize($serial);
echo $data['products'][1]['price'];

The data in this row is structured as:

array(8) {
  ["sku"]=>
  string(0) ""
  ["products"]=>
  array(3) {
    [1]=>
    array(3) {
      ["option"]=>
      string(1) "1"
      ["price"]=>
      string(5) "14.95"
      ["saleprice"]=>
      string(0) ""
    }
    [2]=>
    array(3) {
      ["option"]=>
      string(0) ""
      ["price"]=>
      string(0) ""
      ["saleprice"]=>
      string(0) ""
    }
    [3]=>
    array(3) {
      ["option"]=>
      string(0) ""
      ["price"]=>
      string(0) ""
      ["saleprice"]=>
      string(0) ""
    }
  }
  ["description"]=>
  string(124) "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua."
  ["shiprate"]=>
  string(1) "A"
  ["featured"]=>
  string(2) "no"
  ["sale"]=>
  string(3) "yes"
  ["cart_radio"]=>
  string(1) "0"
  ["optset"]=>
  string(0) ""
}

Retrieving a Specific Value From A Serialized Array MySQL

2 Answers2