Retrieving a Specific Value From A Serialized Array MySQL

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 wh开发者_C百科at is the best way to retrieve a specific value from a serialized array stored in mysql?

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.

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) ""
}