Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2019-03-15 11:19:58

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 188
Website

Extract h2 and h3 from database

Hello and bravo for the new Texpattern 4.7.3 :)

I’d like to build an oveview of all the articles I’ve written in my website.
I am wondering how I could extract directly form the Body field in the textpattern table in the DB all the h2. and h3.

Since there is not REGEXP_SUBSTR function in MySQL, what would be the best way to do it, as a MySQL query, or any other way.

The answer my be in the depths of this forum… I confess that did very little search 🤦♀️

Offline

#2 2019-03-15 11:39:14

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

Re: Extract h2 and h3 from database

FWIW, there is REGEXP_SUBSTR in MySQL 8, but it returns only one match.


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

Offline

#3 2019-03-15 12:22:16

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 188
Website

Re: Extract h2 and h3 from database

Yeah, I saw that. But:
  1. I need all the occurences
  2. My provider runs an older version

I looked at etc_query but can’t figure out if this would be a good use.

Offline

#4 2019-03-15 12:39:47

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

Re: Extract h2 and h3 from database

planeth wrote #317069:

I looked at etc_query but can’t figure out if this would be a good use.

Ah, if you are not limited to MySQL, this should do:

<txp:article_custom>
<txp:etc_query data='<txp:body />' query="//h2|//h3" />
</txp:article_custom>

It extracts textiled headers, but they rarely contain HTML tags.


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

Offline

#5 2019-03-15 12:56:37

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 188
Website

Re: Extract h2 and h3 from database

Oh, great :) Thanks
I could put this in an external form (not really sure how this goes).

Offline

#6 2019-03-15 13:06:13

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

Re: Extract h2 and h3 from database

planeth wrote #317071:

I could put this in an external form (not really sure how this goes).

Quite easily. First enable Advanced options on Admin prefs tab. Then go there and create a mediatyped custom form type, say

[txt]
title="Plain text"
mediatype="text/plain"

Next, create a txt-type form, say headers:

<txp:article_custom>
<txp:etc_query data='<txp:body />' query="//h2|//h3" />
</txp:article_custom>

and access it via ?f=headers URL.

And if you prefer to output it in Textile format, this should do:

<txp:article_custom>
<txp:etc_query data='<txp:body />' query="//h2|//h3">
{#}. {?}
</txp:etc_query>
</txp:article_custom>

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

Offline

#7 2019-03-15 13:30:58

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 188
Website

Re: Extract h2 and h3 from database

Woha, don’t even have to think through all this. My answer is serve on a platter !
Thanks again.
This going to be much easier to re model the structure of my website.

Offline

Board footer

Powered by FluxBB