SQL queries for Textpattern

uli · 2014-01-10 13:27:57

While trying to set up a nice selector for smd/etc_query noone wants to worry through pages and pages of manuals on how to administer a database or create temporary tables. One often just needs an example to modify, the closer it is to TXP the better.

If you have something to share: post your snippets or links to great MySQL queries here, even those you found on the forum.

For the Textpattern sort value, you can use much more attributes (and much more powerful ones) than listed in the TXP manual. One often just doesn’t know what the SQL syntax offers to use there. If you know of any such properties post your examples here.

Last edited by Gocom (2014-01-25 15:08:45)

uli · 2014-01-10 13:29:31

Show only articles from a certain period of time

I make a start with Oleg/etc’s reply to a question in the aks_article topic: whocarez needed only articles “older than one month and not older than one year”

Oleg’s code is:

<txp:aks_article sort="custom_7 desc" wraptag="ul" break="li" limit="1" offset="1" section="artikel" form="zwei_und_drei"
    where="DATEDIFF(Posted, NOW()) BETWEEN -365 AND -31" />

(Plugin used: aks_article.)

Last edited by Gocom (2014-01-25 15:09:26)

uli · 2014-01-11 17:34:23

Random article posted within the last 365 days

The following snippet is shared by maruchan (originally found here):

<txp:smd_query query="SELECT Title, Image, url_title FROM textpattern WHERE Section = 'articles' AND Posted >= CURDATE() - INTERVAL 1 YEAR AND Status = 4 ORDER BY RAND() LIMIT 1">
  <div class="sidebar-article-image">
  <txp:images id="{Image}">
      <a href="<txp:site_url />articles/{url_title}?ref=sidebar-gallery-imglink" title="Read the article">
      <img src="<txp:site_url />timthumb.php?src=<txp:image_url />&w=260" alt="{Title} image" />
  </a>
    </txp:images>
    <h1>{Title}</h1>
    <p><a href="<txp:site_url />articles/{url_title}?ref=sidebar-gallery-textlink">Read the article now.</a></p>
</div> <!-- sidebar article image -->
</txp:smd_query>

Plugin used: smd_query

Query, formatted for better readability:

SELECT Title, Image, url_title
FROM textpattern
WHERE Section = 'articles'
AND Posted >= CURDATE() - INTERVAL 1 YEAR
AND Status = 4
ORDER BY RAND()
LIMIT 1

Last edited by Gocom (2014-01-25 15:35:02)

els · 2014-01-11 18:46:46

I appreciate this topic, very useful! Don’t you think it should be in the How Do I… forum, because it definitely is Txp related? (and maybe even made sticky?)

uli · 2014-01-11 20:30:54

You’re probably right, I’ll do that. I just felt a little disoriented: The How-to topics are now in the Archives, where it’s paradoxical to start a topic ;)

uli · 2014-01-11 20:52:15

Increase numeric values

You can do some maths in SQL. That might come in handy e.g. in the case of merging two databases, when you’ll have to adjust the article IDs.

UPDATE textpattern SET ID = ID + 150

Or, for shifting only certain blocks

UPDATE textpattern SET ID = ID + 77 WHERE ID >= 999

… where 77 is the number that you’d like to add to the IDs 999 and above.

If there are comments in the database you have to adjust, you need to do exactly the same with parentid in table txp_discuss than what you did with ID in the textpattern table, cause these values have to be identical in order to call the correct comments for their articles. Plus: You also have to add an apt value to txp_discuss > discussid.

UPDATE txp_discuss SET parentid = parentid + 150

Please note: This is not meant as a quick how-to for merging two TXP databases and it might not be everything you have to adjust. Backup your database before running any SQL queries.

Last edited by uli (2022-09-23 19:13:42)

uli · 2014-01-11 20:54:28

Reset auto increment to a certain base value

Here an example for the txp_file table.

ALTER TABLE `txp_file` AUTO_INCREMENT = 98;

Count the number of images in an image category and display it on a page or article

 <txp:php>echo safe_count('txp_image', "category = 'image_category'");</txp:php>

colak · 2014-01-12 06:17:20

Count the number of links under a particular link category and display it on a page or article

<txp:php>echo safe_count('txp_link', "category = 'link_category'");</txp:php>

colak · 2014-01-12 06:20:27

Count the number of live articles and display it on a page or article

<txp:php>echo safe_count('textpattern', 'Status = 4');</txp:php>

colak · 2014-01-12 06:29:08

Display copyright dates

This is more of a combination of php and variables rather than mysql but it might be of use to someone:)

Strictly speaking, every time a text is amended, the particular year should appear in the copyright notice. ie ©2010, 2012 for which case there is a plugin. Here is a wrong way which we use in our sites:

<txp:variable name="year" value='<txp:php>echo safe_strftime("%Y");</txp:php>' />
<txp:if_variable name="year" value='<txp:posted format="%Y" />'>
&copy; <txp:variable name="year" /> ,
<txp:else />
&copy; <txp:posted format="%Y" /> - <txp:variable name="year" /> 
</txp:if_variable>

etc · 2014-01-12 12:06:09

Section list with article counts

<txp:etc_query wraptag="ul" break="li"
	data="SELECT name, title, count
	FROM (SELECT COUNT(*) count, Section FROM textpattern WHERE Status=4 GROUP BY Section HAVING count!=0) c
	JOIN txp_section ON name=c.Section
	ORDER BY title"
>
	<a href='<txp:section name="{name?}" url="1" />'>{$htmlspecialchars({title?})}</a> [{count?}]
</txp:etc_query>

Edit: replaced count>0 with count!=0, just because txp syntax highlighting seems to consider > inside "..." as something special.

Last edited by etc (2014-01-12 21:51:48)

Textpattern CMS

Textpattern CMS support forum

#1 2014-01-10 13:27:57