Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#13 2014-01-12 16:05:28
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,310
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
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,310
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
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
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
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
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?
Offline
#20 2014-01-26 16:05:58
- els
- Moderator
- 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
Re: SQL queries for Textpattern
Gocom wrote #278489: I personally unfortunately don’t see much value in this thread as its now.
LOL, why would you? You’re obviously not taking your first steps in SQL queries, so policing newbie’s correct usage of “mySQL” and “SQL” is a bit silly don’t you think?
I love sharing queries and have learned a lot by just seeing how people construct them. I’ve just refrained from sharing mine on this thread because most of my gems, like “show me the ballers” are specific to my custom menagerie of plugins.
Offline
#22 2014-01-27 11:57:18
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,310
Re: SQL queries for Textpattern
renaming this thread Useless SQL queries
correct usage of “mySQL” and “SQL”
Point taken, topic renamed, forum saved. Greets, Bert Macklin
mrdale wrote #278512:
I […] have learned a lot by just seeing how people construct them.
Exactly that was the reason for opening the topic. And by having SQL in the familiar TXP context there’s one unknown less in the equation: We know the column names.
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
Re: SQL queries for Textpattern
That’s what you hang on and ignore the point that this topic would better serve on TXPTips, on a site mean for tips? Legit.
You sharing copy+paste snippets doesn’t teach anyone. If yourself aren’t well educated enough to explain your snippet, at least use proper terminology so that people interested can search the reasons how and why the snippet of yours works. Lack of explaining and teaching is why I see this thread as invaluable. Not because I don’t need these snippets. Its why I want these snippets on TXPTips as longer guides, not scattered small snippets buried on this thread.
Last edited by Gocom (2014-02-08 15:22:55)
Offline
Re: SQL queries for Textpattern
I guess that there’s a difference between txptips and here. When posting snippets of code in this forum we post them so as to hopefully get some feedback which will help in their development. That is how I saw the “Examples” forum and this is how I see this thread.
I agree that final snippets (with explanations) should appear in txptips which it should function as our central resource. Until those snippets become final though, this forum is the best tool we have to have our ideas scrutinised.
Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.
Offline