0

So the previous coder of my project decided to put PHP-seralized info in the MySQL database like such:

a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}

Or

a:1:{s:4:"Size";s:1:"S";}

And I need to extract using MySQL directly what's between the quotes, to display this:

Format: NTSC, License: Home

Or

Size: S

So I need to basically keep what's between the quotes and concatenate it. I could use something like Looking to extract data between parentheses in a string via MYSQL or MySQL: Use REGEX to extract string (select REGEX) But I can't because the number of attributes is arbitrary (0,1,2,3,...) instead of always 2 for example.

Any ways to do this?

Community
  • 1
  • 1
NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58
  • The easiest (a 20 min workout) option would be just export the data into a a csv file, parse it with perl, create the data that you want into a new column and get it back into the table (with a new column). Does this option work for you (It wouldn't work if the table is live and still getting constantly written in the PHP format even today). Is doing it in the clientn (PHP) feasible? Also, here is code to do regex with UDFs in MySql if you have time to build/install and if you can sacrifice some performance in database. – blackpen Oct 04 '16 at 08:48
  • Yeah, I gave up and I'm doing something like this now. Unless somebody has a way of doing it in MySQL, I'm looping every row in PHP to execute a custom regex. This is done only occasionally when a client exports his list of orders, so it's ugly and I hate looking at what I just wrote, but it's not executed often and the client needs it quick :( – NaturalBornCamper Oct 04 '16 at 09:14
  • I would assume that when your client asks to you get things that "look like regex", you do into MySql run a query with approximate regex as "look for rlike regex", bring the rows to PHP, run extraction and show it to the clients. I think that wouldn't be such a bad idea, as long as the client asks for things that can be managed with RLIKE. – blackpen Oct 04 '16 at 11:07
  • I'm not sure I get exactly what you mean. Can you write this as an answer? It looks like this might be the best option – NaturalBornCamper Oct 05 '16 at 01:54

2 Answers2

1

Write a PHP script to unserialize it and print it. It will be easier than using a regexp.

Rick James
  • 135,179
  • 13
  • 127
  • 222
1

Unserialize in PHP: Best option is just to unserialize it, concatenate it and hand it to your client (For performance you would narrow down the rows before bringing them to PHP).

<?php
$data='a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}';

/* To unserialize and print */
$arr=unserialize($data); $out="";
foreach ($arr as $k => $val) {
    $out.=$k.":".$val." ";
}
print "$out<br>";
?>

Pattern Matching in PHP: Next option is to regex match it in PHP (There isn't much benefit of doing so though. The unserialize is better). I will leave this example here, just in case if you want to do something with regular expression matches.

<?php

$data='a:2:{s:6:"Format";s:4:"NTSC";s:7:"License";s:4:"Home";}';
preg_match_all('/"(.*?)"/',$data, $result);

$out="";
for ($i = 0; $i < count($result[0]); $i+=2) {
    print $result[1][$i].":".$result[1][$i+1]." ";
}
print "$out<br>";
?>

Export,Process,Import: If the table column is no more being written in the serialized format, and if it is only being read (to get back the legacy data), you could export it as csv file, process the data in perl, add a new column to contain the "Tag=Value" format separated by blanks, and imported the file back into database with a new column in which the new data is inserted. If you need it, I could supply you with a perl script to parse/convert the serialized data.

Pattern Matching in MySql using UDFs: If, on other hand, if you just want to use regular expressions in MySql query itself (to extract the tag/values), you can do so by using this extension. It needs to be build from sources, installed.

In response to question about RLIKE: If you know all possible formats of your serialized data (if it looks simple enough for you), you could run regular expression matches before bringing the rows into PHP (if performance matters).

create table tt (data varchar(100));
insert into tt values ("a:2:{s:6:\"Format\";s:4:\"NTSC\";s:7:\"License\";s:4:\"Home\";}");

select data from tt where data rlike '"Format";s:[0-9]+:"NTSC"';
blackpen
  • 2,339
  • 13
  • 15