get last value from JSON array

Question

I have a JSON string within a MySQL database which I could like to get 2 numbers from within WordPress.

My string is as follows:

a:1:{i:300;a:4:{s:7:"lessons";a:1:{i:302;i:1;}s:6:"topics";a:1:{i:302;a:2:{i:304;i:1;i:311;i:1;}}s:9:"completed";i:1;s:5:"total";i:4;}}

The i:300 is the course ID. Within the topics is both 302 and 311. These are the IDs of each page completed. I need to get the last one of these which in this case would be 311.

I basically need something that would do the following:

Get Page ID Find Page ID in JSON string Get the last ID from JSON string topics

How would I go about this?

Update - Getting the string from the DB but doesn't display.

$conn = new mysqli($servername, $username, $password, $dbname);
$user_id = get_current_user_id();
$sql1 = "SELECT meta_value FROM xrji_usermeta";
$sql3 = " WHERE user_id = ".$user_id." AND meta_key = '_sfwd-course_progress';";
$sql =  $sql1 . "" . $sql3;
//echo "sql: ".$sql;
$a = $conn->query($sql);
$results = unserialize($a);
echo "<br>results: ".$results;

I have the above however nothing is shown for $results however if I run the query through sequel pro it works fine.

SELECT meta_value FROM xrji_usermeta WHERE user_id = 2 AND meta_key = '_sfwd-course_progress'

This string is serialized array. First you need to unserialize() it; that access it as ordinary array; demo. — Glavić
– Glavić, Commented Jul 7, 2016 at 9:41
Great, your got an array, now use array_pop() to get last value of the needed array. — Glavić
– Glavić, Commented Jul 7, 2016 at 9:50

Serge Uvarov · Accepted Answer · 2016-07-07 10:05:39Z

3

What you posted is not a JSON, it's a result of PHP serialization that is done through serialize() function.

In order to use the values, you have to get this string and pass to the unserialize() function, which will give you... well, it will give you an error:

PHP Notice: unserialize(): Error at offset 5 of 235 bytes

That's because you assumed it was JSON and formatted it for us. It's okay for JSON and death to serialized string. You need to get rid of whitespaces:

'a:1:{i:300;a:4:{s:7:"lessons";a:1:{i:302;i:1;}s:6:"topics";a:1:{i:302;a:2:{i:304;i:1;i:311;i:1;}}s:9:"completed";i:1;s:5:"total";i:4;}}'

After you do unserialize() on it, you will get:

Array
(
    [300] => Array
        (
            [lessons] => Array
                (
                    [302] => 1
                )

            [topics] => Array
                (
                    [302] => Array
                        (
                            [304] => 1
                            [311] => 1
                        )

                )

            [completed] => 1
            [total] => 4
        )
)

So, you get a PHP array in $a and now you can do whatever you want with it:

$courseID = 300;
$lessons = array_keys($a[$courseID]['lessons']);
$topics = array_keys($a[$courseID]['topics']);
$pages = array_keys($a[$courseID]['topics'][ $topics[0] ]);
$lastPage = $pages[ count($pages)-1 ];

You will have 302 in $topics, 304 and 311 in $pages and 311 in $lastPage.

Sidenote: How to distinguish JSON from PHP serialization

This is a bit of a simplification, but it's good for memorization. Whenever I see a bunch of <one-letter>:<number>:<value> periods, I assume that it's the serialization result, where <one-letter> is type (array, string, integer) and <number> is a length in characters or array elements (omitted for integer). If instead of <one-letter> I see descriptive variable names, enclosed in "quotation_marks", or arrays that are wrapped in [square brackets], I assume it's JSON.

edited Jul 7, 2016 at 10:05

answered Jul 7, 2016 at 9:54

Serge Uvarov

1099 bronze badges

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

3 Comments

Serge Uvarov Over a year ago

I'm not allowed to chime in to the long discussion in the comments to your question, but please try the code I described here, it seems to have solved your problem.

Ben H Over a year ago

I have a query to get the string from the database and if i run the query within sequel pro it works fine however when running through PHP i get zero output. I have updated my question with the code. Would you mind taking a look? Thanks

Serge Uvarov Over a year ago

@BenH, after $conn->query() line, add $result = $a->fetch_array(MYSQLI_ASSOC); and then var_dump($result);

Collectives™ on Stack Overflow

get last value from JSON array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related