smd_query: Talk to the database directly via SQL

Bloke · 2011-10-18 15:01:10

aslsw66 wrote:

I think the issue is getting this to work nicely across three different plugins.

Gotcha. That will throw some spanners in the machine. Hmmm. [EDIT: stupid code hack removed]

I think you’re stuffed in this case, sorry. Can’t think of a way to selectively parse content in the form/container, which is essentially what you’re trying to do: you need to ‘mask off’ the mem_form stuff until later, parse the rah_repeat bits and then go back to the mem_form bits to complete the DB insert. A tall order in one container.

The only way I can possibly think of that might get what you want is to have the notion of a post-record Form. In other words, once you’ve used the container to do your transmogrification of content and whatnot, hand off control to a separate form which holds the mem_form stuff and allows you to still use the {replacements} you’ve built up. Do you think that’d work or would it still present problems in your case? If it might get you closer to a solution I can look into it.

Last edited by Bloke (2011-10-18 18:42:33)

aslsw66 · 2011-10-19 08:39:45

I’m not stressing about this. I’m only using rah_repeat to streamline my query. The main thing I want to avoid is multiple smd_queries (I have two already) but I can achieve what I want to through just repeating the code manually.

There are far more important things to worry about!

kuopassa · 2011-12-19 09:17:59

Is it possible to get an average value of two things that are to be echoed like below? I need to smash the pluses and minuses together and return what’s the average of them — let’s say with one decimal accuracy.

<txp:smd_query query="SELECT * FROM txp_wlk_helpful_counts WHERE textpattern_id = 62">{pluses}{minuses}</txp:smd_query>

Last edited by kuopassa (2011-12-19 09:18:55)

jens31 · 2011-12-19 10:10:37

aks_var can make simple variable calculations..

kuopassa · 2011-12-19 10:47:26

Thanks, jens31. That was helpful. So my code block is now this:

<txp:smd_query query="SELECT * FROM txp_wlk_helpful_counts WHERE textpattern_id = 61">{pluses} pluses and {minuses} minuses is <txp:aks_evar name="average" value="0" calc="({pluses}+{minuses})/2" /></txp:smd_query>

Seems to work pretty well. Although if either {pluses} or {minuses} is zero, the system doesn’t recognize it and does that calculation anyway. I tried originally to not to do this thing with plugins because it would be easier with raw PHP. But then the problem would be to put this code inside Textpattern… Does anyone know if I need a third plugin to compare if {pluses} or {minuses} is empty, or does smd_query have some inbuilt function for it?

jens31 · 2011-12-19 10:56:30

just guessing, but smd_query pulls out normal txp variables, aint it?
so this might be it:

<txp:if_variable name="pluses" value="0">something else<txp:else /><txp:if_variable name="minuses" value="0">something else<txp:else />PARTY</txp:if_variable></txp:if_variable>

..?
(dunno if i took the correct variable names, but..)

Last edited by jens31 (2011-12-19 10:59:00)

kuopassa · 2011-12-19 11:44:12

Thanks again for taking the time to answer, jens31. The code you offered is indeed pointing towards the right direction. I think the best solution is to somehow get out of the tag soup and use raw PHP as that offers better solutions for conditional statements. I’ll keep on experimenting. :-)

txpdevcoder · 2012-06-15 09:54:15

hi bloke / everyone.

Nice plugin. Ive got a tricky one and i think smd_query can help, but im tottally lost.

Im trying to create a collapasable tree of all the articles on the site grouped into year and month, as nested lists. Ive got this far, but beside the year and the month, i want to put an article count in brackets beside the month and the year.

original forum thread here, with the code im using already to list it out. i just need the article count adding in:

Forum thread for collapsable accordian

How do i throw smd-query into that to get the article count?

Cheers!

-J

Last edited by txpdevcoder (2012-06-15 09:55:24)

maruchan · 2012-06-27 22:28:13

Stef: Thank you for this very useful plugin. I’m trying to use {prefix.column} and it’s not working—is this available some other way? Example:

<txp:smd_query break="li" wraptag="ul" query="select f.id,f.filename,f.title,c.title from txp_file f left outer join txp_category c on f.category=c.name where c.name like 'fun-downloads%'">			
                      <a href="<txp:site_url />file_download/{id}/{filename}">{f.title}</a> {c.title}
                      <txp:else />
                      <txp:variable name="no-results-message" />
                    </txp:smd_query>
                    <!-- end test -->

This outputs “{f.title}” and “{c.title}” on the page literally.

Thanks!

Last edited by maruchan (2012-06-27 22:28:59)

Bloke · 2012-06-27 22:34:38

maruchan

Try:

select f.id,f.filename,f.title as file_title,c.title as cat_title from...

and use {file_title} and {cat_title} instead.

Last edited by Bloke (2012-06-27 22:35:16)

maruchan · 2012-06-27 23:09:33

Works great, Stef. Thanks.

piyashrija · 2012-07-06 00:00:53

Hi

I am not able to retrieve future article with query below:

(Posted < NOW() AND Expires >= NOW()): this part works fine
(Posted > NOW() this doesn’t work why

<txp:smd_query query = "SELECT ID FROM textpattern WHERE (Posted > NOW() OR (Posted < NOW() AND Expires >= NOW())) AND Section = 'event'">

Thanks

^{Stef: Edited for code display}

Last edited by Bloke (2012-07-06 00:09:25)

uli · 2012-07-06 12:16:35

piyashrija, are you actually looking for the time="any" attribute/value pair in article_custom?

piyashrija · 2012-07-07 07:09:38

Thanks for reply

I dont want all the article to get display. I hope “any” will display all article including past one as well.

I have date range like 06-07-2012 ::06-09-2012 or 06-08-2012::06-09:2012.

so just want to display all the article that is posted relative to today and still active.

thanks

piyashrija · 2012-07-07 09:29:00

Sorry about the stupid question

<txp:smd_query query = "SELECT ID FROM textpattern WHERE (Posted > NOW() OR (Posted < NOW() AND Expires >= NOW())) AND Section = 'event'">
<txp:article id="{ID}" time ="any" >

will solve the problem..

I am new to textpattern and thought if i pass future id in article tag it should display future article as well.

{Edited to add bc. for better code display. – Uli}

Last edited by uli (2012-07-07 11:37:23)

Textpattern CMS

Textpattern CMS support forum

#271 2011-10-18 15:01:10

Re: smd_query: Talk to the database directly via SQL

#272 2011-10-19 08:39:45

Re: smd_query: Talk to the database directly via SQL

#273 2011-12-19 09:17:59

Re: smd_query: Talk to the database directly via SQL

#274 2011-12-19 10:10:37

Re: smd_query: Talk to the database directly via SQL

#275 2011-12-19 10:47:26

Re: smd_query: Talk to the database directly via SQL

#276 2011-12-19 10:56:30

Re: smd_query: Talk to the database directly via SQL

#277 2011-12-19 11:44:12

Re: smd_query: Talk to the database directly via SQL

#278 2012-06-15 09:54:15

Re: smd_query: Talk to the database directly via SQL

#279 2012-06-27 22:28:13

Re: smd_query: Talk to the database directly via SQL

#280 2012-06-27 22:34:38

Re: smd_query: Talk to the database directly via SQL

#281 2012-06-27 23:09:33

Re: smd_query: Talk to the database directly via SQL

#282 2012-07-06 00:00:53

Re: smd_query: Talk to the database directly via SQL

#283 2012-07-06 12:16:35

Re: smd_query: Talk to the database directly via SQL

#284 2012-07-07 07:09:38

Re: smd_query: Talk to the database directly via SQL

#285 2012-07-07 09:29:00

Re: smd_query: Talk to the database directly via SQL

Board footer