smd_query: Talk to the database directly via SQL

jeroenvg · 2018-05-21 18:00:35

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

lists <vacant> places of <total> places – a counter,
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

Bloke · 2018-05-22 08:11:54

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?

jeroenvg · 2018-06-26 15:41:18

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.

jeroenvg · 2018-06-26 15:51:02

jeroenvg wrote #311942:

does smd_query cache results?

think i found what’s going on. from looking at mysql query log, i can see smd_query asking for the current score from my table when the page with html form is rendered. but for the (new, uncached) ‘thank you’ page, the query is not repeated.

so, the ‘thank you’ page gets the counter value somewhere else. could it be that they’re carried over from the html form?

if so, is there a way to clear smd_query values beforehand, to make the ‘thank you’ page query my table again?

aslsw66 · 2018-08-31 08:28:40

I’m back in the land of Textpattern after a long break!

I have a page that returns results from the database – in this case, a list of incidents. I have a form that allows the user to filter the list according to the type of incident – a fire, a community event or a controlled burn. Or everything. The form posts the selection in a URL variable called ‘type’.

Here’s my code so far:

<txp:smd_query query="SELECT id, date_format (date_out,'%d/%m/%Y') as date, type, description, location FROM gck_incidents where type like '?type' order by date_out desc" limit="25" urlfilter="/(\ball\b)/" defaults="type:%" pageform="page_info">

This works perfectly for any selected value, but if the user leaves the value as the default of ‘all’ then nothing is returned. I’m still trying to figure out regular expressions so I know this needs more work to stop users adding anything else they want to the URL. But for testing purposes it would be good to at least get this working.

Last edited by aslsw66 (2018-08-31 08:29:20)

demoncleaner · 2019-12-30 11:04:18

Just in case the world is not aware of it… I just found out that this beautiful peace of code will unfortunately not work anymore under textpattern 4.8. (beta) It will just output nothing.

gaekwad · 2019-12-30 13:26:56

demoncleaner wrote #320759:

I just found out that this beautiful peace of code will unfortunately not work anymore under textpattern 4.8. (beta) It will just output nothing.

I am sure Stef (bloke) will be able to make it work again, he’s that kind of guy.

Bloke · 2019-12-30 15:07:28

The plugin tag just needs registering. I can do that and release an update as soon as I can, but if you want to do it yourself in the meantime, see the tag registering example.

demoncleaner · 2020-01-02 16:20:54

Perfect. Will have a look at it. Thank you so much!

etc · 2020-01-03 13:23:23

I would expect many front-side plugins to stop working in 4.8. But it’s the third txp version since introducing tags registry, the authors had almost four years to update their plugins.

Bloke · 2020-01-04 00:31:02

etc wrote #320798:

the authors had almost four years to update their plugins.

Okay okay, guilty! I’ll get it fixed :)

demoncleaner · 2020-05-16 06:43:47

Sorry to come up with this again, but I run into the same problem again and I did not really manage to solve it the last time. I took a look at the post tag registering but I have no clue where to register it or where to put what exactly in case of smd_query. Can you help please?

Bloke · 2020-05-16 10:20:25

Sorry for the huge delay. I’ve finally released smd_query v0.6.0 which brings with it tag registration as well as <txp:smd_query_info /> tag which you can use instead of the {replacement} syntax for added power and flexibility.

demoncleaner · 2020-05-16 15:19:49

Thanks a lot Stef!

But I have to admit that my very problem was at the end not related to smd_query. I found out that it was in combination with smd_macro that caused the problem. I am using smd_query inside a smd_macro which until the update to 4.8.0 was working like a charm.

After I updated smd_query to 0.6.0 my problem was still there. First I tried to register the tags for smd_macro by putting the code for tag registration just inside the code on the top but no success. Maybe I miss something here? Would be good to know on how tag registration should be done in general to be able to help myself in the future. But I don´t want to go too much off-topic here.

For smd_macro: I know that I should not be using smd_marco anymore because txp forms can do the same and better. So I arranged all my macros in forms now and voila. Everything seems fine now.

Bloke · 2020-05-16 15:24:29

demoncleaner wrote #323014:

I tried to register the tags for smd_macro by putting the code for tag registration just inside the code on the top but no success.

If you’re using the latest version then it should auto-register your tags. Strange if not.

I arranged all my macros in forms now and voila. Everything seems fine now.

Yay, good. While it works well, smd_macro is a massive hack so the more people that can move to core shortcodes, the better.

Textpattern CMS

Textpattern CMS support forum

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

Bloke wrote #311954:

#319 2018-06-26 15:51:02

Re: smd_query: Talk to the database directly via SQL

jeroenvg wrote #311942:

#320 2018-08-31 08:28:40

Re: smd_query: Talk to the database directly via SQL

#321 2019-12-30 11:04:18

Re: smd_query: Talk to the database directly via SQL

#322 2019-12-30 13:26:56

Re: smd_query: Talk to the database directly via SQL

demoncleaner wrote #320759:

#323 2019-12-30 15:07:28

Re: smd_query: Talk to the database directly via SQL

#324 2020-01-02 16:20:54

Re: smd_query: Talk to the database directly via SQL

#325 2020-01-03 13:23:23

Re: smd_query: Talk to the database directly via SQL

#326 2020-01-04 00:31:02

Re: smd_query: Talk to the database directly via SQL

etc wrote #320798:

#327 2020-05-16 06:43:47

Re: smd_query: Talk to the database directly via SQL

#328 2020-05-16 10:20:25

Re: smd_query: Talk to the database directly via SQL

#329 2020-05-16 15:19:49

Re: smd_query: Talk to the database directly via SQL

#330 2020-05-16 15:24:29

Re: smd_query: Talk to the database directly via SQL

demoncleaner wrote #323014:

Board footer