Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 6,911
Website

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 ©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.org | hblack.net | LABS | State Machines | NeMe @ github

Offline

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

etc
Developer
Registered: 2010-11-11
Posts: 2,949
Website

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)


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

Offline

#13 2014-01-12 16:05:28

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

Re: SQL queries for Textpattern

Yiannis and Oleg, thank you for your contibutions and seizing on the idea.


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

Offline

#14 2014-01-13 10:22:27

gaekwad
Member
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 2,379

Re: SQL queries for Textpattern

Global search + replace

I’ve used this a lot to change CSS classes when I have to make framework-related changes:

UPDATE textpattern SET Body = replace(Body, 'oldString', 'newString');
UPDATE textpattern SET Body_html = replace(Body_html, 'oldString', 'newString');

Can also be used in excerpts (or anywhere in the database, actually):

UPDATE textpattern SET Excerpt = replace(Excerpt, 'oldString', 'newString');
UPDATE textpattern SET Excerpt_html = replace(Excerpt_html, 'oldString', 'newString');

Usual warning: make a backup before you start making changes.

Last edited by gaekwad (2015-03-25 10:37:38)

Offline

#15 2014-01-13 15:53:32

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

Re: SQL queries for Textpattern

Thanks, Pete, much appreciated. I’ve used it once for ironing out umlaut glitches.


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

Offline

#16 2014-01-18 11:48:46

gaekwad
Member
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 2,379

Re: SQL queries for Textpattern

Globally changing article IDs

From a recent thread on Google+, here’s a way of using the MySQL concat function to prepend a number to the article ID:

update textpattern set ID = concat('9', ID);

The 9 could be any number, and the one I used in the example. For instance, this would change a series of article IDs from 1,2,3,4,5,6,7,8,9,10 to 91,92,93,94,95,96,97,98,99,910. Note this is prepending (putting a number before the start of the ID) and not incrementing, so the numeric continuity is gone.

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

Offline

#17 2014-01-25 15:21:44

Gocom
Plugin Author
Registered: 2006-07-14
Posts: 4,524
Website

Re: SQL queries for Textpattern

I’ve modified some of the posts to use proper terminology and naming. The database management system is called MySQL; the language you speak of is SQL and some of you are using MySQL extensions on top of standard SQL. What you might want to make clear is that some of these queries will perform pretty badly; like for instance all those involving rand().

That said, you might want to change the title to something different since these snippets are starting to have nothing to do with SQL. Some are even using plugins. My suggestion would be to unstick these thread and post these tips on TXP Tips. I personally unfortunately don’t see much value in this thread as its now.

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

Offline

#18 2014-01-25 15:42:48

Gocom
Plugin Author
Registered: 2006-07-14
Posts: 4,524
Website

Re: SQL queries for Textpattern

uli wrote #278061:

Reset auto increment to a certain base value

Here an example for the txp_file table.

ALTER TABLE `txp_file` AUTO_INCREMENT = 98;...

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

Please note that the auto-increment ID can not be equal or less than the highest existing ID in the table. It must be n + y, where n is the highest ID in the table and y is between n + 1 and 2 32 – (n+1)

Offline

#19 2014-01-26 10:11:59

etc
Developer
Registered: 2010-11-11
Posts: 2,949
Website

Re: SQL queries for Textpattern

Gocom wrote #278489:

you might want to change the title to something different since these snippets are starting to have nothing to do with SQL. Some are even using plugins.

Given that core tags do not accept SQL queries as attributes, I don’t see how to avoid plugins… would renaming this thread Useless SQL queries be an acceptable compromise?


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

Offline

#20 2014-01-26 16:05:58

Els
Admin
From: The Netherlands
Registered: 2004-06-06
Posts: 7,458

Re: SQL queries for Textpattern

etc wrote #278497:

would renaming this thread Useless SQL queries be an acceptable compromise?

Lol :)

Offline

Board footer

Powered by FluxBB