get 3rd occurrence of "

[jackvull]

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:

<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 "


I think this must be a serialised string of an array?

[lucy24]

If this were a Regular Expressions question I could answer it standing on my head ;)

"id";(?:s:3:)?"(\d+)"

Oh, wait. It is a RegEx question. Everything is, in the end.


"Disable graphic smile faces" is your friend.

[astupidname]

Just to note, the string you are pulling via sql looks to me as though it has been serialized, see serialize [php.net]
as well as:
unserialize [php.net]

although when I try to unserialize the string you show within <metavalue> it does not work for me, comes back false, most likely because the serialized string is incomplete because of your query breaking it

which kinda makes me think that if you pulled all of meta or meta.meta_value and get the entire serialized string from sql and then try to unserialize it it should work if I am correct, which I can almost guarantee by looking at the similarity of that string and a serialized object string.
So if that were working you would do something like:
// $s is the entire string from sql, not the chopped one from your query as shown
$obj = unserialize($s);
//this may need refinement if 'id' is not root level within the object, but probably is
echo $obj->id;

Just for your entertainment and knowledge, here is a demonstration of serialize() & unserialize() :

<?php
 
$obj = (object)array('a' => 193, 'b' => "hello world", 'id' => 83478, 'c' => array(9, 8, 7));
$s = serialize($obj);
var_dump($s);echo '<br><br>';
$obj2 = unserialize($s);
var_dump($obj2);echo '<br><br>';
echo $obj2->id;
 
?>

Now, all that being said, you certainly can try to circumvent having to have the whole string from sql by using regex, but if you just had the whole object instead like this serialize/unserialize gives you it may save you other queries/regex's so I figured I'd point it out.

[astupidname]

although when I try to unserialize the string you show within <metavalue> it does not work for me

Holy crap, I don't know what I did wrong when I tried it before but I must have botched something. I just revisited and tried it again from scratch and it works perfectly now! I never actually doubted I was right.... :)

First, a little bit more for you about serialize() and unserialize(). The item which is represented by the string within your <metavalue> tags is in fact one array which contains as it's only indexed item another array which holds only name/value pairs and no indexed items.
When I purposely manually break that string from your <metavalue> tags down and format it, the result you are looking at is this:

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:"";
    }
}

Note how serialize() (which was obviously used to store that string) is always formatted as
(property descriptor type - 's' for string, 'a' for array, 'i' for integer, not shown above - but 'O' for object) : (property descriptor length - except in the case of integer type property descriptors then property descriptor length not shown and goes straight to property descriptor which is an integer) : (property descriptor); (property value type - see descriptor type for 's','a' 'i' etc.) : (property value length) : (property value);

So, now the proof that it works:

<?php
 
//the string which is within your <metavalue> tags:
$s = '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:"";}}';
 
//will be an array with one indexed item which is another array
$arr = unserialize($s);
//let's give you a visualization of what that array contains
echo '<pre>';print_r($arr);echo '</pre>';
 
echo $arr[0]['id'];
 
?>

So now you have quick easy access to all 10 of the name/value pairs within the subarray! No regex needed here.. not that there's anything wrong with regex, but always best to do things right and when something's been serialized, just unserialize!
Hope this all helps!

[astupidname]

I think this must be a serialised string of an array?

Holy crap again! You know, I'm certain I did not actually liminally read that, must have either subliminally noticed it or was just on the same wave length as you there. And here I go into a whole diatribe about serializing when you already had the inkling of the idea!
:)