How to extract a single value from a json encoded database table column value in Joomla?

Question

I'm trying to get an image url from Joomla via PHP/Sql Query. It's a multidimensional array or object, but I can't get the second dimensions.

What data type is that? Do I have to convert it somehow? I already tried "loadAssocList()" and "loadObjectList()", but both give me strange data.

How do I convert it to a multi-dimensional array, or even better, get access to the $r[images][intro_image] value? And why are the slashes escaped there?

Joomla DB Query like in Documentation:

$db = JFactory::getDbo();
$id = 9; //example
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__content');
$query->where('catid="'.$id.'"');

$db->setQuery((string)$query);
$res = $db->loadAssocList();
?>
<?php foreach($res as $r): ?>
<pre>
<?php print_r($r); ?>
</pre>
<?php endforeach; ?>

PHP Response Array:

[fulltext] => 
    [state] => 1
    [catid] => 9
    [created] => 2018-09-10 20:45:29
    [created_by] => 165
    [created_by_alias] => 
    [modified] => 2018-09-14 08:28:52
    [modified_by] => 165
    [checked_out] => 165
    [checked_out_time] => 2018-09-14 08:32:10
    [publish_up] => 2018-09-10 20:45:29
    [publish_down] => 0000-00-00 00:00:00
    [images] => {"image_intro":"images\/thumb_2017-28-04-Taspo.jpg","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"images\/thumb_2017-28-04-Taspo.jpg","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}
    [urls] => {"urla":"\/images\/\/Presseartikel\/2017-28-04-Taspo-optimiert.pdf","urlatext":"","targeta":"","urlb":false,"urlbtext":"","targetb":"","urlc":false,"urlctext":"","targetc":""}
    [attribs] => {"article_layout":"","show_title":"","link_titles":"","show_tags":"","show_intro":"","info_block_position":"","info_block_show_title":"","show_category":"","link_category":"","show_parent_category":"","link_parent_category":"","show_associations":"","show_author":"","link_author":"","show_create_date":"","show_modify_date":"","show_publish_date":"","show_item_navigation":"","show_icons":"","show_print_icon":"","show_email_icon":"","show_vote":"","show_hits":"","show_noauth":"","urls_position":"","alternative_readmore":"","article_page_title":"","show_publishing_options":"","show_article_options":"","show_urls_images_backend":"","show_urls_images_frontend":""}
    [version] => 5

I need to read data from this part of the array:

{"image_intro":"images\/thumb_2017-28-04-Taspo.jpg","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"images\/thumb_2017-28-04-Taspo.jpg","float_fulltext":"","image_fulltext_alt":"","image_fulltext_caption":""}

score 0 · Accepted Answer · answered Sep 14 '18 at 10:38

0

Images are in JSON format so you first need to decode that data.

$images = json_decode($r['images']);
print_r($images);

then you can access image_intro using below code -

$images->image_intro;

answered Sep 14 '18 at 10:38

Irfan

7,029
3
42
57

wtf they dont simply use multidimensional array for that ? :( – Tdotcom Sep 14 '18 at 10:42

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

I have a few points to make.

Since you only want to access the image_intro value for the selected row, you should query for precisely that -- as a matter of best practice.

Nominate images as the target column from the #__content table and cast your $id value as an integer if that variable is fed by an insecure resource (for instance, user submitted data).
loadResult() is the best call to access the (single column & single row) value in the result set.
It is a good habit to build in error catching (try{}catch{}) and echoing checkpoints for debugging purposes. Just be careful not to display php's actual error messages to the end user because bad people may be able to do sinister things with this high value feedback.
The value that is returned in the result set is actually a JSON object. To sensibly access a value within by its key, you must first decode the value into a php array or object. json_decode() is the correct call for this task. If you feed the function your json value without a 2nd parameter, an object array will be generated and accessing the image_intro value is done with ->image_intro syntax. If you use true as the the 2nd parameter, an array will be generated and ["image_into"] syntax should be used.

Code:

$id = 9;  // assumed to be insecure, so casting with (int) is done in snippet
try {
    $db = JFactory::getDbo();
    $query = $db->getQuery(true)
                ->select("images")
                ->from("#__content")
                ->where("catid = " . (int)$id);
    echo $query->dump();
    $db->setQuery($query);
    if (!$result = $db->loadResult()) {  // declare $result and check for falsey value
        echo "Sorry no row found @ id = " , (int)$id;
    } else {
        echo "Image Intro = " , json_decode($result)->image_intro;
    }
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage();  // never show getMessage() details to the public
}

The above snippet will display the built query and desired output: (it will be your db prefix)

SELECT images FROM lmnop_content WHERE catid = 9

Image Intro = images/thumb_2017-28-04-Taspo.jpg

p.s. When you need Joomla-specific support, please post your question at Joomla Stack Exchange. This is where the Joomla team wants you to post so that the community can be better supported.

You may or may not notice that in the administrator section of your site, there is a Help tab and the 2nd last item on the dropdown is Stack Exchange <-- this will transport you directly to JSE.

How to extract a single value from a json encoded database table column value in Joomla?

2 Answers2