Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#21 2014-01-27 04:32:05
- mrdale
- Member
- From: Walla Walla
- Registered: 2004-11-19
- Posts: 2,212
- Website
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,236
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
#23 2014-02-08 15:10:24
- Gocom
- Plugin Author
- From: Helsinki, Finland
- Registered: 2006-07-14
- Posts: 4,533
- Website
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
#24 2014-02-08 17:15:50
- colak
- Admin
- From: Cyprus
- Registered: 2004-11-20
- Posts: 8,467
- Website
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.org | hblack.net | State Machines | NeMe @ github
I do my best editing after I click on the submit button.
Offline
#25 2014-02-13 07:41:20
- colak
- Admin
- From: Cyprus
- Registered: 2004-11-20
- Posts: 8,467
- Website
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 | State Machines | NeMe @ github
I do my best editing after I click on the submit button.
Offline
#26 2014-02-13 08:35:05
- Dragondz
- Moderator
- From: Algérie
- Registered: 2005-06-12
- Posts: 1,383
- Website
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.
Offline
#27 2014-10-02 16:36:36
- jakob
- Admin
- From: Germany
- Registered: 2005-01-20
- Posts: 4,035
- 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-item
is a normal txp form to display the event details using regular textpattern tags (the smd_query attributepopulate="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
Offline
#28 2016-05-27 14:11:07
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,236
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
Offline