Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#313 2016-02-15 10:56:37
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
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
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
Re: smd_query: Talk to the database directly via SQL
Offline
#317 2016-05-26 12:46:13
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,310
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
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