My SQL Query works but not with a variable in the query

Support
1

I am trying to get the value of a resource, dependent on the current id. I know I can use getResourceField for this but that couldn’t get the value either. I have been able to successfully get the value, but not with the variable as the ‘contentid’.

<?php
//$query = 'SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = '.$id.' AND `tmplvarid` = 31';
$query = 'SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = 282 AND `tmplvarid` = 31';
$result = $modx->query($query);
if (!is_object($result)) {
   return '';
}
else {
   $row = $result->fetch(PDO::FETCH_ASSOC);
   return $row['value'];
}

This works and returns 1, however doing it as a variable returns blank.

I really can’t work out why this isn’t working, if you return just the query with the variable so you can see the value you can see the query is the same?

2

I can’t see, where $id is coming from

3

It’s added as an &id=[[+id]] in the snippet call.

For example [[exampleSnipptet? &id=`[[+id]] ]]

4

I think [[+id]] isn’t set at the time, when your snippet is called.
Where is the placeholder coming from?

5

As i said, you can return the $query and see the correct id in the query so it must be getting it. The [[+id]] is coming from the id of the resource being called in a getResources tpl

6

well, that doesn’t say, that placeholder is set allready. your snippet could ouput that placeholder and it gets parsed after that. So you will see the id in the query anyway.

7

how does your getResources - call look like?

8

can’t you just get the TV-value with getResources?

9

No it’s a little hard to explain.

I’m using getResources to build a JSON object of a bunch of my resources that can be used as an API.

The problem is my tpl has a gallery chunk in, which needs to know the gallery album to use. It can’t get it through getResources for some reason, it also can’t get it from getResourceField. Even using return $modx->resource->getTVValue(’31’); doesn’t return the value.

The only way I have managed to get the value is by doing the MySQL query myself, which works fine. The only problem is it isn’t working with the variable, which is needed.

10

just show us your whole code. I’m sure, there is a simple reason, why this doesn’t work for you.
Maybe we will have an easier solution at all for your situation.

11

Now I feel really stupid, none of the template variables are pulling through. I had been so focused on the gallery I missed the fact there was nothing pulling through other than the standard tags. Maybe my error can be sorted with getResources. I’m going to mess around and come back

12

Fixed the tv issue I set tv prefix to blank and then i removed tv. from the tpl and it worked. All worked other than the Gallery.

Here is the get resources call:
{
[[!getResources?
&parents=[[*id]]
&tpl=propertyFeed.tpl
&tplLast=propertyFeedLast.tpl
&includeTVs=1
&processTVs=1
&tvPrefix=``
&includeContent=1
&limit=0
]]
}

Here is the tpl (last is the same but without a comma at the end):
“[[+propertyID]]”: {
“pagetitle”: [[!if:JSONencode? &subject=[[*dev.postcode]] &operator=notempty &then=[[+pagetitle]], <a href="https://maps.google.com/maps?q=[[+dev.postcode]]" style="color: #d86802;" target="blank">[[+dev.postcode]]</a> &else=[[+pagetitle]]]],
“longtitle”: “[[+longtitle]]”,
“desc”: “[[+description]]”,
“alias”: “[[+alias]]”,
“featured”: “[[+dev.featured]]”,
“luxury”: “[[+dev.luxury]]”,
“propStatus”: “[[+dev.status]]”,
“featuredVideo”: “[[+dev.featuredVideo]]”,
“company”: “[[+dev.company]]”,
“thumb-image”: “[[++site_url]][[+dev.thumb-image]]”,
“thumb-video”: “[[+dev.thumb-video]]”,
“thumb-videoLink”: “[[+dev.thumb-videoLink]]”,
“thumb-videoID”: “[[+dev.thumb-videoID]]”,
“mainImage”: “[[++site_url]][[+dev.mainImage]]”,
“galleryLarge”: [ [[$imageArrayLarge:stripNL:stripLastComma]] ],
“galleryThumb”: [ [[$imageArrayThumb:stripNL:stripLastComma]] ],
“beds”: “[[+dev.beds]]”,
“price”: “[[+dev.price]]”,
“address”: “[[+dev.address]]”,
“location”: “[[+dev.location]]”,
“postcode”: “[[+dev.postcode]]”,
“mapLink”: “[[+dev.mapLink]]”,
“map”: [[!If:JSONEncode? &subject=[[+dev.map]] &operator=notempty &then=<div class="dev-info-map">[[+dev.map]]</div> &else=]], "tel": "[[+dev.tel]]", "email": "[[+dev.email]]", "opening": "[[+dev.opening]]", "brochure": [[!if:JSONEncode? &subject=`[[+dev.brochure]]` &operator=`notempty` &then=`<div class="dev-info-link"><a href="[[++site_url]][[+dev.brochure]]" target="_blank" style="text-decoration: none;"><i class="fas fa-download"></i>&ensp;&ensp;View brochure</a></div>` &else=]],
“directions”: “[[+dev.directions]]”,
“availability”: “[[+dev.availability]]”,
“content”: [[+content:JSONEncode]]
},

Here is the large gallery chunk (thumb is the exact same just smaller images):
[[Gallery? &album=[[+dev.gallery]]&thumbWidth=1500&thumbHeight=851&thumbQuality=75&thumbTpl=imageArray.tpl]]

The tpl for this just formats the gallery so it goes into an Array.

Hope this is enough and sorry about the formatting of the code.

13

You are calling getResources uncached, while you are calling Gallery cached.
This might cause your issue.
I think, in your case, you can call everything cached.

14

To format a block of code, you can just put three back-ticks above and below it.

BTW, the code in your original post can be simplified to two lines (unless you’re doing it that way for the very small speed increase you might get):

$tvr = $modx->getObject('modTemplateVarResource', 
    array('contentid' =>$id, 'tmplvarid' => 31));

return $tvr? $tvr->get('value') : '';
15

you might also need to pass dev.gallery to the chunks

“galleryThumb”: [ [[$imageArrayThumb:stripNL:stripLastComma? &dev.gallery=`[[+dev.gallery]]`]] ],