Suppose you have a serialize string that have various values in different position and you need to search from serialized field. You want particular key’s value using MySQL query. It is very easy with MySQL “%like%” statement but “%like%” fetches more matches which you do not require.
Search from serialized field: Suppose you have a following serialize string in database:
a:9:{s:2:"m1";s:4:"1217";s:2:"m2";s:8:"9986-961";s:2:"m3";s:19:"1988-03-07 00:00:00";s:2:"m4";s:0:"";s:2:"m5";s:0:"";s:2:"m6";s:0:"";s:2:"m7";s:3:"104";s:2:"m8";s:6:"150000";s :2:"m9";s:18:"Ok Then, Yes It Is";}And you need the row in which the m9 value is ‘Yes It Is’.
So the sql will be like:
SELECT * FROM table WHERE field REGEXP '.*"array_key";s:[0-9]+:".array_value.".*'
i.e.
SELECT * FROM table WHERE field REGEXP '.*"m9";s:[0-9]+:".Ok Then, Yes It Is.".*'
Read Also: SQl MATCH AGAINST find results with only more than 3 characters in the result
An author of Namaste UI, published several articles focused on blogging, business, web design & development, e-commerce, finance, health, lifestyle, marketing, social media, SEO, travel.
For any types of queries, contact us on info[at]namasteui.com.
Hi,
We have to use that second query but it doesn’t return any rows. I want to check array key and array value.
My serialize data
a:42:{i:4;s:7:”Samsung”;i:44;s:12:”Blossom Pink”;i:112;s:3:”Bar”;i:74;s:11:”Loudspeaker”;i:37;s:13:”Galaxy Note 4″;i:46;s:3:”Yes”;i:47;s:15:”Single Sim, GSM”;i:72;s:18:”SM-N910GZIEINS/INU”;i:94;s:16:”Yes, 3840 x 2160″;i:456;s:54:”F1.9 Selfie (90 Degree), Wide Selfie Mode (120 Degree)”;i:102;s:11:”HD, Full HD”;i:50;s:10:”Yes, 16 MP”;i:51;s:11:”Yes, 3.7 MP”;i:457;s:96:”Voice Commands (Smile, Cheese, Capture, Shoot), Smart OIS, Fast Auto Focus, Live HDR (Rich Tone)”;i:104;s:27:”Yes, Full HD, 4K – Ultra HD”;i:103;s:96:”Adapt Sound, Sound Alive, Wise Voice 2.0, Extra Volume 2.0, 3 Mics (Directional Voice Recording)”;i:95;s:70:”Yes, Supports WMA, eAAC+, Vorbis, FLAC, AAC, AAC+, AMR-WB, MP3, AMR-NB”;i:53;s:2:”3G”;i:96;s:7:”Android”;i:84;s:9:”Yes, v4.1″;i:105;s:3:”Yes”;i:85;s:5:”Email”;i:55;s:22:”Yes, 802.11 a/b/g/n/ac”;i:86;s:3:”Yes”;i:56;s:3:”Yes”;i:58;s:122:”Accelerometer, Geo-magnetic, Proximity Sensor, Gyroscope, Barometer, Hall Sensor, RGB Ambient Light Sensor, Gesture Sensor”;i:98;s:3:”Yes”;i:88;s:3:”Yes”;i:99;s:3:”Yes”;i:107;s:54:”Samsung WatchON, Advanced S Note Widgets, Smart Select”;i:60;s:27:”Quad HD, 2560 X 1440 Pixels”;i:109;s:23:”Vivid and Clear Display”;i:33;s:8:”3220 mAh”;i:61;s:17:”78.6×153.5×8.5 mm”;i:108;s:5:”176 g”;i:20;s:87:”1 year manufacturer warranty for Phone and 6 months warranty for in the box accessories”;i:65;s:20:”microSD, upto 128 GB”;i:66;s:8:”3 GB RAM”;i:67;s:5:”32 GB”;i:101;s:38:”GSM – 900, 1800; UMTS – 2100; 4G – LTE”;i:69;s:21:”Android v4.4 (KitKat)”;i:70;s:18:”2.7 GHz, Quad Core”;}
replace the ” with .
Thanks a lot! You have saved my time.