Using Database with template variable(s)?

Hi,

I’m trying to figure out how to use Template Variables to display database data.

As an example, I have a content page and want to display the shop name, city and telephone from a DB table

My solution so far has been to...

- add new table to the modxfs database
- get the [*pagetitle*] (which is the shop name)
- create a ’getdatabase’ snippet and pass the shop name variable in as a snippet parameter (based on the code in
http://wiki.modxcms.com/index.php/DB_query_and_Placeholders)
- display the returned data using the chunk in the same example

(I’ll post this example to the wiki in the next few days.)

This all works fine but the big downside is that it doesn’t appear in the search results. Obviously I could write my own conventional search script and return the results from that (but lose the functionality of the search that comes with MODx out of the box)

So I want to do the same using TVs which I understand do search within the database. I have read the docs but am really struggling due to the lack of examples.

Anyone got any pointers please?
Do I use the @SELECT or @EVAL binding? Why would I want to use one in preference to the other?
Do I need to create a separate TV for each field e.g. [*tv_shopname*], [*tv_city*], [*tv_tel*]?
In this case would I need to have a custom SQL query for each (which seems really inefficient....must be a better way of doing it)?

Thanks very much!
Mark.

TVs will not solve your problem unless you copy the data from your tables into TVs so that the data is there for the existing search components to use. You can use snippets and such within TVs, but the only thing that will be searchable really is the snippet call or SQL statement itself. You would honestly be much better off writing a custom search component that was aware of your custom tables and how they are linked to your views.

Thanks for the info. The data doesn’t need to be dynamically retrieved from the database table so I’m open to alternative options. But I do need to store the content info in a database.

>unless you copy the data from your tables into TVs
How would I do that please? Can I write a script that will populate the TVs? (Which presumably I would run again to refresh the data after a database update?)

Sorry for the dumb newbie questions...

cheers,
Mark.

You can write Plugins and attach them to various events, like the OnDocFormPrerender and OnDocFormSave events which would allow you to update the data when editing a document (you can detect the POST variables representing the Template Variable and intercept/alter/use the values as you need to in these plugins). There are other events and methods you could use to do it, for instance, when the site cache is cleared, etc. It truly depends on the nature of your data...

I haven’t tried this yet --> http://modxcms.com/forums/index.php/topic,27316.msg205086.html#msg205086

It looks as a nice approach!

Thanks guys.

Think the plugin option looks too technically challenging for me. I simply want to select a single recordset and display some of the fields.

I thought the
http://wiki.modxcms.com/index.php/DB_query_and_Placeholders
option was OK, but the snippet/chunk seems to have a a bug - a blank line in the array since it repeats any static content you put it. Unsure if this is a PHP/array issue or the MODx parser? May post this issue separately....

Will have a look at that Ajax search and see if it does the trick. Cheers.

The code from that wiki article works well, but you would need to do something like (http://modxcms.com/forums/index.php/topic,33881.0.html) to control the output further.

AMDbuilder