Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#277 2011-09-09 14:03:33

jakob
Admin
From: Germany
Registered: 2005-01-20
Posts: 4,198
Website

Re: smd_query: Talk to the database directly via SQL

Two quick questions:

  1. Is there a way of forcing the pageform to appear permanently (for example to display page information) even if there is only a single page? In my experiments it only shows if there is more than one page.
  2. Is there a way to treat pagepos “above” differently from “below”, for example to integrate the top navigation within a header element? I tried with <txp:smd_if field="{smd_thisrow}" operator="eq" value="1"> but I guess the pagination is independent of a particular row.

TXP Builders – finely-crafted code, design and txp

Offline

#278 2011-10-18 09:03:04

aslsw66
Member
From: Canberra, Australia
Registered: 2004-08-04
Posts: 342
Website

Re: smd_query: Talk to the database directly via SQL

Stef, this is just something I have discovered but not necessarily a bug… an enhancement maybe?

I have a table where the fields are named the same but have a single digit on the end eg. “crew1” to “crew8” [FN]. I was using rah_repeat to iterate over the numbers 1 to 8 and pull out the value. The problem is that using rah_repeat_value inside the replacement variable converts the variable to just a string ie. not a true variable anymore.

For example, {crew<txp:rah_replacement_value} outputs '{crew1}' as a string, instead of what my outer smd_query grabbed.

[FN] Yes, I know it is bad database design to do this. It would be better to have a simpler and more flexible table with each crew member in a single record. But the problem I have is inserting multiple records into a database from a single form submission. I’m using mem_form which only allows a single record at a time and I’m not smart enough to figure out how to do a multiple insert from one submit on a form.

[EDIT]
I know I can solve my original issue by just repeating each line manually. It just doesn’t look as neat!

Last edited by aslsw66 (2011-10-18 09:07:46)

Offline

#279 2011-10-18 09:12:04

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,577
Website GitHub

Re: smd_query: Talk to the database directly via SQL

aslsw66 wrote:

For example, {crew<txp:rah_replacement_value} outputs '{crew1}' as a string, instead of what my outer smd_query grabbed.

Does it change things if you use the preparse="1" attribute? See example 9 in the help.


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

#280 2011-10-18 09:59:34

aslsw66
Member
From: Canberra, Australia
Registered: 2004-08-04
Posts: 342
Website

Re: smd_query: Talk to the database directly via SQL

Oh, that’s what it’s there for!

This looked like it would work but then broke some existing functions that did work. You see, each crew also has a ‘crew_leader’ and ‘driver’ and the display for those sits outside the rah_repeat function because they have a known, non-repeating name.

So, I separated out these into two separate queries – one for the two known names, and the other for the consecutively name crew fields. Adding preparse="1" works fine when I just want to display the results, but if I want the result to become the selected attribute for a mem_form_select then preparse stops that happening – just nothing gets passed through to the form.

Plan Z is to just run a separate query for each one.

Offline

#281 2011-10-18 10:56:38

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,577
Website GitHub

Re: smd_query: Talk to the database directly via SQL

aslsw66 wrote:

Oh, that’s what it’s there for!

:-)

This looked like it would work but then broke some existing functions that did work. You see, each crew also has a ‘crew_leader’ and ‘driver’ and the display for those sits outside the rah_repeat function

Weird. It should work, umm, I think. I just ran a (simplified) test:

<txp:variable name="tester">ID: {ID}<br />Title: {Title}<br /></txp:variable>

<txp:smd_query query="select * from textpattern where ID < 10" preparse="1">
<txp:variable name="tester" />
  CUSTOM 1: {custom_1}<br />
</txp:smd_query>

and it displayed the three values for each of the 10 articles. Where custom_1 was missing it didn’t get displayed. Not sure why it didn’t display your known-named replacements.

Can you ‘pre-compute’ the list of {crewN} items in a txp:variable and do it like I just did? Or does that defeat the purpose of doing it inside the query to begin with?


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

#282 2011-10-18 14:36:38

aslsw66
Member
From: Canberra, Australia
Registered: 2004-08-04
Posts: 342
Website

Re: smd_query: Talk to the database directly via SQL

I think the issue is getting this to work nicely across three different plugins. At the moment, this is the structure:

  • outer plugin: smd_query to get the values for crewN,
  • middle plugin: rah_repeat to interate over the values 1-8, and insert each value into the mem_form_select,
  • inner plugin: mem_form (specifically mem_form_select) to output the form for the web.

This form has two modes:

  • new – nothing is prepopulated, easy.
  • edit – current value should be assigned to the select attribute.

So, if I precompute the list of {crewN} items on the outside, I need to somehow link that to the list of rah_repeat values.

BTW, if I use preparse on the outer plugin, it returns the correct values but then the mem_form_select ignores it.

Last edited by aslsw66 (2011-10-18 14:38:58)

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 10,577
Website GitHub

Re: smd_query: Talk to the database directly via SQL

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)


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

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

aslsw66
Member
From: Canberra, Australia
Registered: 2004-08-04
Posts: 342
Website

Re: smd_query: Talk to the database directly via SQL

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!

Offline

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

kuopassa
Plugin Author
From: Porvoo, Finland
Registered: 2008-12-03
Posts: 218
Website

Re: smd_query: Talk to the database directly via SQL

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)

Offline

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

jens31
Plugin Author
From: munich / dtschermani
Registered: 2008-08-25
Posts: 183
Website

Re: smd_query: Talk to the database directly via SQL

aks_var can make simple variable calculations..

Offline

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

kuopassa
Plugin Author
From: Porvoo, Finland
Registered: 2008-12-03
Posts: 218
Website

Re: smd_query: Talk to the database directly via SQL

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?

Offline

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

jens31
Plugin Author
From: munich / dtschermani
Registered: 2008-08-25
Posts: 183
Website

Re: smd_query: Talk to the database directly via SQL

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)

Offline

Board footer

Powered by FluxBB