Converting a PostgreSQL HSTORE to PHP Array

Question

I'm working with PostgreSQL's HSTORE data type and PHP.

I require the ability to transform PostgreSQL's returned hstore string into a PHP array. I've tried using the fromPg() method defined here as mentioned in [this post][2], however when I save the value and attempt to retrieve it, when I encounter a value similar to the following:

"myKey" => "my\"Value"

... the fromPg() method breaks the value into two array entries, the first having a key of "myKey" and a value of "my\" and the second having a key of "Value\"" and a blank value.

I'm wondering if anyone else has had this problem and if someone would be willing to provide a more functional method of transforming an hstore string into it's equivalent PHP array.

Here is a code sample to replicate the issue I'm having with the existing "solution":

<?php
function fromPg($data) {
    $split = preg_split('/[,\s]*"([^"]+)"[,\s]*|[,=>\s]+/', $data, 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
    $hstore = array();
    for($index = 0; $index < count($split); $index = $index + 2) {
        $hstore[$split[$index]] = $split[$index + 1] != 'NULL' ? $split[$index + 1] : null;
    }
    return $hstore;
}

$hstore_string = '"myKey" => "my\"Value"';
$hstore_array = fromPg($hstore_string);

print '<pre>';
print_r($hstore_array);
print '</pre>';

I've finally broken down and created a PHP library of my own specifically designed to tackle this problem, as well as many other shortcomings of PHP's PostgreSQL driver such as automatic detection and transformation of PostgreSQL Hstores, Arrays, Dates, Geometric data-types, etc. It's available on GitHub at: https://github.com/JDBurnZ/PHPG

possible duplicate of Convert postgresql hstore to php array — plaes
– plaes, Commented Mar 4, 2012 at 19:04

Community · Accepted Answer · 2017-05-23 12:12:58Z

If you're interpreting an hstore string that comes (directly or not) from user input, Piotr's solution is potentially dangerous (as all user-input evals). I cannot come up with a way to abuse it, but still I'd rather avoid eval'ing an unknown string.

Usage:

hstore(<some_array_or_object>, false) converts the input to a valid hStore string literal and returns it:

hstore(array('k1' => 'v1', 'k2' => 'v2')) => "k1"=>"v1","k2"=>"v2"

hstore(<some_array_or_object>) converts the input to a valid hStore, single-quoted, followed by ::hstore

hstore(array('k1' => 'v1', 'k2' => 'v2')) => '"k1"=>"v1","k2"=>"v2"'::hstore

hstore(<some_string>) converts from an hstore string (as it comes from a query) to array

hstore('"k1"=>"v1","k2"=>"v2"') => array('k1' => 'v1', 'k2' => 'v2')

It handles NULLs (both ways), and properly escapes/unescapes keys and values.

<?php

/**
 * mixed hstore(mixed $input[, bool $prepared = false])
 *      Convert from hstore string to array, or from array/object to hstore.
 * Inner arrays/objects are serialized but deserialization is up to you; you
 * are expected to keep track of which fields you sent as non-scalars.
 *
 * @param mixed $input          A string (from hstore) or an array/object
 * @param type $prepared        Array or object to convert to hstore string
 * @return mixed                Depends on the input
 */
function hStore($input, $prepared=true)
{
    if (is_string($input))
    {
        if ($input === 'NULL')
        {
            $output = NULL;
        }
        else
        {
            $re = '_("|^)(.*?[^\\\\"])"=>"(.*?[^\\\\"])("|$)_s';
            preg_match_all($re, $input, $pairs);
            $mid = $pairs ? array_combine($pairs[2], $pairs[3]) : array();

            foreach ($mid as $k => $v)
            {
                $output[trim($k, '"')] = stripslashes($v);
            }
        }
    }
    elseif (is_null($input))
    {
        $output = $prepared  ? 'NULL::hstore' : 'NULL';
    }
    elseif (!is_scalar($input))
    {
        foreach ((array)$input as $k => $v)
        {
            !is_scalar($v) && ($v = serialize($v));
            $entries[] = '"' . addslashes($k) . '"=>' .
                         '"' . addslashes($v) . '"';
        }

        $mid = empty($entries) ? '' : join(', ', $entries);

        $output = $prepared ? "'{$mid}'::hstore" : $mid;
    }

    return $output;
}

?>

This question is indeed a duplicate of Convert postgresql hstore to php array.

I'm just re-posting here my answer to that post.

quaker · Accepted Answer · 2012-06-25 08:01:21Z

1

Try this:

function unescape_hstore($hstore)
{        
  $hstore = preg_replace('/([$])/', "\\\\$1", $hstore);
  $unescapedHStore = array();
  eval('$unescapedHStore = array(' . $hstore . ');');
  return $unescapedHStore;
}

And feed this function with output from postgresql.

answered Jun 25, 2012 at 8:01

quaker

112 bronze badges

Comments

greg · Accepted Answer · 2012-08-22 12:14:42Z

0

You can use Pomm's converter system. It comes with a HSTore converter from/to PHP - Postgresql.

Hope it helps…

answered Aug 22, 2012 at 12:14

greg

3,5351 gold badge27 silver badges35 bronze badges

Collectives™ on Stack Overflow

Converting a PostgreSQL HSTORE to PHP Array

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related