Textpattern CMS

#25 2014-02-13 07:41:20

colak

Admin

From: Cyprus

Registered: 2004-11-20

Posts: 8,954

Website GitHub Twitter

Re: SQL queries for Textpattern

Here’s a simple mysql to change the password of a user. It’s handy when the server does not send emails to new users

update txp_users set pass=password(lower('pass')) where name='user';

---

Yiannis
——————————
neme.org | hblack.net | EMAP | A Sea change | NeMe @ github
I do my best editing after I click on the submit button.

#26 2014-02-13 08:35:05

Dragondz

Moderator

From: Algérie

Registered: 2005-06-12

Posts: 1,507

Website GitHub Twitter

Re: SQL queries for Textpattern

colak wrote #278897:

Here’s a simple mysql to change the password of a user. It’s handy when the server does not send emails to new users

update txp_users set pass=password(lower('pass')) where name='user';

There is also rah_change _password that can do that.

---

Conception site web Algerie | Livre Algerie | Construction Piscine Algerie

#27 2014-10-02 16:36:36

jakob

Admin

From: Germany

Registered: 2005-01-20

Posts: 4,431

Website

Re: SQL queries for Textpattern

I found this thread useful so I thought I’d add to it. The use case is a simple calendar of events page with start and end dates stored in custom fields rather than using txp’s own posted/expires fields:

Show only articles newer / older than the current date using dates stored in custom_fields

The setup is as follows:

• uses Stef’s smd_query plugin
• custom field custom_18 is the event’s start date in format YYYY-MM-DD (this date format sorts well)
• custom field custom_19 is the event’s end date in format YYYY-MM-DD (with glz_custom_fields, you can set the date picker to use this date format)
• event-itemis a normal txp form to display the event details using regular textpattern tags (the smd_query attribute populate="article" makes that possible – see note in example 13 in the smd_query docs).
• shows both sticky and live articles

<txp:smd_query query="SELECT *,
     unix_timestamp(Posted) as uPosted,
     unix_timestamp(LastMod) as uLastMod,
     unix_timestamp(Expires) as uExpires
     FROM textpattern 
     WHERE Status IN (4,5)
     AND section = 'calendar'
     AND custom_19 >= CURDATE()
     ORDER BY custom_18 ASC
     LIMIT 5"
     wraptag="" break="" form="event_item"
     populate="article" />

This can easily be adapted for an archive page of past events by reversing the display order and changing “greater than or equal” to “less than”:

<txp:smd_query query="SELECT *,
     unix_timestamp(Posted) as uPosted,
     unix_timestamp(LastMod) as uLastMod,
     unix_timestamp(Expires) as uExpires
     FROM textpattern 
     WHERE Status IN (4,5)
     AND section = 'calendar'
     AND custom_19 < CURDATE()
     ORDER BY custom_18 DESC
     LIMIT 5"
     wraptag="" break="" form="event_item"
     populate="article" />

PS: Corrections welcome if I’ve overlooked something.

---

TXP Builders – finely-crafted code, design and txp

#28 2016-05-27 14:11:07

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,292

Re: SQL queries for Textpattern

Using image names as alt texts, removing file name extensions

In case you have neatly readable image names when you upload images to Textpattern, you can save the labour of copying their names over to the alternative text field by executing the following SQL statement:

UPDATE txp_image SET alt = name,
alt = 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
alt, '.jpg', '')
,'.jpeg','')
,'.png','')
,'.gif','')
,'_',' ')
WHERE alt = '';

The first line takes the DB entry from the name field and copies it over to the alt field, the following four, nested, REPLACE steps each takes one of the pixel formats’ file extensions valid in Textpattern and removes them from the alt field by replacing them with nothing (''). The last REPLACE supersedes understrikes you might have used with spaces. Finally, WHERE alt = '' shelters alt fields that already have entries.

Note: Although this should not overwrite entries you might already have in the alt fields you’d better backup your database before!

In case you know something more readable to set up than these repeated, nested, statements, please post here.

Last edited by uli (2016-05-27 14:38:58)

---

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

#29 2022-08-11 18:25:55

uli

Moderator

From: Cologne

Registered: 2006-08-15

Posts: 4,292

Re: SQL queries for Textpattern

Increase numeric values 2

For those with bookmarks leading here:
This post has been merged with Increase numeric values 1

Last edited by uli (2022-09-23 19:18:39)

---

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