Textpattern Support Forum

smd_query… good idea…

Consider sharing the solution here :)

smd_query is nice indeed..
I was making things more complicated then I should have been. Should have RTFM…
The populate=“article” attribute solved quite some headaches. (processing custom fields etc)

Every entry has only one rendition in it’s original language.
For every available site language, a custom field holds the part I do want to have translated (in this case, where the writer came from (eg “a family from Belgium”, “een familie van België”, “une famille venant de la Belgique”).
I’m ordering by title (which contains the date of the entry in the physical guestbook) as I’m not sure they will be inputting them in order.

<txp:smd_query 
       query="SELECT * FROM 
              (
                SELECT *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod FROM l10n_txp_en_gb
                UNION ALL
                SELECT *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod FROM l10n_txp_nl_nl
                UNION ALL
                SELECT *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod FROM l10n_txp_fr_fr
              ) ALIAS
               WHERE 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section IN ('guestbook')
              ORDER BY Title DESC"
         populate="article"
         form="guestbook_entry" />

EDIT: I took the SQL statements from a regular article_custom query. The fields and dates etc are needed for the populate attribute to work. /EDIT

Thanks to the populate=“article” attribute you can use the regular article related tags in the used form.

Any opinions on putting the SQL WHERE clause just at the end or in all separate SELECT statements?

Now I just have to add the filtering (probably just a show all languages/show user language toggle). And look in to pagination. Indeed, I did not really RTFM on everything about smd_query… But these things are probably better off in another thread. But I’d welcome any pointer :)

I’m happy. I tamed MLP :)
Thank you both for your help!

Cheers,
Jeroen

Last edited by jeroen (2012-06-21 22:13:35)

I just updated one of my sites to txp to 4.4.1 and the latest MLP pack (featuring plugin 4.3.0.12). Now, no articles are displayed anymore. Not as a list, and not as a single article. I have looked at the tags and the syntax, and everything appears to be OK…

I am totally stumped!

Any suggestions as to how I should approach fixing this problem?

maniqui wrote:

1) Backup the database
2) Just to breath more calm, check if the articles are there in the DB
3) Repeat/review the installation & upgrade process in case you missed something (for example, to replace txplib_db.php).
4) Finally, you could try running the uninstall MLP wizard, and then, running the install wizard again.

Thanks, maniqui!

I haven’t yet done the above, but I am getting the following errors:

Tag error: <txp:article form="single" listform="default" limit="4" searchall="1" /> -> Textpattern Warning: Unknown column 'Expires' in 'where clause' select count(*) from mainl10n_txp_en_us as l10n_txp_en_us where 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section != 'about' and Section != 'books' and Section != 'map' and Section != 'bibliography' textpattern/lib/txplib_db.php:264 getThing() on line 113 textpattern/lib/txplib_db.php:113 trigger_error() textpattern/lib/txplib_db.php:344 safe_query() textpattern/lib/txplib_db.php:264 getThing() textpattern/publish.php:791 safe_count() textpattern/publish.php:964 doArticles() textpattern/publish.php:592 parseArticles() textpattern/publish.php:1188 article() textpattern/publish.php:1100 processTags() textpattern/publish/taghandlers.php:3586 parse() textpattern/publish.php:1188 if_search()

Tag error: <txp:article form="single" listform="default" limit="4" searchall="1" /> -> Textpattern Warning: Unknown column 'Expires' in 'field list' select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from mainl10n_txp_en_us as l10n_txp_en_us where 1=1 and Status = 4 and Posted <= now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') and Section != 'about' and Section != 'books' and Section != 'map' and Section != 'bibliography' order by Posted desc limit 0, 4 textpattern/lib/txplib_db.php:258 startRows() on line 113 textpattern/lib/txplib_db.php:113 trigger_error() textpattern/lib/txplib_db.php:323 safe_query() textpattern/lib/txplib_db.php:258 startRows() textpattern/publish.php:815 safe_rows_start() textpattern/publish.php:964 doArticles() textpattern/publish.php:592 parseArticles() textpattern/publish.php:1188 article() textpattern/publish.php:1100 processTags() textpattern/publish/taghandlers.php:3586 parse() textpattern/publish.php:1188 if_search()

I think this may be the key issue: Textpattern Warning: Unknown column 'Expires' in 'where clause'

maniqui wrote:

Did you log in in the admin side? It may be that the update script for 4.4.1 hasn’t been run yet.

Yes, I thought of that, too. Logged out and back in several times. Even re-uploaded the Update folder, but no luck…