If you've been involved in a moderately custom e-commerce store based on the WP-Ecommerce WordPress plugin, you've probably come across the need to display product meta data (dimensions, weight, links, etc.).
You may have already decided that the easiest way is to draw this information direct from the database. After some digging, you discover that this information is located in the wp_postmeta table. Okay, makes sense. The products, after all, are a WordPress content type.
After digging a little deeper, you discover that all product metadata is "merged" not only into one table record, but one field! You're wondering how this semi-colon and color delimitted list somehow translates to metadata. If you have never serialized an Array in PHP, this would naturally puzzle you.
This value is actually a serialized Array of product data:
a:19:{s:25:"wpec_taxes_taxable_amount";s:0:"";s:13:"external_link";s:0:"";s:18:"external_link_text";s:0:"";s:20:"external_link_target";s:0:"";s:6:"weight";s:1:"1";s:11:"weight_unit";s:5:"pound";s:10:"dimensions";a:6:{s:6:"height";s:1:"8";s:11:"height_unit";s:2:"in";s:5:"width";s:2:"4 ";s:10:"width_unit";s:2:"in";s:6:"length";s:1:"3";s:11:"length_unit";s:2:"in";}s:8:"shipping";a:2:{s:5:"local";s:1:"0";s:13:"international";s:1:"0";}s:14:"merchant_notes";s:0:"";s:8:"engraved";s:1:"0";s:23:"can_have_uploaded_image";s:1:"0";s:15:"enable_comments";s:0:"";s:24:"unpublish_when_none_left";s:1:"0";s:11:"no_shipping";s:1:"0";s:16:"quantity_limited";s:1:"0";s:7:"special";s:1:"0";s:17:"display_weight_as";s:5:"pound";s:16:"table_rate_price";a:2:{s:8:"quantity";a:0:{}s:11:"table_price";a:0:{}}s:17:"google_prohibited";s:1:"0";}
By using the unserialize() function, we are able to convert this value back into an Array, making it much easier to grab individual pieces of metadata.
The following query might fill in a few gaps as well:
$cartsql = $wpdb->prepare( "SELECT cart.*, meta.meta_value
FROM " . WPSC_TABLE_CART_CONTENTS . " cart
INNER JOIN wp_postmeta meta
ON meta.post_id = cart.prodid
WHERE meta_key = '_wpsc_product_metadata'");