Converting PHP array to PostgreSQL array and back

Question 1

I have a page where I am creating a PHP array containing the items in a select box and I use json_encode to convert the array into a string that can be inserted into a PostgreSQL database where the field type is TEXT[]. However, when I get the string back and call json_decode on it, I get a blank string.

function encode_array($libraries)
{
 $encoded = json_encode($libraries);
 // Replace the square brackets with curly braces for PostgreSQL TEXT[] field formatting.
 $encoded = str_replace('[', '{', $encoded);
 $encoded = str_replace(']', '}', $encoded);
 return $encoded;
}
function decode_array($libraries)
{
 $decodedStr = str_replace('{', '[', $libraries);
 $decodedStr = str_replace('}', ']', $decodedStr);
 $decoded = json_decode($decodedStr);
 return $decoded;
}

So essentially, I start out with a PHP array, then insert it into the database. So assuming I have an array that looks like:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');

I should end up with a database entry that looks something like:

'{LIBRARY_01, LIBRARY_02, LIBRARY_03}'

And then once decoded, I should once again end up with a PHP array with the same values as before.

The insertion / encoding seems to work, as I can see the array in the database, and I assume it would complain if the encoded string was malformed.

Does anyone know where I'm going wrong here? For reference, I'm using PostgreSQL 9.3 and PHP 5.6 if it matters.

EDIT: In case there was an confusion, the array is just a simple array of strings, there are no special symbols in any of these strings, just alphanumeric characters. The reason I do a catch-all replace of square brackets to curly braces is because the JSON string output by json_encode will be surrounded in square brackets, but the database requires it to be surrounded in curly braces when I pass it into the TEXT[] field.

Question 2

In the second part, your decode_array() function, you're not just changing the curly brackets that you changed the first time. You're changing ALL the curly brackets back, including the ones that were curly brackets initially. So, now you have none.

Question 3

@AdamWinter I don't understand what you mean... there's only a single pair of square brackets enclosing a list of strings. I convert them to curly braces for the database, and when I get it out of the database, I convert the curly braces back to square brackets so it will be in JSON format for the decode function. I'm only using json_encode because it easily converts an array to a comma-separated list enclosed in braces/brackets.

Question 4

I'm saying if "$libraries" already has some curly brackets before you do anything with it, then those will get changed into square brackets in your decode_array() function, which is not what they origially were. I can't see your original string.

Question 5

Alternatively, you might look at "escaping" the square brackets in the string so that you can just insert the string as it is.

Question 6

I just read the additional info you added to the question. I'm providing an answer as an answer in a moment.

Question 7

So, the main issue here is that json format uses Key-Value pairs, and the inferred indexes don't seem to work. Secondly, using json_decoded([string], TRUE) with the TRUE flag, gives you an associative array, instead of an object.

Try running all of this for a good look at what you have:

<?php 
//$libraries = array('0'=>'LIBRARY_01', '1'=>'LIBRARY_02', '2'=>'LIBRARY_03'); //this will not work
$libraries = array('one'=>'LIBRARY_01', 'two'=>'LIBRARY_02', 'three'=>'LIBRARY_03');
var_dump($libraries);
echo '<br>';
$encoded = json_encode($libraries);
echo $encoded;
echo '<br>';
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);
echo 'String: '.$encoded;
echo '<br>';
echo '<br>';
$stdObj = json_decode($encoded);
echo 'var_dump: ';
var_dump($stdObj);
echo '<br>';
echo 'json_encode: '.json_encode($stdObj);
echo '<br>';
foreach($stdObj as $element){
 echo $element;
 echo '<br>';
}
echo '<br>';
$array = json_decode($encoded, TRUE);
echo 'var_dump associative: ';
var_dump($array);
echo '<br>';
echo 'array[one]: '.$array[one];
echo '<br>';
echo '<br>';
echo '<br>';
echo 'json_encode: '.json_encode($array);
echo '<br>';
?>

However, if you're just using json_encode() as a way to turn a one-dimensional array into a simple string list of comma-separated values, then you might use this quick and dirty method:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$encoded = json_encode($libraries);
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);
//in and out of database
$decodedStr = str_replace('{"', '', $encoded);
$decodedStr = str_replace('"}', '', $decodedStr);
$delimiter = '","';
$libraries = explode($delimiter, $decodedStr);
var_dump($libraries);

Point is, json_decode() is not the exact mirror reversal of json_encode().

Using implode() and explode() are the better choice for your situation:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$commaSeparatedString = implode(',', $libraries);
$string = '{'.$commaSeparatedString.'}';
//in and out of database with a comma-separated list in curly brackets
$stringFromDatabase = str_replace('{"', '', $stringFromDatabase);
$stringFromDatabase = str_replace('"}', '', $stringFromDatabase);
$array = explode(',', $stringFromDatabase);

Question 8

The point that I was trying to make in the comments of my post is that I only use json_encode because it's a quick way to convert a PHP array into a string with comma-separated items. I do not need a JSON string. Literally all I need is for an array of strings to be converted into a comma-separated list enclosed in curly braces for the database and a way to convert that back to a PHP array once I retrieve it from the database. I only need the string in this format for the SQL query.

Question 9

Yeah, and (as I stated here in my answer), the problem with using json_decode() is that the string needs to have Key-Value pairs.

Question 10

I've updated the bottom part of my answer here to do exactly what you're looking for.

Question 11

LOL! Yes. Even better. I'm going to change my answer to that.

Question 12

I brushed implode() aside early on because I thought there was something particular about json_encode() that you were trying to do. I forgot to come back to it when you established what you were trying to do with json_encode().

Adam Winter 2,0221 gold badge17 silver badges35 bronze badges · Accepted Answer · 2020-05-18 23:07:58Z

So, the main issue here is that json format uses Key-Value pairs, and the inferred indexes don't seem to work. Secondly, using json_decoded([string], TRUE) with the TRUE flag, gives you an associative array, instead of an object.

Try running all of this for a good look at what you have:

<?php 
//$libraries = array('0'=>'LIBRARY_01', '1'=>'LIBRARY_02', '2'=>'LIBRARY_03'); //this will not work
$libraries = array('one'=>'LIBRARY_01', 'two'=>'LIBRARY_02', 'three'=>'LIBRARY_03');
var_dump($libraries);
echo '<br>';
$encoded = json_encode($libraries);
echo $encoded;
echo '<br>';
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);
echo 'String: '.$encoded;
echo '<br>';
echo '<br>';
$stdObj = json_decode($encoded);
echo 'var_dump: ';
var_dump($stdObj);
echo '<br>';
echo 'json_encode: '.json_encode($stdObj);
echo '<br>';
foreach($stdObj as $element){
 echo $element;
 echo '<br>';
}
echo '<br>';
$array = json_decode($encoded, TRUE);
echo 'var_dump associative: ';
var_dump($array);
echo '<br>';
echo 'array[one]: '.$array[one];
echo '<br>';
echo '<br>';
echo '<br>';
echo 'json_encode: '.json_encode($array);
echo '<br>';
?>

However, if you're just using json_encode() as a way to turn a one-dimensional array into a simple string list of comma-separated values, then you might use this quick and dirty method:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$encoded = json_encode($libraries);
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);
//in and out of database
$decodedStr = str_replace('{"', '', $encoded);
$decodedStr = str_replace('"}', '', $decodedStr);
$delimiter = '","';
$libraries = explode($delimiter, $decodedStr);
var_dump($libraries);

Point is, json_decode() is not the exact mirror reversal of json_encode().

Using implode() and explode() are the better choice for your situation:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$commaSeparatedString = implode(',', $libraries);
$string = '{'.$commaSeparatedString.'}';
//in and out of database with a comma-separated list in curly brackets
$stringFromDatabase = str_replace('{"', '', $stringFromDatabase);
$stringFromDatabase = str_replace('"}', '', $stringFromDatabase);
$array = explode(',', $stringFromDatabase);

The point that I was trying to make in the comments of my post is that I only use json_encode because it's a quick way to convert a PHP array into a string with comma-separated items. I do not need a JSON string. Literally all I need is for an array of strings to be converted into a comma-separated list enclosed in curly braces for the database and a way to convert that back to a PHP array once I retrieve it from the database. I only need the string in this format for the SQL query.
Yeah, and (as I stated here in my answer), the problem with using json_decode() is that the string needs to have Key-Value pairs.
I've updated the bottom part of my answer here to do exactly what you're looking for.
LOL! Yes. Even better. I'm going to change my answer to that.
I brushed implode() aside early on because I thought there was something particular about json_encode() that you were trying to do. I forgot to come back to it when you established what you were trying to do with json_encode().

CollectivesTM on Stack Overflow

Converting PHP array to PostgreSQL array and back

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

CollectivesTM on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related