Storing a multidimensional array in a DB 1

[Sleidia]

I read somewhere that you can store a multidimensional array with the serialize() function.

The problem is that, once serialized, the array isn't readable/understandable anymore from within a MySQL data editor.

So, is there a way to store a multidimensional array in such a way that it's still readable? Note that the method must also allow the retrieval of the array so that it can be modified and stored again.

In my case, since I only need to store row indexes (IDs) inside the array, I came up with this idea : store the array as a string with the following format :
(1, 2(33, 44, 55), 23, 34(1, 2(4, 7, 9), 3), 12)
What do you think?


Thanks

 

[jpadie]

if it must be editable from within a mysql data editor then why not store it as an evaluatable expression?

array("somekey"=>array("somekey2"=>"somevalue"),
        "somekey3"=>"somevalue2");
//etc

you can get pretty close to this format with print_r($array,true)

 

[sleipnir214]

I have found the output of serialize() to be quite readable.

But I suppose the real question is, "If you store the array that way, how are you going to reconstruct the array?"


Want the best answers? Ask the best questions! TANSTAAFL!

 

[Sleidia]

Hi Sleipnir214,

You call this readable? :
a:3:{i:0;s:8:"red";i:1;s:9:"blue";i:2;s:6:"green";i:3;s:4:"yellow";}
( I've found it here :

http://evolt.org/node/60222

)

But I suppose the real question is, "If you store the array that way, how are you going to reconstruct the array?"

You're talking about the following format? :
(1, 2(33, 44, 55), 23, 34(1, 2(4, 7, 9), 3), 12)

 

[Sleidia]

Thanks Jpadie

But with your solution, how do I reconstruct the array?

 

[sleipnir214]

Yes. I have no problem following that serialized string.

Is it as easy to read as the format you propose? No. But then, serialize() is a general-purpose function where yours is special-purpose.

Also, the string presented by that website is not the string serialize() would produce. If you fix the errors in the html code in the page you linked and run the code, the string output is:

[tt]a:4:{i:0;s:3:"red";i:1;s:4:"blue";i:2;s:5:"green";i:3;s:6:"yellow";}[/tt]

Which I think you will find eminently more readable than the string posted on that site. The string says: the value is an array four elements: the first element has an integer index of 0 and a value that is a 3-character string "red", the second element has an index of 1 and a value of a 4-character sting "blue", etc.

Even if you correctly run the serialized string through mysql_escape_string() (which the page does not do), then you get:

[tt]a:4:{i:0;s:3:"red";i:1;s:4:"blue";i:2;s:5:"green";i:3;s:6:"yellow";}[/tt]

Which to my eye is still pretty readable.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[Sleidia]

a:4:{i:0;s:3:"red";i:1;s:4:"blue";i:2;s:5:"green";i:3;s:6:"yellow";}

Which to my eye is still pretty readable.

I knew you're not human! ahaha

But then, serialize() is a general-purpose function where yours is special-purpose.

Yeah, that was one of my main concerns, I reckon.
But still, I'm not happy with serialize().

 

[jpadie]

sorry for the delay in posting back, sleidia. would something like this be what you are looking for:

<?php
$array = array("apples", "oranges", array("daffodil", "tulip"));
$stringforstoringindb = prepForDB($array);
echo "array current looks like this:<br/><pre>";
print_r($array);
echo "<hr/><br/>and the prep'd array looks like this (notice the quotes)<br/>";
echo $stringforstoringindb;
echo "<br/>";

//sometime later

$newarray = reAssembleDBString($stringforstoringindb);
echo "<hr/>re-assembled array <br/>";
print_r($newarray);

function prepForDB($array){
	if (is_array($array)){
		return var_export($array, true);
	} else {
		return $array;
	}
}
function reAssembleDBString($string){
	eval ('$newarray = '.$string .';');
	return $newarray;
}
?>

 

[Sleidia]

Hello Jpadie

Thanks for telling me about var_export which I didn't know at all! The same goes for the way to use eval.