Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

SQL queries for Textpattern

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)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#2 2014-01-10 13:29:31

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: SQL queries for Textpattern

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)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#3 2014-01-11 17:34:23

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: SQL queries for Textpattern

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)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#4 2014-01-11 18:46:46

els
Moderator
From: The Netherlands
Registered: 2004-06-06
Posts: 7,458

Re: SQL queries for Textpattern

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?)

Offline

#5 2014-01-11 20:30:54

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: SQL queries for Textpattern

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 ;)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#6 2014-01-11 20:52:15

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: SQL queries for Textpattern

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)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#7 2014-01-11 20:54:28

uli
Moderator
From: Cologne
Registered: 2006-08-15
Posts: 4,303

Re: SQL queries for Textpattern

Reset auto increment to a certain base value

Here an example for the txp_file table.

ALTER TABLE `txp_file` AUTO_INCREMENT = 98;

See also Gocom’s addendum

Please note: Backup your database before running any SQL queries.

Last edited by uli (2018-11-08 16:24:52)


In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links

Offline

#8 2014-01-12 06:14:48

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,007
Website GitHub Mastodon Twitter

Re: SQL queries for Textpattern

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>

Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#9 2014-01-12 06:17:20

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,007
Website GitHub Mastodon Twitter

Re: SQL queries for Textpattern

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>

Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#10 2014-01-12 06:20:27

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,007
Website GitHub Mastodon Twitter

Re: SQL queries for Textpattern

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

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

Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#11 2014-01-12 06:29:08

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,007
Website GitHub Mastodon Twitter

Re: SQL queries for Textpattern

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 &copy;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>

The code detects if the article was posted this year and displays &copy 2014 or if it was posted any year before then it displays that year and the current one. ie &copy; 2009 - 2014


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#12 2014-01-12 12:06:09

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: SQL queries for Textpattern

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)

Offline

Board footer

Powered by FluxBB