smd_query: Talk to the database directly via SQL

johnnie · 2008-07-25 12:00:54

Just wanted to thank you for this wonderful plugin. It really helps me work with product feeds!

Bloke · 2008-08-09 11:18:52

johnnie wrote:

Just wanted to thank you for this wonderful plugin. It really helps me work with product feeds!

You’re welcome. If you’ve done some good stuff with it, please consider sharing examples here on the thread that might help others. Thanks!

jakob · 2008-09-04 21:55:45

Thank you again, Bloke. I’d not forgotten the thread, just have been working on non-txp stuff for a while.

I can’t make it fail with any errors unless I do some horrible stuff with quotes (like put single quotes inside single quotes or mis-match them

In short, after removing all other conditionals, this does seem to have been the problem. It gets pretty hairy with the new parser when you have to change the single quotes to nest tags, then watch out for quotes around contained attributes.
Also, a key aspect is to put the query='...' in single quotes… as in your example, and to change the other parts of the query to use double-quotes. Without this, the txp:variable is not parsed resulting in no output.

End of the story: txp:else in smd_query now works just fine :-)))

Bloke · 2008-09-04 23:04:35

jakob wrote:

End of the story: txp:else in smd_query now works just fine :-)))

Excellent, glad you got it sorted. Yes, it does get a bit interesting with all the different types of quote characters floating about. When I eventually get this up and running with the URL line, try combining this with <txp:smd_if />, <txp:smd_vars /> and/or <txp:variable /> if you really want to make your eyes roll into the back of your head :-)

sthmtc · 2008-10-03 15:34:30

Ok, let’s suppose I have some articles that belong to a specific section. Most of these articles contain comma separated values in the keywords field. Would it be possible to build a query that tells me which keywords are the most popular in that section? :)

Last edited by sthmtc (2008-10-03 15:34:58)

merz1 · 2008-10-03 16:23:31

The plug-in tru_tags contains code which counts the numbers of tags to render a tag cloud. And tru_tags can also render tag clouds for specific sections. You might want to take a look at the tru_tags code.

pieman · 2008-10-24 13:00:36

thank you thank you thank you

Bloke · 2008-10-24 13:29:38

pieman wrote:

thank you thank you thank you

You’re welcome welcome welcome :-)

If you have any cool tidbits of info on how you used this plugin to solve your particular issue, feel free to share it here. Ta!

Bloke · 2008-11-24 03:01:07

Alright folks, this is the big one. smd_query goes ballistic.

v0.2 | compressed features:

<txp:variable /> support so you can read values from those lovely things directly into a query instead of having to embed them tag-in-tag stylie and get hung up on quoting issues
URL variable support: yes, I took the plunge, you can now take arbitrary user input off the address bar and plug it into a query as well, making all manner of crazy HTML form stuff possible. But PLEASE PLEASE PLEASE read the docs (especially the ‘Filtering and injection’ part) on how to help keep your database safe when using this feature. See this: *scrub scrub scrub* ? That’s me washing my hands of any wrongdoing this plugin may cause ;-)
To complement the URL variable ability are a slew of security features. By default all URL variables are run internally through gps() and doSlash() to make them as safe as possible. I’ve not managed to get an injection attack past it all evening (doesn’t mean it’s not possible of course). For all the security muffins out there, you can further filter the input by specifying characters that you will allow or disallow in user variables. The urlfilter and urlreplace attributes are advanced features (they’re regexes) but are well worth befriending if you are going to allow user input in your queries. If you have any hassles with them, there are a bajillion resources on the web or, failing that, shout here and someone who eats regular expressions for breakfast will help
You can specify default values in the event a variable fails a check or is missing, e.g. if you restrict a variable to the words ford, jaguar and toyota and someone supplies “nissan”, that variable ‘fails’ and returns nothing. If you have set up a default value for that variable then it will come into play at that point. This allows your queries to degrade more gracefully in the event of bogus input
Some convenience attributes delim and paramdelim have been added in case you’re working with complex filters that require those characters

As ever, use this tag as a last resort when all built-in tags and other plugins fail you, or when you just need to write some seriously powerful apps without getting your fingers dirty in PHP. Post here if you come up with nifty queries that might help others, or you do something mad like rewrite Second Life in TXP! The floor is also always open if you find a way to improve the plugin, need a hand getting it working or (gasp!) find a bug.

I know I always seem to say this when I release or update a plugin but it’s phenomenal what you can do with this sucker. Nobody has any excuse for not being able to do anything ever again :-D

Play safe.

Last edited by Bloke (2008-11-24 03:15:10)

mrdale · 2008-11-24 16:59:40

Huzzah! er… my brain hurts… Horray!

gomedia · 2009-02-21 21:20:41

Hi Stef, just been playing with your top smd_query plugin. Should be very useful for getting boy scouts out of horses hooves.

I’m a little confused though. In your Example 8 from the plugin help:

<txp:smd_query query="SELECT Title
     FROM textpattern
     WHERE status = '?user_status'"
     urlfilter="/[^1-5]/"
     defaults="user_status: 4">
   <txp:permlink>{Title}</txp:permlink>
</txp:smd_query>

Shouldn’t <txp:permlink> be <txp:permlink id={id}">? Because without the id="{id}" bit I get lots of:

Tag error: <txp:permlink> -> Textpattern Notice: Article tags cannot be used outside an article context on line 2075

Cheers,

Adi

Bloke · 2009-02-21 22:38:24

gomedia wrote:

Example 8 from the plugin help… Shouldn’t <txp:permlink> be <txp:permlink id={id}">?

For purism, yes. Good catch. Although if you are trying to display articles from a query then it kind of loosely implies that the smd_query should be wrapped in <txp:if_individual_article /> tags or TXP will complain, as you found.

That’s the downside to giving raw SQL access in a plugin: you are still bound by TXP’s law (or is that lore?!) and have to work within its parameters in order to achieve sensible (read: error free) output.

But I should probably either wrap the example in the conditional or add the id="{id}" for completeness, so thanks for the report. Since there are no new features on the horizon I’ll fix the docs so any new downloads get the alterations.

Textpattern CMS

Textpattern CMS support forum

#73 2008-07-25 12:00:54

Re: smd_query: Talk to the database directly via SQL

#74 2008-08-09 11:18:52

Re: smd_query: Talk to the database directly via SQL

#75 2008-09-04 21:55:45

Re: smd_query: Talk to the database directly via SQL

#76 2008-09-04 23:04:35

Re: smd_query: Talk to the database directly via SQL

#77 2008-10-03 15:34:30

Re: smd_query: Talk to the database directly via SQL

#78 2008-10-03 16:23:31

Re: smd_query: Talk to the database directly via SQL

#79 2008-10-24 13:00:36

Re: smd_query: Talk to the database directly via SQL

#80 2008-10-24 13:29:38

Re: smd_query: Talk to the database directly via SQL

#81 2008-11-24 03:01:07

Re: smd_query: Talk to the database directly via SQL

#82 2008-11-24 16:59:40

Re: smd_query: Talk to the database directly via SQL

#83 2009-02-21 21:20:41

Re: smd_query: Talk to the database directly via SQL

#84 2009-02-21 22:38:24

Re: smd_query: Talk to the database directly via SQL

Board footer