Demon’s Code

Archive for June, 2010

Mysql & PHP unserialize

by Nox on Jun.01, 2010, under PHP

Yes, I was a bad boy. I did wrong in creating a simple little table that was meant to be an extension for another Table.

Suppose you have an user-table in your database and want to extend it with various data you dont know yet, just as I did some time ago...

You have your table with an id primary key, and some other fields you know, like name, and password. Now you create another table (user_data) to store any, yes ANY, data. This table has an foreign primary key id, that is of course linked to the user.id field. And now, you get 2 more fields user_data.key and user_data.value. Now your table is like an associative array in php, linked only through your id-field to the user table.

Sounds fun, doesnt it?

$user = SELECT * FROM `user` WHERE `id`=X
$user_data = SELECT * FROM `user_data` WHERE `id`=X
$user_data = array_map(convert_with_unserialize, $user_data);

And now you got all the data you want back in PHP. To insert you will have to serialize the data, and to read it, you will have to unserialize it. It's not hard. Actually it's pretty useful, as long as you don't know your data.

But it is hard as hell to run a specific SELECT on user to select everyone that has a white rabbit shoulder tatoo. This data is of course saved in the user_data table as not everyone needs to have a tatoo field.

SELECT `id` FROM `user_data` WHERE `key`='tatoo' AND `value`='s:12:white rabbit';

As you see, you will have to serialize your "question" in some way too. It would be better to write something like this.

SELECT `id` FROM `user_data` WHERE `key`='tatoo' AND UNSERIALIZE(`value`)='white rabbit';

This has some other uses, and actually I used it to correct my mistake in creating such a table in the first place. But for any of you, using some kind of serialized data, this function might help.

BEGIN
	DECLARE len INT DEFAULT LENGTH(str);
	DECLARE pos INT DEFAULT 0;

	IF ISNULL(str) THEN
		RETURN NULL;
	END IF;

	IF LEFT(str, 1) = 's' THEN
		SET pos = LOCATE(':', str, 3) + 2;
		RETURN MID(str, pos, len - pos - 1);
	END IF;

	IF LEFT(str, 1) = 'a' THEN
		RETURN str;
	END IF;

	RETURN MID(str, 3, len - 3);
END

It checks for null first, then unserializes strings, then returns arrays as themselfs and then guesses that there are only numbers left. Of course everything is still returned as string, but PHP should do the rest of the dirty work.

Have fun

Leave a Comment :, more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...

Archives

All entries, chronologically...