etc_query: all things Textpattern

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

etc · 2016-02-25 22:54:39

Hi Giampiero,

I follow the forum, but have not much time to reply atm, sorry. I’m not sure etc_query is of much help here, but judge for yourself.

Things would be easier if your remote data were in some structured format (json, xml). Extracting from a text file is possible, of course, but depends on the data representation. You generally can not avoid some php here. We can disguise it in a txp tag (like etc_query or rah_function), but this wouldn’t simplify things much.
More anecdotally, etc_query speaks XPath language, and / in XPath has a special meaning. So ARGENTO/DOLLARI/ONCIA is an unfortunate choice as data field name.

This said, you can get rid of external php code. For simplicity, I use also etc_date, but it can be avoided. Try the following, inside some form or notextile.. block:

<!-- create a variable with json-encoded data -->

<txp:variable name="data"><txp:php>
$data = file_get_contents("http://my_example.com/dati.txt");
$array = array();
if($data) foreach(do_list($data, n) as $chunk) if($chunk) {
	@list($key, $val) = explode('=', $chunk, 2);
	$array[str_replace('/', '_', $key)] = txpspecialchars(str_replace(',', '.', $val));
}
echo json_encode($array);
</txp:php></txp:variable>

<!-- process the data, extracting and formatting needed fields -->

<txp:etc_query data='<txp:variable name="data" />' markup="json" query="." wraptag="table">
	<thead><tr><th>Date</th><th>Gold ounce</th><th>Silver ounce</th><th>and so on</th></tr></thead>
	<tbody><tr>
	<td><txp:etc_date date="{$(DATA?).str_replace(/|-|$)}" format="%A, %d %B %Y" lang="en_GB" />, {ORARIO?}</td>
	<td>$ {$(ORO_DOLLARI_ONCIA?).floatval.number_format($|2|.|,)}</td>
	<td>$ {$(ARGENTO_DOLLARI_ONCIA?).floatval.number_format($|2|.|,)}</td>
	<td>other data</td>
	</tr></tbody>
<txp:else />
	<p>No remote data.</p>
</txp:etc_query>

It should output a table with some data in it. Up to you to change the html markup and style it. If it works as you need, we can discuss a multilingual solution.

Last edited by etc (2016-02-25 23:04:16)

giampablo · 2016-02-26 12:20:23

Yes, it works also with live datas.
Of course I must change the markup and style (i.e. the date and hour is out of my table) but it does the job of extracting the data, converting in the proper format and render the txt page.

Maybe I can ask the customer to have a better representation of data. What format would be better, json or xml? And do you think we can easily add something else with etc_query (i.e. some graph) ???

Thank you for your help, by now

etc · 2016-02-26 14:27:44

giampablo wrote #298027:

Maybe I can ask the customer to have a better representation of data. What format would be better, json or xml?

XML is more structured, but JSON could be easier to process here. And if you have choice, ask them to keep to some php standards:

3.14 rather than 3,14 in floats
26-02-2016 rather than 26/02/2016 in dates
ORO_DOLLARI_ONCIA rather than ORO/DOLLARI/ONCIA

You will then not need the first part of the code above.

And do you think we can easily add something else with etc_query (i.e. some graph) ???

Graphs are mainly made with Javascript (e.g. flot), you will only use etc_query to feed them data. And yes, it’s quite easy.

Textpattern CMS

Textpattern CMS support forum

#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

#286 2016-02-25 22:54:39

Re: etc_query: all things Textpattern

#287 2016-02-26 12:20:23

Re: etc_query: all things Textpattern

#288 2016-02-26 14:27:44

Re: etc_query: all things Textpattern

giampablo wrote #298027:

Board footer