Textpattern CMS support forum

You are not logged in. Register | Login | Help

#251 2015-11-05 22:16:21

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

Hi Oleg (SOLVED)

I am implementing your wonderful A-Z filter / paginated A-Z article list but I am having trouble limiting the database call to specific textpattern section.

Is there a way to adjust this call to delimit textpattern sections?
I have a section called “Books” to be specific.

<txp:etc_query wraptag="nav" break=" | " name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 ORDER BY alpha"
>
	<a href="?alpha={alpha?}">{alpha?}</a>
</txp:etc_query>

I have tried various iterations on the call by adding AND Section = ‘books’ but with no success.

I didn’t realize there were two calls that needed to be changed so the following is the solution.

NEW Code



<!-- alpha list -->
<txp:etc_query wraptag="nav" break=" | " name="alphalist"
	data="SELECT DISTINCT UPPER(SUBSTRING(Title,1,1)) AS alpha FROM textpattern WHERE Status = 4 AND Section = 'books' ORDER BY alpha"
>
	<a href="?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>"
>
	{//nav}
	<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' ORDER BY Title" />
<txp:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

<txp:if_variable name="ids">
	<!-- Pagination bar -->
	<txp:etc_query name="pages" data="{?ids|1|substr_count($|,).+1./2.ceil}" />
	<txp:etc_pagination pgcounter="page" link="Page {*}" wraptag="nav" break=" | "
		pages='<txp:variable name="pages" />'  />
	<!-- articles output -->
	<txp:article_custom id='<txp:variable name="ids" />' limit="2"
		offset='<txp:etc_offset pgcounter="page" pageby="2" />' />
</txp:if_variable>

Last edited by lazlo (2015-11-05 23:16:55)

Offline

#252 2015-11-06 09:43:19

etc
Developer
Registered: 2010-11-11
Posts: 3,396
Website

Re: etc_query: all things Textpattern

Hi Leslie, glad you have solved it. For multiple sections, you can add

Section IN('books', 'news')

to the appropriate WHERE clauses. And if you have expired/future articles, you may need also

 (NOW() <= Expires OR Expires = '0000-00-00') AND Posted <= NOW()

Last edited by etc (2015-11-06 10:41:07)

Offline

#253 2015-11-21 02:33:41

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

Hey Oleg

So far the A-Z works wonderfully and because it worked so well I was asked if it could parse for current category.
Category2 in my case has ‘‘fiction’, ‘drama’

I got as far as this.

<!-- alpha list display A-Z-->
<txp:variable name="current_category" value="<txp:category />" />
<txp:variable name="current_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 = 'drama' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/<txp:category />/?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 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:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

<txp:hide>
<txp:if_variable name="ids">
	<!-- Pagination bar -->
	<txp:etc_query name="pages" data="{?ids|1|substr_count($|,).+1./2.ceil}" />
	<txp:etc_pagination pgcounter="page" link="Page {*}" wraptag="nav" break=" | "
		pages='<txp:variable name="pages" />'  />

        <!-- articles output -->
	<txp:article_custom id='<txp:variable name="ids" />' limit="2"
		offset='<txp:etc_offset pgcounter="page" pageby="2" />' />

</txp:if_variable>
</txp:hide>

<!-- End alpha list -->

First I couldn’t get the the <txp:variable name=“current_category” /> to work. So I tried just for the specific case ‘drama’ which still does not work the way I want.
Current code above does this:

URL…textpattern455/books/
(with code directly above shows “A-Z” and Category drama [which makes sense] but what I want is this to show all books)

URL…textpattern455/books/?alpha=B
(with code directly above shows “B” drama but what I want is this to show all “B” books)

URL…textpattern455/books/drama
(shows entire list of drama but what I want is “A-Z” drama )

URL…textpattern455/books/drama/?alpha=B
(shows entire list of drama but what I want is “B” drama)

My original code prior (listed in message above this one) was wrapped in this:
<txp:if_article_list>
<txp:if_section name=“books”>
<txp:if_category name=” “>
<txp:output_form form=“alpha_list_display” /> (links to the code in the previous message above)
</txp:if_category>
</txp:if_section>
</txp:article_list>

Now it is just this:
<txp:if_article_list>
<txp:if_section name=“books”>
<txp:output_form form=“alpha_list_display” /> (links to the code in this message)
</txp:if_section>
</txp:article_list>

I am wondering if I have to have a separate “category_alpha_list_display” and I would have to call it at this level?

<txp:if_article_list>
<txp:if_section name=“books”>
<txp:if_category name=” “>
<txp:output_form form=“alpha_list_display” />
<txp:else>
<txp:output_form form=“category_alpha_list_display” />
</txp:if_category>
</txp:if_section>
</txp:article_list>

So can I get a query that works for this “URL…textpattern455/books/drama/?alpha=B “ and does it need to be separate from the original query that worked for “URL…textpattern455/books/”

Regards
Leslie

Last edited by lazlo (2015-11-21 02:35:34)

Offline

#254 2015-11-21 14:12:17

etc
Developer
Registered: 2010-11-11
Posts: 3,396
Website

Re: etc_query: all things Textpattern

Hi Leslie, let’s see. Textpattern has no native section/category url mode, so I presume you are using some rewrite rule or plugin? Some of them (like gbp_permanent_links or smd_tags) change internal Textpattern variables and potentially can perturb others work, but let’s assume they play well here. So, <txp:category /> on books/drama page should output “drama” (please check). If so, use single quotes in your <txp:variable /> definition:

<txp:variable name="current_category" value='<txp:category />' />
<txp:variable name="current_category" />

Now, replace Category2 = 'drama' with

Category2 LIKE '<txp:variable name="current_category" />%'

in both db queries. That’s a bit fragile, since it will also match dramatic+art on books/drama page, but ok for testing. Please report back whether it fixes some points or not, and we will see the rest.

Offline

#255 2015-11-21 22:29:46

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

Hey Oleg

I can’t get a <txp:variable /> to work in the code. The best I have managed is this output, this case <txp:variable name=“currentcategory” /> is “fiction” [Category2 LIKE “fiction%”]:

<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 "fiction%" ORDER BY alpha"

Which is produced from this code:

<txp:variable name="currentcategory" value='<txp:category />' />
<txp:variable name="currentcategory" />
<txp:variable name="ids" />

<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 "<txp:variable name='currentcategory' />%" ORDER BY alpha"

I think I have tried every variation on single and double quotes, and this is the only version I have come up with that actually makes an etc_query call. They either threw back syntax errors or failed silently with no etc_query call happening at all.

If I don’t use double quotes and use single quotes like this

Category2 LIKE '<txp:variable name='currentcategory' />%'

I get this error:

Tag error: <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 '<txp:variable name='currentcategory' />%' ORDER BY alpha"

and this version fails silently without etc_query even being called.

Category2 LIKE '<txp:variable name="currentcategory" />%'

Solving this variable call sure would be nice as I could hard code the category but defeats the purpose of a dynamic CMS.

Offline

#256 2015-11-21 23:28:04

etc
Developer
Registered: 2010-11-11
Posts: 3,396
Website

Re: etc_query: all things Textpattern

Right, sorry, try to nest duplicated single quotes:

<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 ''<txp:variable name="currentcategory" />%'' ORDER BY alpha'>
...

Offline

#257 2015-11-21 23:55:23

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

Unfortunately duplicate single quotes fail silently.

<txp:output_form form="alpha_list_display" />
				[SQL (0.000259876251221): select Form from txp_form where name='alpha_list_display']
				[Form: alpha_list_display]
				<txp:variable name="currentcategory" value='<txp:category />' />
					[attribute 'value']
					<txp:category />
					[/attribute]
				<txp:variable name="currentcategory" />
				<txp:variable name="ids" />
					[<txp:variable>: Unknown variable 'ids']
				<txp:variable name="currentcategory" />
[txp:etc_query SHOULD APPEAR HERE but NOTHING everything after this point is dumped]
				<txp:site_url />
				<txp:section />

Offline

#258 2015-11-22 02:01:58

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

Oleg

Even if we can’t solve the variable issue. I thought I would point out that this works
URL …textpattern455/books/drama/?alpha=E for me when hardcoded and beautifully if I might add.

I am not using a plugin to get there, unless it is an unintended consequence (I don’t think so) and I don’t think I am using a rewrite rule, see below. But to be frank I have been piecing/adding/improving this code together for years I forget how some parts happened. At one point I noticed that instead of coding for this “http://textpattern.site/section/?c=sitemaps” I could code for this “http://textpattern.site/section/sitemaps” and it passed the category through.

I had just thought it was a new feature.

In the same way this works on textpattern tip site:
http://textpattern.tips/category/tutorials = http://textpattern.tips/?c=tutorials
Probably only when your preference for permanent link mode is /section/title (maybe/maybe not/haven’t tried)

Here is my .htaccess

#DirectoryIndex index.php index.html

#Options +FollowSymLinks
#Options -Indexes
#ErrorDocument 403 default

<IfModule mod_rewrite.c>
	RewriteEngine On
	#RewriteBase /relative/web/path/

	RewriteCond %{REQUEST_FILENAME} -f [OR]
	RewriteCond %{REQUEST_FILENAME} -d
	RewriteRule ^(.+) - [PT,L]

	RewriteCond %{REQUEST_URI} !=/favicon.ico
	RewriteRule ^(.*) index.php

	RewriteCond %{HTTP:Authorization}  !^$
	RewriteRule .* - [E=REMOTE_USER:%{HTTP:Authorization}]
</IfModule>

#php_value register_globals 0

# SVG
AddType image/svg+xml  svg svgz
AddEncoding gzip       svgz

Last edited by lazlo (2015-11-22 02:03:43)

Offline

#259 2015-11-22 10:39:13

etc
Developer
Registered: 2010-11-11
Posts: 3,396
Website

Re: etc_query: all things Textpattern

lazlo wrote #296744:

URL …textpattern455/books/drama/?alpha=E for me when hardcoded and beautifully if I might add.

In the same way this works on textpattern tip site:
http://textpattern.tips/category/tutorials = http://textpattern.tips/?c=tutorials

It’s different: category has a special meaning (in English) for Textpattern, it’s not a section name. If you go to www.textpattern.tips/articles/?c=css, you will get all CSS tips from articles section. But www.textpattern.tips/articles/css will output all articles.

Back to variables, could you post the relevant code block? Looks like a syntax error somewhere.

Offline

#260 2015-11-22 17:59:56

lazlo
Member
Registered: 2004-02-24
Posts: 102

Re: etc_query: all things Textpattern

The code below brings back this error:

textpattern/publish.php:551 trigger_error()
index.php:83 textpattern()
<!-- alpha list display A-Z-->
<txp:variable name="currentcategory" value='<txp:category />' />
<txp:variable name="currentcategory" />
<txp:variable name="ids" />

<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''<txp:variable name="current category'' />" 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 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 LIKE''<txp:variable name="currentcategory" />'' ORDER BY Title" />
<txp:else />
	<txp:variable name="alphalist" />
</txp:etc_query>

And the relevant mysql from page source above

Offline

Board footer

Powered by FluxBB