(Evo) TV Delimited List and DB Query

☆ A M B ☆
1,231 Posts

AugmentBLU Reply #1, 15 years, 7 months ago

I need to create a way to display specific results in a custom database table in the modx database according to IDs in a TV. I am trying to create a list like 1234,4321,2314,4231 and for the rows with these IDs to be displayed. I have created the TV already, I see that the TV is an array, what would be the best method to take the values from the TV and display the results?

One other thing is I have another table that holds further details that is linked to the other table. How would I search both tables at once using the same TV input? I usually do a DB query within a query but that’s turning out to be a lot of requests.

Cheers!

Ross Sivills - MD AugmentBLU Edinburgh, Scotland UK
AugmentBLU - MODX Partner

BLUcart - MODX Revolution E-Commerce & Shopping Cart

2,310 Posts

bunk58 Reply #2, 15 years, 7 months ago

Just to start you of, not meant as a complete solution!

<?php
$tv = $modx->getTemplateVarOutput(true, $modx->documentIdentifier);  // grabs all the TV's value fields for the current document id
$your_ids = $tv['NameOfTV']; // selects only the named TV's value field
$rs = $modx->db->query('SELECT * FROM `your-custom-table` WHERE `key-id` IN('.$your_ids.')');  // select any rows that have a 'key-id' contained in the list from the previously selected TV
foreach($rs as $key => $val){
// do something with each row of data from the custom table
    $output. = $val['field-name'];
    }
return $output;
?>

To get data from the second table you can do a JOIN before the WHERE statement, along the lines of :

LEFT JOIN your-custom-table ON second-custom-table.matching-key-id = your-custom-table.key-id

☆ A M B ☆
1,231 Posts

AugmentBLU Reply #3, 15 years, 7 months ago

Thanks Bunk, I worked this out not long before you posting! Bookmarked this though for future ideas... laugh

Ross Sivills - MD AugmentBLU Edinburgh, Scotland UK
AugmentBLU - MODX Partner

BLUcart - MODX Revolution E-Commerce & Shopping Cart