Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

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

Re: etc_query: all things Textpattern

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)


etc_[ query | search | pagination | date | tree | cache ]

Offline

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

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

Re: etc_query: all things Textpattern

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:

<!-- alpha list A-Z TEST-->...

Yes that works! HUZZAH!

Offline

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

candyman
Member
From: Italy
Registered: 2006-08-08
Posts: 684

Re: etc_query: all things Textpattern

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

Offline

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

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

Re: etc_query: all things Textpattern

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.

Offline

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

giampablo
Member
From: Italy
Registered: 2008-07-17
Posts: 86
Website

Re: etc_query: all things Textpattern

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

Offline

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

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

Re: etc_query: all things Textpattern

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)


etc_[ query | search | pagination | date | tree | cache ]

Offline

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

giampablo
Member
From: Italy
Registered: 2008-07-17
Posts: 86
Website

Re: etc_query: all things Textpattern

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

Offline

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

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

Re: etc_query: all things Textpattern

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.


etc_[ query | search | pagination | date | tree | cache ]

Offline

#289 2016-04-04 01:25:13

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

Re: etc_query: all things Textpattern

Hey Oleg

Hoping you can help me out again as the <!— alpha list A-Z —> works wonderfully for the “Title” field. In my case I use it for both an Book Title List and an Author List. These articles have alternative titles that exist in a custom field, in one case it is <txp:custom_field name=“Contributor_Full_Name_Reversed” />

For example your following code works for the “Title” field

<!-- alpha list Author display A-Z-->

<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 = 'authors' ORDER BY alpha"
>
	<a href="<txp:site_url /><txp:section />/?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="," 
		data="SELECT ID FROM textpattern WHERE UPPER(SUBSTRING(Title,1,1)) = '{?alpha|A}' AND Status = 4 AND Section = 'authors' ORDER BY Title" />

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

<!-- End alpha list -->

I want to use adi_gps to search display the contents of this field <txp:custom_field name=“Contributor_Full_Name_Reversed” /> instead of title.

If my GET was now ?lastfirstalpha do I replace all instances of ?alpha in the above code with ?lastfirstalpha and how do search the custom field itself as it doesn’t seem to be as simple as replacing the “Title” field variable.

Offline

#290 2016-04-04 14:44:09

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

Re: etc_query: all things Textpattern

Hey Leslie

lazlo wrote #298483:

If my GET was now ?lastfirstalpha do I replace all instances of ?alpha in the above code with ?lastfirstalpha and how do search the custom field itself as it doesn’t seem to be as simple as replacing the “Title” field variable.

Yes, replace alpha with lastfirstalpha everywhere, and replace Title with custom_n, where n is the internal number of your Contributor_Full_Name_Reversed cf (as set in Admin/Advanced). This should give you an alphabetic list populated from Contributor_Full_Name_Reversed values.


etc_[ query | search | pagination | date | tree | cache ]

Offline

Board footer

Powered by FluxBB