Unserializing Issue in MySQL

[atheistrical]

I don't know whether to post this under MySQL or PHP section. The issue, however, focuses on MySQL more than PHP.

I have a column named "Benefit" in a table named "SRHC". This column holds a serialized array, an instance of which is as below:

Say $b is the array returned after unserializing data from the "Benefit"column. $b now contains:

$b[0][0]=1;
$b[0][1]=2;
$b[0][2]=3;
$b[0][3]=4;
$b[1][0]=5;
$b[1][1]=6;
$b[1][2]=7;
$b[1][3]=8;

Now the real issue:

I would like to construct a query like:

SELECT Benefit FROM SRHC WHERE $b[0][1]=5 AND $b[1][1]=6

Note that the "Benefit" Column contains serialized value of the array $b.

[Old Pedant]

Well, the problem is that you are trying to fit a square peg (and array, whether serialized or not) into a round hole (a properly normalized database).

Clearly, good DB design would say that storing ANY kind of array or list in a single DB field is a mistake. Instead, you should put such data into a separate many-to-one table that uses a PK/FK relationship to the main table.

I assume you are stuck with the DB design and have no authority to change it. In which case you either have to figure out how to tell MySQL how to look inside the serialized form or you have to do the testing you show in the PHP code.

You don't say *how* that data is serialized--that is, what algorithm was used--so I can't begin to guess how practical it would be to write a custom function or stored procedure to do the deserialization (or equivalent) in MySQL code.

[atheistrical]

Thank you for the response.

You are right, I don't have authority to change the database design.

I have used the PHP's inbuilt function serialize(mixed $array).

Example:

$b[0][0]=1;
$b[0][1]=2;
$b[0][2]=3;
$b[0][3]=4;
$b[1][0]=5;
$b[1][1]=6;
$b[1][2]=7;
$b[1][3]=8;

$serialized = serialize($b);
echo $serialized;

The Output is as follows:

a:2:{i:0;a:4:{i:0;i:1;i:1;i:2;i:2;i:3;i:3;i:4;}i:1;a:4:{i:0;i:5;i:1;i:6;i:2;i:7;i:3;i:8;}}

Even the slightest help will be greatly appreciated!

[Old Pedant]

I dunno...this would probably be a tough nut to crack.

How much do the various serializations differ??? This one is of a 2x4 array. Are they *all* 2x4 arrays? Or do you have pretty much any possible set of dimensions??

I *think* that if you wanted to do some preparation work in the PHP code ahead of time, you could do it via regular expressions.

For example, to match against $b[0][1]==5, you might do:

/^a\:2\:\{i\:0\;a\:4\:\{i\:0\;i\:\d+\;i\:1\;i\:5\;/

You see that? essentially, we did $b[0][0]==any digits followed by $b[0][1]==5

For $b[1][1]==6 you would probably need to do:

/^a\:2\:\{i\:0\;a\:4\:\{i\:0\;i\:\d+\;i\:1\;i:\d+\;i\:2\;i\:\d+\;i\:3\;i\:\d+\;\}i\:1\;a\:4\:\{i\:0\; i\:\d+\;i\:1\;i\:6\;/

Probably could simplify the pattern somewhat:

a\:2\:\{i\:0\;a\:4\:\{(i\:\d+\;{8,8}\}i\:1\;a\:4\:\{i\:0\;i:\d\;i\:1\;i\:6\;/

Not sure you need the backslashes in front of all the colons and semicolons. Depends on the "dialect" of regexp that MySQL uses. Clearly need them in front of the { and } of course.