smd_query: Talk to the database directly via SQL

jakob · 2011-09-09 14:03:33

Two quick questions:

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.
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.

aslsw66 · 2011-10-18 09:03:04

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)

Bloke · 2011-10-18 09:12:04

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.

aslsw66 · 2011-10-18 09:59:34

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.

Bloke · 2011-10-18 10:56:38

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?

aslsw66 · 2011-10-18 14:36:38

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)

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)

Textpattern CMS

Textpattern CMS support forum

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

Board footer