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,152

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

Online

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

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

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

Online

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

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

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

Online

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

Els
Admin
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,152

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

Online

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

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

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

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 (2018-11-08 16:54:28)


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

Online

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

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

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

Online

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

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

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

Offline

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

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

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

Offline

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

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

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

Offline

Board footer

Powered by FluxBB