Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#313 2016-02-15 10:56:37

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,436
Website GitHub

Re: smd_query: Talk to the database directly via SQL

sacripant wrote #297837:

Sometime I create Json with your plugin, and it’s not super convenient. Perhaps the plug-in could do that directly ?

Not sure. Would have to dive back into the code and see what’s possible. With the nature of the plugin being that you build custom output using replacement tags, I’m not sure how useful it would be to assume you want all data to be output as JSON and thus pre-format it. I’ll have a think about it.

In the meantime if you have any concrete examples of tags you are using, and the output you want the plugin to create from them, it might give me some ideas on how to implement something.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#314 2016-02-26 14:42:50

etc
Developer
Registered: 2010-11-11
Posts: 5,159
Website GitHub

Re: smd_query: Talk to the database directly via SQL

Sorry to poke my nose in others affair, but this should do it:

<txp:etc_query data="SELECT * from txp_users" query="$json_encode" />

Offline

#315 2016-05-26 11:03:23

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,719
Website

Re: smd_query: Talk to the database directly via SQL

Hi Stef, I’m using smd_query in conjunction with txp v.4.6dev and populate="article" to query results of a section based on a custom field holding a date:

<txp:smd_query query="SELECT *,
     unix_timestamp(Posted) as uPosted,
     unix_timestamp(LastMod) as uLastMod,
     unix_timestamp(Expires) as uExpires
     FROM textpattern 
     WHERE Status IN (4,5)
     AND section = 'aktuelles'
     AND custom_14 >= CURDATE()
     ORDER BY custom_13 ASC
     LIMIT 5"
     wraptag="" break="" form="event_homepage_item"
     populate="article" />

where custom_14 holds the end date in format YYYY-MM-DD and custom_13 the start date in the same format and the form event_homepage_item looks like this (simplified):

<txp:if_first_article>
            <ul class="meldungen"> 
</txp:if_first_article>
              <li class="meldung">
                <p class="date">…</p>
                <h3>
                  <a href="/<txp:permlink />"><txp:title /></a>
                </h3>
              </li>
<txp:if_last_article>
            </ul>
</txp:if_last_article>

Weirdly, I get the opening <ul…> after the first entry though the closing </ul> is correct. If I add row-{smd_thisrow} to the opening ul class, I get row-2 so it definitely kicks in on the second loop.

It works using:

<txp:smd_if field="{smd_thisrow}" operator="eq" value="1">
            <ul class="meldungen"> 
</txp:smd_if>
…
<txp:smd_if field="{smd_thisrow}" operator="eq" value="{smd_allrows}">
            </ul>  
</txp:smd_if>

so the row count is correct.

The other problem I have is how to handle the falseCase, i.e. no results when using a form. I get no output at all with:

<txp:smd_query query="…"
     wraptag="" break="" form="event_homepage_item"
     populate="article">
<txp:else />
  <p>No results</p>
</txp:smd_query>

I guess because the output is being handled via a form and not the container. I tried using the following in the form:

<txp:smd_if field="NULL" operator="eq" value="{smd_allrows}">
 <p>No results</p>
<txp:else />
…
</txp:smd_if>

but also got no output at all. Setting debug="1" gives me the query but no results in the array. Am I missing something elementary?

For the moment, I’ve worked around it with adi_if_content, so it’s non-urgent ;-)


TXP Builders – finely-crafted code, design and txp

Offline

#316 2016-05-26 11:30:42

etc
Developer
Registered: 2010-11-11
Posts: 5,159
Website GitHub

Re: smd_query: Talk to the database directly via SQL

jakob wrote #299292:

For the moment, I’ve worked around it with adi_if_content, so it’s non-urgent ;-)

Just fyi, an updated 4.6 version can be downloaded here. Nothing new, just optimized for 4.6 parser (hence won’t work in 4.5). Sorry for off-topic.

Offline

#317 2016-05-26 12:46:13

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,719
Website

Re: smd_query: Talk to the database directly via SQL

Thanks Oleg!


TXP Builders – finely-crafted code, design and txp

Offline

#318 2017-03-19 17:46:52

jeroenvg
Member
From: netherlands
Registered: 2010-04-21
Posts: 34

Re: smd_query: Talk to the database directly via SQL

for a form call from an article body:

...
article body: <txp:output_form form="smd_query_test" />
...

and form smd_query_test (Form type: Article), querying my own jvg_booking table in the TXP database:

<txp:smd_query column="*" table="jvg_booking">
    <p>{smd_allrows} row(s)</p>
</txp:smd_query>

i get this warning at the top of the article view (public side, Production status: Debugging):

Tag error: <txp:smd_query column="*" table="jvg_booking"> ->  Textpattern Notice: unregistered_tag while parsing form smd_query_test on page workshops
textpattern/lib/txplib_publish.php:518 trigger_error()
textpattern/lib/txplib_publish.php:463 processTags()
textpattern/lib/txplib_misc.php:4415 parse()
textpattern/publish/taghandlers.php:487 parse_form()
output_form()
textpattern/vendors/Textpattern/Tag/Registry.php:83 call_user_func()
textpattern/lib/txplib_publish.php:514 Textpattern\Tag\Registry->process()
textpattern/lib/txplib_publish.php:463 processTags()
textpattern/publish/taghandlers.php:2846 parse()
body()

works, though:

“article body:
1 row(s)”

how can i get rid of the Tag error?

(context: txp 4.6.2, smd_query 0.50)

edit: added context

Last edited by jeroenvg (2017-03-25 14:34:32)

Offline

#319 2017-03-19 17:58:39

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,306

Re: smd_query: Talk to the database directly via SQL

Hi jeroen, please see this topic, the first two links about tag registration might help you.

But basically you just turn off debug mode.

Edit: Link correction.

Last edited by uli (2017-03-19 17:59:27)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#320 2017-03-25 14:42:14

jeroenvg
Member
From: netherlands
Registered: 2010-04-21
Posts: 34

Re: smd_query: Talk to the database directly via SQL

uli wrote #304911:

[…] the first two links about tag registration might help you.

thanks, that’s cleared up.

Offline

#321 2017-03-25 15:28:38

jeroenvg
Member
From: netherlands
Registered: 2010-04-21
Posts: 34

Re: smd_query: Talk to the database directly via SQL

for my test described in #304909, but with a shorthand WHERE clause added to the form:

<txp:smd_query column="*" debug="1" table="jvg_booking"
    where="article_id='?article_id'">
    <p>{smd_allrows} row(s)</p>
</txp:smd_query>

i expected ?article_id to resolve to 138; the ID of the current article, but it doesn’t (debug info, there is no output at all on the page):

SELECT * FROM jvg_booking WHERE article_id='article_id'

from the plugin help, i understood that question mark replacements may be used “to read values from the current context”.

did i get this wrong?

Offline

#322 2017-03-27 14:45:38

jeroenvg
Member
From: netherlands
Registered: 2010-04-21
Posts: 34

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305030:

did i get this wrong?

bingo, it needs single quotes:

where='article_id=?article_id'

(and debug info seems to echo the query before replacements are resolved.)

sorry for the noise.

Offline

#323 2017-04-04 16:00:04

jeroenvg
Member
From: netherlands
Registered: 2010-04-21
Posts: 34

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305081:

bingo, it needs single quotes:

where='article_id=?article_id'

errr, spoke too soon. this returns all rows from my table, not just the ones for the current article.

looked in general_log for the actual query mysql server received. for where='article_id=?article_id' it logs:

181 Query SELECT * FROM jvg_booking WHERE article_id=article_id
so:
  • question mark replacements are broken (or i’m using them the wrong way),
  • debug info is correct; it shows the actual query that is send to the server.

as a workaround, where='article_id=<txp:article_id />' works:

190 Query SELECT * FROM jvg_booking WHERE article_id=139

Offline

#324 2017-04-04 16:23:31

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,436
Website GitHub

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305213:

where=‘article_id=?article_id’

I’m just curious, where is the field article_id in your setup? Is it a txp:variable? If not, all you have to play with are the article columns that are in the textpattern table. In which case, you probably want:

where='article_id=?ID'

The question mark variables inject info from the ‘current context’ (i.e. Textpattern scope), txp:variables or the URL query params (if used with care!)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

Board footer

Powered by FluxBB