etc_query: all things Textpattern

lazlo · 2015-11-22 21:37:05

But a line listing of this shows that the value is drama.
<txp:variable name=“currentcategory” value=“drama” />
<txp:variable name=“currentcategory” />

etc · 2015-11-22 21:43:04

lazlo wrote #296761:

Nope it outputs all categories. But if I type replace ‘{?currentcategory}%’ with ‘drama’ it outputs just an A-Z list of drama books.

Very weird. Have you set globals="_GET" or something like that? Try with globals="variable", thought it should work by default.

Edit: and could it be that you use some pre 4.0.8 txp version?

Last edited by etc (2015-11-22 21:44:48)

lazlo · 2015-11-22 21:47:27

Very weird. Have you set globals="_GET" or something like that? Try with globals="variable", thought it should work by default.

I am embarrassed to say I have now idea where to do that. Which means I don’t think I have ever used such a setting unless some plugin has set those.

Last edited by lazlo (2015-11-22 21:47:52)

lazlo · 2015-11-22 21:49:17

No using the latest version.
Textpattern version: 4.5.7 (r5900)
PHP version: 5.6.10

lazlo · 2015-11-22 21:51:43

Testing on MAMP Pro install on OS X if that might mean anything?

etc · 2015-11-22 21:54:42

It’s like here

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="_GET" data='<txp:variable name="alphalist" />'
...

Well, what is the output of just this:

<txp:variable name="currentcategory" value="drama" />
<txp:etc_query data='{?currentcategory}' />

lazlo · 2015-11-22 22:05:10

etc wrote #296767:

Well, what is the output of just this:

<txp:variable name="currentcategory" value="drama" />...

drama

etc · 2015-11-22 22:22:36

Fine, then

<txp:variable name="currentcategory" value="drama" />
<txp:etc_query markup="raw"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE '{?currentcategory}%' ORDER BY alpha" />

should output

SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE 'drama%' ORDER BY alpha

If so, without markup="raw" attribute it should give a AC..X list, please check.

etc · 2015-11-22 22:26:55

lazlo wrote #296766:

Testing on MAMP Pro install on OS X if that might mean anything?

This is fine, but I have noticed that some text editors on OS X tend to prettify the code way too much. Please check if straight quotes don’t become curly, and so on.

lazlo · 2015-11-23 19:40:12

Hey Oleg

Thanks for being very patient. I just wanted to report back to you what I came up with in the end.

This is the code I eventually went with, it works as expected but parts had to be hard coded.

<!-- alpha list A-Z -->
<txp:variable name="currentcategory" value='<txp:category />' />

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 = '{?currentcategory}' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/<txp:variable name="currentcategory" />/?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="_GET" data='<txp:variable name="alphalist" />'
	query="//a[text() = '{?alpha|A}']"
	replace="&=<span class='active'>{?alpha|A}</span>"
>
	{//ul}
<txp:if_category name='drama'>
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = 'drama' ORDER BY Title" />
</txp:if_category>

<txp:if_category name ='poetry'>
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = 'poetry' ORDER BY Title" />
</txp:if_category>

<txp:if_category name ='fiction'>
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = 'fiction' ORDER BY Title" />
</txp:if_category>

<txp:if_category name ='non-fiction'>
	<txp:etc_query name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = 'non-fiction' ORDER BY Title" />
</txp:if_category>


<txp:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

I (with your help) tried endlessly to get the right variable structure into the first part, only to have me find out that in the first etc_query this variable {?currentcategory} works but in the second etc_query it does not but this one does <txp:variable name=“currentcategory” /> (tested through raw output).

The following piece of code almost works, displays correct A-Z and correct category articles but when you click on the A-Z it just brings back the A articles regardless of which letter is clicked.

<!-- alpha list A-Z TEST-->
<txp:variable name="currentcategory" value='<txp:category />' />
TESTRED
<txp:variable name="currentcategory" />

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 = '{?currentcategory}' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/<txp:variable name="currentcategory" />/?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="_GET" data='<txp:variable name="alphalist" />' 
	query="//a[text() = '{?alpha|A}']"
	replace="&=<span class='active'>{?alpha|A}</span>"
>
	{//ul}
	<txp:etc_query markup="raw" name="ids" break="," globals="_GET"
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 = '<txp:variable name="currentcategory" />' ORDER BY Title" />

<txp:else />
<txp:variable name="alphalist" />
</txp:etc_query>

Hardcoding ‘<txp:variable name=“currentcategory” />’ with “drama” in the second etc_query brings back expected results so that it what I did in my current working code. Again thanks for the patience, and the wonderful plug-in and code.

Last edited by lazlo (2015-11-23 19:42:52)

etc · 2015-11-23 20:45:16

Hey Leslie, and thank you for the feedback. Sorry, I have not spot it immediately, globals attribute must be set to "variable,_GET" in the second etc_query call:

<!-- alpha list A-Z TEST-->
<txp:variable name="currentcategory" value='<txp:category />' />
TESTRED
<txp:variable name="currentcategory" />

<txp:etc_query wraptag="ul" class="pull-right breadcrumb" break="li" name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' AND Category2 LIKE '{?currentcategory}%' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/<txp:if_variable name="currentcategory" value=""><txp:else /><txp:variable name="currentcategory" />/</txp:if_variable>?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

<!-- output alphalist and get articles ids when necessary -->
<txp:etc_query globals="variable,_GET" data='<txp:variable name="alphalist" />' 
	query="//a[text() = '{?alpha|A}']"
	replace="&=<span class='active'>{?alpha|A}</span>"
>
	{//ul}
	<txp:etc_query name="ids" break=","
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'books' AND Category2 LIKE '{?currentcategory}%' ORDER BY Title" />

<txp:else />
<txp:variable name="alphalist" />
</txp:etc_query>

Last edited by etc (2015-11-23 20:52:50)

lazlo · 2015-11-23 21:22:18

etc wrote #296793:

Hey Leslie, and thank you for the feedback. Sorry, I have not spot it immediately, globals attribute must be set to "variable,_GET" in the second etc_query call:

...

Yes that works! HUZZAH!

candyman · 2015-11-24 09:12:19

Paginated alphabetic articles list: ( limiting the database call to a specific section) solved.
This would deserve an article on TXPtips :)

lazlo · 2016-02-08 02:03:01

etc wrote #296737:

Hi Leslie, let’s see. Textpattern has no native section/category url mode, so I presume you are using some rewrite rule or plugin?

Hey Oleg, I finally have an answer for this, I installed rss_unlimited_categories years ago turned it on and literally never used it for its intended purpose, so today while trying to clean up code I turned it off and broke my section/category url mode. So that is the answer. To be completely clear in case anyone else is reading this I never used a single rss_unlimited_categories tag, just turned the plugin on.

giampablo · 2016-02-25 10:07:26

Hi Oleg,
Jakob suggested me to use your plug-in in this scenario:
A small company in precious metals business wants his website to display a table with metals quotations. The values are updated every hour into a simple external TEXT file.

To be clear, I already have a working solution (see this thread ) by including a php script. But I would like to explore a more elegant and efficient way to extract values from a text file and return them in a textpattern page.
It seems that etc_query can do it, so that I could drop entirely the need of a php script.

Here is a snippet of the dati.txt file

DATA=24/02/2016
ORARIO=12:51
ORO/DOLLARI/ONCIA=1236,88
ORO/EURO/GRAMMO=36,2625
ARGENTO/DOLLARI/ONCIA=15,361
......

First, do you think that etc_query can extract the bits after = or a more structured file is needed?
Second, if a more structured file is not necessary, could you give some heads-up?
I have no idea where and how to start.

Thanks

Textpattern CMS

Textpattern CMS support forum

#271 2015-11-22 21:37:05

Re: etc_query: all things Textpattern

#272 2015-11-22 21:43:04

Re: etc_query: all things Textpattern

lazlo wrote #296761:

#273 2015-11-22 21:47:27

Re: etc_query: all things Textpattern

#274 2015-11-22 21:49:17

Re: etc_query: all things Textpattern

#275 2015-11-22 21:51:43

Re: etc_query: all things Textpattern

#276 2015-11-22 21:54:42

Re: etc_query: all things Textpattern

#277 2015-11-22 22:05:10

Re: etc_query: all things Textpattern

etc wrote #296767:

#278 2015-11-22 22:22:36

Re: etc_query: all things Textpattern

#279 2015-11-22 22:26:55

Re: etc_query: all things Textpattern

lazlo wrote #296766:

#280 2015-11-23 19:40:12

Re: etc_query: all things Textpattern

#281 2015-11-23 20:45:16

Re: etc_query: all things Textpattern

#282 2015-11-23 21:22:18

Re: etc_query: all things Textpattern

etc wrote #296793:

#283 2015-11-24 09:12:19

Re: etc_query: all things Textpattern

#284 2016-02-08 02:03:01

Re: etc_query: all things Textpattern

etc wrote #296737:

#285 2016-02-25 10:07:26

Re: etc_query: all things Textpattern

Board footer