[RESOLVED] Arrays stored in Mysql

Staggy11

How would you go about storing an array in a MySQL database?

I'm aiming to store a two-dimensional array. One sub array would have strings of text in it, and the other would have timestamps corresponding to when the strings of text were posted.

I would eventually add deleting of old strings, and adding of new ones, but right now I'm wondering how on earth you storey arrays in MySQL.

I tried storing it as a VARCHAR slot, but soon realised that this didn't work. Does anyone have any soloutions/tips?

bradgrafelman

I would say [man]serialize/man the array and store the string. Since VARCHAR is limited to 255/256 (can't remember which), you might need to use a TEXT column to store the outputted string.

Obviously, you would then [man]unserialize/man the string when you later pull it from the DB and wish to re-create the array.

Piranha

I think I would do something like this:

CREATE DATABASE arrays(
id int auto_increment,
arrayno int,
text varchar(50),
date timestamp
)

When a new array should get stored it gets a new arrayno, then each row in the array are stored with that arrayno and text and date seperatley. When retrieving it you simply call for all rows with arrayno = $desired.

Staggy11

I'm a little unsure as to the syntax of serialize() and unserialize(). Would a valid code be:

$info=serialize($array1);
$update=mysql_query("UPDATE `map` SET `speech`= '$info' WHERE blah='blah' ") or die(mysql_error());

bradgrafelman

Well, I would use [man]addslashes/man or - better yet - [man]mysql_real_escape_string/man on the $info variable, otherwise your query might get destroyed with quotes and whatnot.

Other than that, yes, that seems fine to me.

Staggy11

Thanks for the tip about mysql_real_escape_string(). My final piece of code will be

$info=mysql_real_escape_string(serialize($array1));
$update=mysql_query("UPDATE `map` SET `speech`= '$info' WHERE blah='blah' ") or die(mysql_error());

I'll then use stripslashes() for getting the info out of the database.

Anyways, thanks for the help.

bradgrafelman

Staggy11 wrote:
I'll then use stripslashes() for getting the info out of the database.

Actually, I believe that is unnecessary and might cause errors.