smd_query: Talk to the database directly via SQL

Bloke · 2016-02-15 10:56:37

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.

etc · 2016-02-26 14:42:50

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

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

jakob · 2016-05-26 11:03:23

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 ;-)

etc · 2016-05-26 11:30:42

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.

jakob · 2016-05-26 12:46:13

Thanks Oleg!

jeroenvg · 2017-03-19 17:46:52

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)

uli · 2017-03-19 17:58:39

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)

jeroenvg · 2017-03-25 14:42:14

uli wrote #304911:

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

thanks, that’s cleared up.

jeroenvg · 2017-03-25 15:28:38

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?

jeroenvg · 2017-03-27 14:45:38

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.

jeroenvg · 2017-04-04 16:00:04

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

Bloke · 2017-04-04 16:23:31

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!)

Textpattern CMS

Textpattern CMS support forum

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

Re: smd_query: Talk to the database directly via SQL

sacripant wrote #297837:

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

jakob wrote #299292:

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

uli wrote #304911:

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305030:

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

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305081:

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

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305213:

Board footer