smd_query: Talk to the database directly via SQL

aslsw66 · 2010-01-28 20:27:16

Can this plugin be used to delete content from the database?

I have used mem_simple_form to add content from the front-end to a new database table ie. I am not messing about with core database tables.

I plan on using smd_query to extract this data and display it. However, I have realised that it would be nice to be able to delete or update some of this content.

Any pointers on how to do this? If I could find another plugin that did something similar, I could work my way through it but as far as I can tell the only functionality like this is in the core.

Thanks,

Bloke · 2010-01-29 09:22:15

aslsw66 wrote:

Can this plugin be used to delete content from the database?

Hell yes! :-) Just be careful you sanitize everything and check, check, check that your data is as as valid as you can before throwing it at smd_query. The latest version of smd_query with its automatic mode detection means you can use the plugin without container:

<txp:smd_query query="DELETE FROM textpattern WHERE something='whathaveyou' AND summatelse='somevalue'" />

it would be nice to be able to delete or update some of this content.

You can insert / update rows using logic such as this:

<txp:variable name="row_exists"><txp:smd_query query="SELECT ID FROM textpattern WHERE blah='blahblah'">{ID}</txp:smd_query></txp:variable>

<txp:if_variable name="row_exists" value="">

   <txp:smd_query query="INSERT INTO textpattern SET Title='some_title', Body_html='some_content', etc" />

<txp:else />

   <txp:smd_query query="UPDATE textpattern SET Title='some_title', Body_html='some_content', etc WHERE ID='?row_exists'" />

</txp:if_variable>

Try that and see how you get on.

Last edited by Bloke (2010-01-29 09:24:19)

aslsw66 · 2010-01-29 10:13:13

Thanks, I will try this out tonight.

Just glancing at your suggestions, the key issue seems to be to get variables out of a submitted HTML form and into the coding. I confess that this is something I have never really got my head around (despite dissecting plugins to examine their innards) but I will give it a go.

Bloke · 2010-01-29 10:31:03

aslsw66 wrote:

to get variables out of a submitted HTML form and into the coding

smd_query makes it easy(ish) becuase you can use the ?variable_name syntax inside your queries and container/form. So if your HTML form POSTs variables called product and quantity (or supplies a GET request with ?product=jeans&quantity=4) they are available to the plugin as ?product and ?quantity respectively.

The only thing you have to worry about is that nobody has tried to screw with the data before you unwittingly use it to construct a harmful query. Read up about ways to minimise the impact of this in the plugin help (under Filtering and injection) or by searching the web for SQL injection attacks.

aslsw66 · 2010-01-29 10:48:49

Does this mean I can post the form back to itself ie. back to the page the form appears on?

If so, that is brilliantly simple!

I understand your concerns with the quality of the data (you have repeated them in many places!).

What I want to use this for is to allow senior members of my volunteer fire brigade (say, only the 5 officers) to post an alert message for the day eg. total fire ban in place. They aren’t necessarily technically savvy (apart from me!), so I plan to use ign_password_protect and privileges to allow them to do this from the front-end but with access limited to only these people. But I guess I need to protect them from their own incompetence so I will read up as well.

Bloke · 2010-01-29 11:02:47

aslsw66 wrote:

Does this mean I can post the form back to itself ie. back to the page the form appears on?

Yes. That’s the best way to do it in my experience.

I understand your concerns with the quality of the data (you have repeated them in many places!).

:-/ Sorry. Stuck record mode *off*

But I guess I need to protect them from their own incompetence

Hehehe, if someone can do something you haven’t anticipated, chances are they will do it by mistake.

^{Note to Micro$oft: this does not give you licence to display “Unexpected Error #187b4c82a” to users :-)}

Last edited by Bloke (2010-01-29 11:02:58)

merz1 · 2010-01-29 16:16:23

OMG Can somebody please jot down a tutorial about how to realize your own craigslist section inside TXP? With categories, tags, etc.

add yellow page items
add calendar entries
auto-vanish after n days

Does this mean I can post the form back to itself ie. back to the page the form appears on?

An incredible eye opener…

I don’t even want to start thinking about the endless possibilities :)

aslsw66 · 2010-02-05 21:14:20

Stef

I’ve been using your suggestion to have a go at this. I’m stumbling over two issues:

when you delete an items, you don’t actually see the deletion unless you refresh the page ie. you can’t just press delete and have it magically disappear.

if I use sm_query to submit new data and update a table in the database, refreshing the page results in the same information being submitted again and again.

Any ideas on how to make this a lot neater?

Thanks,

Anura

[EDIT] For God’s sake, don’t ask me to post any code. I’m chopping and changing constantly!

Last edited by aslsw66 (2010-02-05 21:14:53)

merz1 · 2010-02-06 08:54:08

Make sure that all cache solutions (browser, server, database, TXP, etc.) are switched off for testing the changes.
Re. TXP cache: Switch off ‘last modification date’ and don’t use a cache plug-in like asy_jpcache.

Bloke · 2010-02-06 11:00:32

aslsw66 wrote:

when you delete an items, you don’t actually see the deletion unless you refresh the page… refreshing the page results in the same information being submitted again and again

Time to be devious :-)

Assuming you’re posting the same page back to itself, the issues can usually be “solved” by making your page flow like this:

<txp:smd_if delete detected>
   <txp:smd_query do delete />
</txp:smd_if>

<txp:smd_if update detected>
   <txp:smd_query do update />
</txp:smd_if>

<txp:smd_query get the results />

So, in theory your deletion/update occurs, then the query goes and grabs the latest information and displays it.

This won’t however, solve your refresh issue. To successfully do that, I’ve employed this technique in the past:

<txp:smd_if delete detected>
   <txp:smd_query do delete />
   <txp:php>header('Location: http://www.site.com/section/this_article');</txp:php>
</txp:smd_if>

<txp:smd_if update detected>
   <txp:smd_query do update />
   <txp:php>header('Location: http://www.site.com/section/this_article');</txp:php>
</txp:smd_if>

<txp:smd_query get the results />

What that does, is essentially do the delete and then do a forced page refresh without any arguments passed. Thus the second time the page reloads, it bypasses the ‘delete’ and ‘update’ portions and just shows the results of your meddling.

This technique relies on you not having sent any output to the browser at the time the header() command is called. If you’ve already sent something in your page at that point (e.g. a doctype and the <html> or <head> tags) then you can’t use that trick. In that case, if you’re clever about where you place the delete/update queries in the page flow — I suggest doing them in the <head> since they shouldn’t ever output anything — you can use the good old meta refresh tag instead, like this:

<txp:smd_if delete detected>

   <txp:smd_query do delete />
   <meta http-equiv="refresh" content="0">

<txp:else />

   <txp:smd_if update detected>

      <txp:smd_query do update />
      <meta http-equiv="refresh" content="0">

   <txp:else />

      <txp:smd_query get the results />

   </txp:smd_if>

</txp:smd_if>

So you’re now nesting the ifs such that it only ever executes one branch at a time and therefore only outputs one meta tag. That means as soon as the — largely empty — skeleton page has loaded in the case of the delete/update, it refreshes pretty quickly and then skips the delete/update to go straight to the ‘show results’ query.

Your page in the delete/update cases can simply be the bare minimum, e.g. <!doctype><html><head><meta refresh></head><body></body></html> to make sure that it looks to the visitor that it’s only submitting once when it’s really doing a cheeky double-refresh of the page. Crucially, this means that the last thing in the browser history is the ‘no parameters’ version of the page so no matter how many times the page is subsequently refreshed, nothing untoward happens.

Does that make any sense or give you some food for thought?

Last edited by Bloke (2010-02-06 11:01:33)

aslsw66 · 2010-02-06 11:11:27

Thanks for the detailed response. I hadn’t even considered the need to put all of this in the <head>.

I really like the fact that you provide just enough information which, cobbled together with my minimal understanding of PHP and TXP, means that I will spend another evening engrossed in this! Much better than watching whatever is on the TV… except that I watch TV on my laptop so I can do a bit of both.

maniqui · 2010-02-06 16:25:46

Bloke wrote:

Your page in the delete/update cases can simply be the bare minimum, e.g. <!doctype><html><head><meta refresh></head><body></body></html> to make sure that it looks to the visitor that it’s only submitting once when it’s really doing a cheeky double-refresh of the page. Crucially, this means that the last thing in the browser history is the ‘no parameters’ version of the page so no matter how many times the page is subsequently refreshed, nothing untoward happens.

That’s the magic that happens here at the forum, when I hit the “Submit” button, right?

I’ve always wondered how that “redirect-that-doesn’t-get-saved-in-the-browser’s-history” works. I must confess that, even after your explanation, I doesn’t get it. How should I “google” this trick to have more information about it?
Thanks, Bloke!

Textpattern CMS

Textpattern CMS support forum

#181 2010-01-28 20:27:16

Re: smd_query: Talk to the database directly via SQL

#182 2010-01-29 09:22:15

Re: smd_query: Talk to the database directly via SQL

#183 2010-01-29 10:13:13

Re: smd_query: Talk to the database directly via SQL

#184 2010-01-29 10:31:03

Re: smd_query: Talk to the database directly via SQL

#185 2010-01-29 10:48:49

Re: smd_query: Talk to the database directly via SQL

#186 2010-01-29 11:02:47

Re: smd_query: Talk to the database directly via SQL

#187 2010-01-29 16:16:23

Re: smd_query: Talk to the database directly via SQL

#188 2010-02-05 21:14:20

Re: smd_query: Talk to the database directly via SQL

#189 2010-02-06 08:54:08

Re: smd_query: Talk to the database directly via SQL

#190 2010-02-06 11:00:32

Re: smd_query: Talk to the database directly via SQL

#191 2010-02-06 11:11:27

Re: smd_query: Talk to the database directly via SQL

#192 2010-02-06 16:25:46

Re: smd_query: Talk to the database directly via SQL

Board footer