[Solved] Search and return TV values via MySQL or PDO

95 Posts

banbbc Reply #1, 15 years, 8 months ago

I’ve a checkbox TV which I use to relate resources. I need a way to return the resource IDs to the getResources snippet. I thought this simple snippet might work.

<?php
$stmt = $modx->query("SELECT value FROM catw_site_tmplvar_contentvalues WHERE (tmplvarid = 2 AND value = $resourceid)");
if ($stmt) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $output .=  $row ['value'];
      }
}
return $output;

If the resource ID is 121 then the value returned is ’121||120||119’ which is perfect but if the resource ID is either 120 or 119 nothing is returned. Obviously the reasons no results are returned for 120 and 119 are clear, but how do I get round this?

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #2, 15 years, 8 months ago

Please give more information about how your TV is set up.. what is it storing in the database?

The way I’m reading your code is that you’re are checking the template variables which have a value of (I assume) the resource ID you are on? In other words, if a resource has that value (and only that - you’re doing an exact search) it returns the "value" column, which is your resource ID? I can’t see how this can be returning 121||120||119... huh

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

95 Posts

banbbc Reply #3, 15 years, 8 months ago

Mark I’m using the TV as describe by swespi:

http://modxcms.com/forums/index.php/topic,60381.msg344114.html#msg344114

I’ve two folders consisting of ’products’ and ’reviews’. A review resource might review one or more products. So if a review resource (80) reviews three products (say resources 119, 120, 121) I have the above checkbox TV (id 2) to ’relate’ the products. This is stored in the catw_site_tmplvar_contentvalues table as:

tmplvarid 2 contentid 80 value 121||120|119

On the product page I have two getResources calls, one to link to the review and one to link to the other products reviewed on those pages. So a product page (121) should list the review (80) and the products (119, 120) and it does. But pages 120 and 119 don’t produce results.

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #4, 15 years, 8 months ago

I guess you’d have to use LIKE In the query to see if it appears anywhere in the value, and then explode the result set on || and use in_array() to see if it is a true match, or "1" matching "102" for example.

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

95 Posts

banbbc Reply #5, 15 years, 8 months ago

Thanks Mark, I followed your suggestion and although I’ve not tested it throughly yet it does appear to be working now. Cheers.

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #6, 15 years, 8 months ago

Glad you got it figured out. laugh

Could you share your solution for others who might stumble on this topic?

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

95 Posts

banbbc Reply #7, 15 years, 8 months ago

No problem but the coding won’t win any awards. laugh

[[!getResources? &resources=`[[!relateReviews? &tvid=`13` &resid=`[[*id]]`]]` &tpl=`relateTpl` &showHidden=`1` &includeContent=`1`]]

where $tvid is the id of my checkbox TV used for relating reviews with products.

The relateReviews snippet, which returns id’s of related resources to getResources, is:

<?php
$stmt = $modx->query("SELECT value FROM catw_site_tmplvar_contentvalues WHERE (tmplvarid = $tvid AND value LIKE '%$resid%')");
if ($stmt) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
           $data = explode('||' , $row['value']);
        if (in_array($resid, $data)) {
    $output .=  $row ['value'];
       $output .= "||"; }
} }
$output=str_replace('||',',',$output);
$output=str_replace("$resid,",'',$output);
$output=str_replace(',,','',$output);
$output = substr($output,'',-1);
return $output;
?>

The changes are exactly as Mark suggested. My ugly $output=str_replace code at the end simply changes the output 121||120|119|| to 121,119 for resource 120. There will be a better way of doing this.