Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

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

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: 33

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: 33

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: 8,787
Website

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

#325 2017-04-05 16:34:45

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote #305214:

I’m just curious, where is the field article_id in your setup?

in a Form (type Article), called from an Article (see #304909, #305030).

from the sentence in the plugin’s help:

“Specify the field name with a ? in front of it (e.g. query=“SELECT * FROM txp_image WHERE category=’?category1’ OR category=’?category2’) would show images that had their category set to one of the article’s categories.”

…i understood that i need to rewrite:

<txp:category1 />

…as:

?category1

…to use its value in a smd_query attribute.

Offline

#326 2017-04-05 17:30:12

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,787
Website

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #305229:

Specify the field name with a ? in front of it…

That’s correct. Look at the database table textpattern. Any field (column) there is fair game, e.g. Title, Body_html, custom_1, url_title, ID, category1, and so forth. But there’s no column called article_id so ?article_id won’t match anything and will thus shrug and return the string ‘article_id’ as nothing matched and the plugin doesn’t know what to do with it.

The only time it would match anything (which spurred my question about whether you’d defined it yourself somewhere… though I didn’t articulate that very well, sorry) is if you’d previously done this:

<txp:variable name="article_id" value="480" />

or, more usefully:

<txp:variable name="article_id" value='<txp:article_id />' />

But if you’re doing that and not reusing the variable, you might as well just stick to your workaround and use the tag inside the smd_query attribute directly.

Does that make sense?


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

#327 2017-04-07 14:47:16

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote #305232:

Does that make sense?

yip, thanks!

it was category1 in your example that got me off the wrong foot. i understood you ‘rewrote the textpattern tag in question mark form’, but it was the table column label Category1 you were referring to.

Offline

#328 2018-05-21 18:00:35

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

Re: smd_query: Talk to the database directly via SQL

does smd_query cache results?

i’ve built a plug-in to take reservations for workshops. on a workshop page (an article) for any workshop, it

  1. lists <vacant> places of <total> places – a counter,
  2. takes a reservation by asking for name and e-mail address – an (html) form.

reservations are, among other result actions, stored in a table in the database.

the counter (1) is a (txp) Form my plug-in created, that uses smd_query to read the current score from the database table.

the form (2) is a (txp) Form my plug-in created, that uses zem_contact_reborn for form flow, and adi_contact to store the reservation.

the problem
after a successful form submit, on the ‘thank you’ page, the counter is always 1 behind, or – put another way: it did not count the reservation that just happened, or – again in other words: it seems that smd_query did not run again to get the latest score from the database, though the ‘thank you’ page is a new/ changed (not-cached) page (it shows the ‘thank you’ message).

this leads to some customers repeating their reservation, because they did the math too and think something went wrong. the workshop shows the correct score however, if they’d browse away and navigate back to the page.

this is how i tried to fix it

  • set $prefs['send_lastmod'] = false; at the start of my plug-in, for pretext_end event.
  • from my plug-in, hooked into zemcontact.deliver event on the public side, to run update_lastmod(). (this sets lastmod in table txp_prefs to <now>).
  • copied zem_contact_reborn.php to my plugin directory, and changed the Last-Modified header in zem_contact() from one week ago to <now>.
  • checked that ‘thank you’ page never loads from a browser memory or disk cache (true).
  • searched smd_query.php for caching functions (can’t find any).

i’m running out of solutions. anything you can think of?

context

  • Preferences: Site: Production status: Live
  • Preferences: Publish: Send “Last-Modified” header: No
  • zem_contact_reborn 4.5.0.0-beta.4
  • smd_query 0.50
  • txp 4.6.2
  • php 5.5.9

Offline

#329 2018-05-22 08:11:54

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,787
Website

Re: smd_query: Talk to the database directly via SQL

If you’re reading from a table then you’ll hit this issue if you reload the same page and the content isn’t yet updated. You can force fetch the content by hand first into a variable and use that in your form. Or try changing the load order of the plugins?


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

#330 2018-06-26 15:41:18

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote #311954:

If you’re reading from a table then you’ll hit this issue if you reload the same page and the content isn’t yet updated.

checked that. i happen to have created and modified timestamps in the table i’m saving to. created is a value the form generated. modified an automatic timestamp for the last time the contents of the table row changed. i can easily compare these to a timestamp php writes to the page, from code in the Form.

if i make the html form sleep for 10 seconds after it saved the values to the database, and before the page reloads to show the counter and ‘thank you’ message again, it still shows the wrong (not updated) count.

Or try changing the load order of the plugins?

tried that too, with all combinations of ‘earlier’/ ‘later’ for smd_query, zem_contact_reborn, adi_contact and my plugin. but that doesn’t fix it.

Offline

Board footer

Powered by FluxBB