Search code examples
phpmysqlarraysmagentodatafield

How to retrieve a MySQL Database field with data between brackets {} as an array

I need to retrieve data from this table in magento sales_flat_quote_payment. There is a field in the table called additional_data.

In the additional_data field is data within brackets { }

additional data

Here is how the data looks formatted

a:6:{
    s:14:"component_mode";
    s:4:"ebay";
    s:14:"payment_method";
    s:6:"PayPal";
    s:16:"channel_order_id";
    s:9:"123654789";
    s:17:"channel_final_fee";
    d:5.3600000000000003;
    s:12:"transactions";
    a:1:{
        i:0;
        a:4:{
            s:14:"transaction_id";
            s:15:"987456321456987";
            s:3:"sum";
            d:66.989999999999995;
            s:3:"fee";
            d:1.5700000000000001;
            s:16:"transaction_date";
            s:19:"2016-03-31 02:54:18";
            }
        }
    s:6:"tax_id";
    s:0:"";
}

How could I access this data like an array?

I specifically need to extract from this data the s:3:"fee"; value of d:1.5700000000000001;.

I tried $additional_data->transactions->fee without success.

Here is my code I use.

$con = mysql_connect("localhost","user","password");

if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("database", $con);

$po = $_GET['po'];


if( ! is_numeric($po) )
  die('invalid PO#');

$query1 = "SELECT * FROM `sales_flat_quote` WHERE `reserved_order_id` LIKE '" . $po . "' LIMIT 0 , 30";

$get_entity_id = mysql_query($query1);

while($row = mysql_fetch_array($get_entity_id, MYSQL_ASSOC))
{
  $entity_id = $row['entity_id'];

  echo "  <div style='margin:30px 0px;'>
      entity_id: $entity_id<br />
    </div>
  ";
}

$query2 = "SELECT * FROM `sales_flat_quote_payment` WHERE `quote_id` = " . $entity_id . " LIMIT 0 , 30";

$get_additional_data = mysql_query($query2);

while($row = mysql_fetch_array($get_additional_data, MYSQL_ASSOC))
{
  $additional_data = $row['additional_data'];

  echo "  <div style='margin:30px 0px;'>
      additional_data: $additional_data<br />
    </div>
  ";
}

  echo "  <div style='margin:30px 0px;'>
      fee: $additional_data->transactions->fee<br />
    </div>
  ";

mysql_close($con);

UPdate: I unserialized the array. Now it looks like this.

array(6) {

     ["component_mode"]=> string(4) "ebay"
     ["payment_method"]=> string(6) "PayPal"
     ["channel_order_id"]=> string(9) "123654789"
     ["channel_final_fee"]=> float(3.6)
     ["transactions"]=> array(1) {
         [0]=> array(4) {
         ["transaction_id"]=> string(15) "987456321456987"
         ["sum"]=> float(45)
         ["fee"]=> float(1.16)
         ["transaction_date"]=> string(19) "2016-03-31 05:43:43"
         }
    }
    ["tax_id"]=> string(0) ""
}

I'm still having trouble retrieving the data I want from this array.

I want to retrieve $additional_data->transactions->fee.

How can I do this?

Solution

  • It looks like a serialized array. See unserialize() php doc

    The fee can be retrieved with : $additional_data['transactions'][0]["fee"]