How to get a specific value of an array stored in a mysql database

Question

In a Mysql Database the value of a field is like this:

a:1:{i:0;s:1:"3";}

The value i need here is the 3.

If it is a normal number i could sum up selected field with the query but like this i fail

Is it possible to make the math within the query or do i have to process the result of the query (how?) ?

Update due to Answer of @kabirbaidhya Making the unserialize here Online gives the right result.

Processing the Query gives not the expected

function summe_bilden() 
{
    global $wpdb;
    $value = $wpdb->get_results("SELECT VALUE FROM wp_vxcf_leads JOIN wp_vxcf_leads_detail ON wp_vxcf_leads.id = wp_vxcf_leads_detail.lead_id WHERE name = 'stunden'");  
    foreach($value as $a)
        { 
         $arr = unserialize($a);
         var_dump($a);
         echo $arr[0];
        } 
          
}

object(stdClass)#9463 (1) { ["VALUE"]=> string(18) "a:1:{i:0;s:1:"3";}" } object(stdClass)#9464 (1) { ["VALUE"]=> string(20) "a:1:{i:0;s:3:"9,5";}" } object(stdClass)#9465 (1) { ["VALUE"]=> string(20) "a:1:{i:0;s:3:"7,5";}" }

Update 2: Final working code for me:

    foreach($value as $a)
    { 
     $array = $a->VALUE;
     $ar = unserialize($array);
     echo $ar[0];
     echo '</br>';
    }

Every number is normal , besides that extract the JSON content and calculate — nbk
– nbk, Commented Sep 25, 2020 at 20:31
this is json notation - are you storing json as entity? than calculation outside, after converting and extraction — black blue
– black blue, Commented Sep 25, 2020 at 20:35

AmirAli Esteki · Accepted Answer · 2020-09-25 20:42:10Z

1

Quick answer: NO

there is not any serialize or unserialize function in sql

so you need to get all data from your sql and unserialize they, and finally get sum of your selected value. but pay attention my entered sample maybe have problem in big datas

example:

<?php

$query = {your query}

$sum = 0;

while($row = $query->fetch_array(MYSQLI_ASSOC)){

    $column = $row['column'];
    $column = unserialize($column);
    
    $column = (int)$column;

    $sum = $sum + $column
    
}

?>

answered Sep 25, 2020 at 20:42

AmirAli Esteki

5623 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kabirbaidhya · Accepted Answer · 2020-09-27 16:02:28Z

0

This is a value persisted in the database by serializing an array using PHP function serialize.

That means, the only correct way is the deserialize it back using PHP with its corresponding function unserialize.

Steps:

Get the value from the the db with a query like SELECT VALUE from your_table.
Unserialize the value to get an PHP array which gives you the data you're looking for.

foreach($value as $a) { 
    $arr = unserialize($a->VALUE);
    var_dump($a);
    echo $arr[0];
}

Note: You shouldn't process it with mysql query alone - it's not reliable and is not deterministic for this use case.

edited Sep 27, 2020 at 16:02

answered Sep 26, 2020 at 9:19

kabirbaidhya

3,5085 gold badges39 silver badges60 bronze badges

6 Comments

Arwed Over a year ago

i don´t get further with that.... just using your lines for testing returns bool(false)

kabirbaidhya Over a year ago

What did you do? Check your database query you used to extract the string. Did you check if it was correct? The unserialize function returns false if the input was invalid, read more - php.net/manual/en/function.unserialize.php

Arwed Over a year ago

the query is ok - i checked it directly at phpmyadmin did you see my update on my original question?

kabirbaidhya Over a year ago

@Arwed Just saw your updated query, and found the issue. Check my updated code. Your column name was VALUE so you should unserialize it's value instead of the whole object.

Arwed Over a year ago

unserialize($a["VALUE"]); doesn´t work, i got it working with the code above Update2 Anyway thx for help

|

Arwed · Accepted Answer · 2020-09-25 23:07:37Z

0

After some more searching for the term "unserialize($column)" i found another post related to my question Unserialize through query at database level itself

After some try and error for the right position i found this query working

SELECT VALUE, SUBSTRING_INDEX(SUBSTRING_INDEX(VALUE,'"',2),'"',-1) AS stunden FROM wp_vxcf_leads JOIN wp_vxcf_leads_detail ON wp_vxcf_leads.id = wp_vxcf_leads_detail.lead_id WHERE name = 'stunden'

answered Sep 25, 2020 at 23:07

Arwed

9711 bronze badges

3 Comments

AmirAli Esteki Over a year ago

this query is not the correct way for your needs and only solve your problem. becuase your data is not a simple string that you wanna use substring. substringing is for spliting spe specific range of a string. not for getting X position of an serialized data. anyway. be care this query don't make another problems for you. good luck

Arwed Over a year ago

think i already found the problems you mentioned :) going this way and using sum() it gives the incorrect number ---> 7,5 + 9,5 + 3 gives me 19 instead of 20

kabirbaidhya Over a year ago

This might work for one or two specific arrays with your use case. But it's not correct, since the serialized string could be far more complex depending on the length and type of elements it holds, not to mention they could be nested.

Collectives™ on Stack Overflow

How to get a specific value of an array stored in a mysql database

3 Answers 3

Comments

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related