#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    80
    Rep Power
    5

    Picking up product id from a string


    I am trying to pick up a product ID from a string.
    For some reason, the developer of a plugin decided to store it all as one jumbled text string in the db.
    So, for an order I get:
    Code:
    <metavalue>a:1:{i:0;a:10:{s:2:"id";s:3:"377";s:12:"variation_id";s:0:"";s:4:"name";s:10:"VPN 7 days";s:3:"qty";i:1;s:9:"item_meta";a:0:{}s:13:"line_subtotal";s:4:"1.99";s:17:"line_subtotal_tax";s:1:"0";s:10:"line_total";s:4:"1.99";s:8:"line_tax";s:1:"0";s:9:"tax_class";s:0:"";}}</metavalue>
    My current SQL is:
    SELECT SUBSTRING( meta.meta_value, LOCATE('\"id\";i:', meta.meta_value )+7, 3)AS prodID

    This picks up the start of :"id";s:3:"377"
    However, to make things confusing, sometimes s:3 appears after, sometimes not. So, how can I pick out the 377 part of the code. The only consistent thing is that id is enclosed by "" and so is the product ID "377"
    How do I pick the 3rd occurrence of "
  2. #2
  3. Old Fart
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Chicago
    Posts
    106
    Rep Power
    4
    At first I thought that this could be a JSON string, but if it is then it is not a well formed one. It does look as if it should follow some kind of encoding standard. I suggest that you try to find out how the string is parsed by the plugin instead of trying to reverse engineer it yourself.

    Is the plugin open source? If so look at its source code.

    Is there documentation of the plugin's internals? If so look for a reference to the encoding standard (if any) that it uses.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    80
    Rep Power
    5
    Originally Posted by richpri
    At first I thought that this could be a JSON string, but if it is then it is not a well formed one. It does look as if it should follow some kind of encoding standard. I suggest that you try to find out how the string is parsed by the plugin instead of trying to reverse engineer it yourself.

    Is the plugin open source? If so look at its source code.

    Is there documentation of the plugin's internals? If so look for a reference to the encoding standard (if any) that it uses.
    Woocommerce, let's just say these plugin developers don't document stuff well
    This is all I have to go on in the data unfortunately.
    I know for sure it starts with "id" and then I have to pick up the next bit of string enclosed in "" that is the product ID but it could be 7 chars or 10 or more.
  6. #4
  7. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,958
    Rep Power
    9397
    That is a serialized string of an array.

    Is there any way you can narrow down the search and check the product ID in PHP? Or perhaps rework the poorly-designed code so that the ID at the very least is stored in a proper column?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    80
    Rep Power
    5
    Originally Posted by requinix
    That is a serialized string of an array.

    Is there any way you can narrow down the search and check the product ID in PHP? Or perhaps rework the poorly-designed code so that the ID at the very least is stored in a proper column?
    I don;t think I should rework the code as although it's opensource, it's a plugin so any updates and I'd have to redo it.
    I could take that string and put it into an array perhaps only taking anything enclosed in ""
    Then I would get
    "id"
    "377"
    "variation_id"
    ""
    "name"
    "VPN 7 days"
    "qty"
    "item_meta"
    "line_subtotal"
    "1.99"
    "line_subtotal_tax"
    "0"
    "line_total"
    "1.99"
    "line_tax"
    "0"
    "tax_class"
    ""

    Not sure how to parse the string into an array like that though..unserialize?
    Last edited by qwertyjjj; February 5th, 2013 at 10:13 AM.
  10. #6
  11. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Code:
    php > print_r(unserialize('a:1:{i:0;a:10:{s:2:"id";s:3:"377";s:12:"variation_id";s:0:"";s:4:"name";s:10:"VPN 7 days";s:3:"qty";i:1;s:9:"item_meta";a:0:{}s:13:"line_subtotal";s:4:"1.99";s:17:"line_subtotal_tax";s:1:"0";s:10:"line_total";s:4:"1.99";s:8:"line_tax";s:1:"0";s:9:"tax_class";s:0:"";}}'));
    Array
    (
        [0] => Array
            (
                [id] => 377
                [variation_id] => 
                [name] => VPN 7 days
                [qty] => 1
                [item_meta] => Array
                    (
                    )
    
                [line_subtotal] => 1.99
                [line_subtotal_tax] => 0
                [line_total] => 1.99
                [line_tax] => 0
                [tax_class] => 
            )
    
    )
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

IMN logo majestic logo threadwatch logo seochat tools logo