CODEWITHSUNDEEP
mysql
ru3sch
ru3sch

Reputation: 796

Retrieve a portion of data from MySQL

I have the following data in a column in my database, and I have literally no idea what to do to retrieve a piece of the data that I want:

a:2:{i:1;a:3:{s:8:"property";s:10:"Promo code";s:5:"logic";s:11:"Is equal to";s:5:"value";s:12:"DISCOUNTTIME";}i:2;a:3:{s:8:"property";s:15:"Promo use count";s:5:"logic";s:11:"Is equal to";s:5:"value";s:1:"0";}}

I'm trying to retrieve only DISCOUNTTIME. I can handle the basics like

"SELECT x FROM y WHERE id = z"

but just have no idea how to deal with the way this data is formatted.

Any help?

Upvotes: 1

Views: 135

Answers (2)

ratsbane
ratsbane

Reputation: 890

Which part of the expression are you trying to retrieve? Perhaps the "s:12" part before DISCOUNTTIME? If you're just trying to find the rows which contain DISCOUNTTIME then you can use the MySQL INSTR operator: SELECT * FROM y WHERE INSTR(columnname, 'DISCOUNTTIME')>0

If you're trying to find some piece of text related to DISCOUNTTIME, e.g. "s:12:" then you can do it by combining INSTR with other expressions. If you know the preceding text will always have five characters like "s:12:" then you can SELECT SUBSTR(columnname, INSTR-5, 5)

If the string structure is very complex you might be better off writing a program to query the database and then use regexp or other techniques to extract the string.

See MySQL String Functions here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr

Upvotes: 0

Tudor Constantin
Tudor Constantin

Reputation: 26861

take a look at php unserialize() - it looks like your string is in that format

Upvotes: 2

Related Questions