Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#289 2011-12-19 11:44:12
Re: smd_query: Talk to the database directly via SQL
Thanks again for taking the time to answer, jens31. The code you offered is indeed pointing towards the right direction. I think the best solution is to somehow get out of the tag soup and use raw PHP as that offers better solutions for conditional statements. I’ll keep on experimenting. :-)
Offline
#290 2012-06-15 09:54:15
- txpdevcoder
- Member
- Registered: 2012-06-07
- Posts: 58
Re: smd_query: Talk to the database directly via SQL
hi bloke / everyone.
Nice plugin. Ive got a tricky one and i think smd_query can help, but im tottally lost.
Im trying to create a collapasable tree of all the articles on the site grouped into year and month, as nested lists. Ive got this far, but beside the year and the month, i want to put an article count in brackets beside the month and the year.
original forum thread here, with the code im using already to list it out. i just need the article count adding in:
Forum thread for collapsable accordian
How do i throw smd-query into that to get the article count?
Cheers!
-J
Last edited by txpdevcoder (2012-06-15 09:55:24)
Offline
#291 2012-06-27 22:28:13
Re: smd_query: Talk to the database directly via SQL
Stef: Thank you for this very useful plugin. I’m trying to use {prefix.column} and it’s not working—is this available some other way? Example:
<txp:smd_query break="li" wraptag="ul" query="select f.id,f.filename,f.title,c.title from txp_file f left outer join txp_category c on f.category=c.name where c.name like 'fun-downloads%'">
<a href="<txp:site_url />file_download/{id}/{filename}">{f.title}</a> {c.title}
<txp:else />
<txp:variable name="no-results-message" />
</txp:smd_query>
<!-- end test -->
This outputs “{f.title}” and “{c.title}” on the page literally.
Thanks!
Last edited by maruchan (2012-06-27 22:28:59)
Offline
#292 2012-06-27 22:34:38
Re: smd_query: Talk to the database directly via SQL
maruchan
Try:
select f.id,f.filename,f.title as file_title,c.title as cat_title from...
and use {file_title}
and {cat_title}
instead.
Last edited by Bloke (2012-06-27 22:35:16)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
#293 2012-06-27 23:09:33
Re: smd_query: Talk to the database directly via SQL
Works great, Stef. Thanks.
Offline
#294 2012-07-06 00:00:53
- piyashrija
- Member
- Registered: 2012-05-17
- Posts: 30
Re: smd_query: Talk to the database directly via SQL
Hi
I am not able to retrieve future article with query below:
(Posted < NOW() AND Expires >= NOW())
: this part works fine
(Posted > NOW()
this doesn’t work why
<txp:smd_query query = "SELECT ID FROM textpattern WHERE (Posted > NOW() OR (Posted < NOW() AND Expires >= NOW())) AND Section = 'event'">
Thanks
Stef: Edited for code display
Last edited by Bloke (2012-07-06 00:09:25)
Offline
#295 2012-07-06 12:16:35
- uli
- Moderator
- From: Cologne
- Registered: 2006-08-15
- Posts: 4,310
Re: smd_query: Talk to the database directly via SQL
piyashrija, are you actually looking for the time="any"
attribute/value pair in article_custom
?
In bad weather I never leave home without wet_plugout, smd_where_used and adi_form_links
Offline
#296 2012-07-07 07:09:38
- piyashrija
- Member
- Registered: 2012-05-17
- Posts: 30
Re: smd_query: Talk to the database directly via SQL
Thanks for reply
I dont want all the article to get display. I hope “any” will display all article including past one as well.
I have date range like 06-07-2012 ::06-09-2012 or 06-08-2012::06-09:2012.
so just want to display all the article that is posted relative to today and still active.
thanks
Offline
#297 2012-07-07 09:29:00
- piyashrija
- Member
- Registered: 2012-05-17
- Posts: 30
Re: smd_query: Talk to the database directly via SQL
Sorry about the stupid question
<txp:smd_query query = "SELECT ID FROM textpattern WHERE (Posted > NOW() OR (Posted < NOW() AND Expires >= NOW())) AND Section = 'event'">
<txp:article id="{ID}" time ="any" >
will solve the problem..
I am new to textpattern and thought if i pass future id in article tag it should display future article as well.
{Edited to add bc.
for better code display. – Uli}
Last edited by uli (2012-07-07 11:37:23)
Offline
#298 2012-07-07 14:17:11
Re: smd_query: Talk to the database directly via SQL
piyashrija wrote:
I am new to textpattern and thought if i pass future id in article tag it should display future article as well.
Article tag is context sensitive, meaning that it show articles paged on given context, a requested page. It doesn’t have id
attribute. Please see article_custom instead.
As far as that particular code sample goes, smd_query isn’t needed for that. That’s equivalent to, and requires no more than:
<txp:article_custom time="any" section="event">
<txp:title />
</txp:article_custom>
Which is what Uli was referring to in his reply to you.
Last edited by Gocom (2012-07-07 14:18:50)
Offline
#299 2014-10-10 15:26:07
Re: smd_query: Talk to the database directly via SQL
Question about sanitizing user input
I operate a site for an academic journal, and one feature request I’m working on is to improve the site search so that searching for an author’s name turns up useful results. Because an individual article may have multiple authors, I use a custom field to tie each article to the relevant author info wrangled by smd_bio, and searching for an author name doesn’t usually turn up relevant articles in the anywhere in first page of search results, even with wet_haystack running.
So here’s my idea: Compare the search query against names in the author table, and if there are hits, list the links to those author profile pages, which list all of an author’s articles.
Here’s the code I used, which seems to work:
<txp:smd_query
break="li"
column="name, RealName"
limit="3"
table="txp_users"
where='RealName REGEXP "^(.*)<txp:page_url type="q"/>(.*)$"'
wraptag="ul">
<a href='/authors/{name}'>Articles by {RealName}</a>
</txp:smd_query>
There are several things I’d like to improve about it, but first I want to verify that this method isn’t a reckless fiasco: The SQL query takes user input. Is there anything I need to do, and I can do, to sanitize the input and make sure the query is secure?
This query will only match names in the author table if they contain the exact string in the search query, so searching for “Dan Tucker” or “Daniel Tucker” won’t return the profile for “Daniel B. Tucker”. One of the refinements I have in mind is running the search query through rah_replace before piping it into the query, to add a (.*)
between distinct search keywords. That would allow both of those searches to return the right profile.
Another refinement I have in mind is to only run the author table query if there are multiple search keywords: I don’t want to try to guess an author name if someone searches for “Dan”, or other innocuous words that might be part of a name—“And”, or “The”, etc.
I only mention the refinements I’m thinking about in case they might bear on the security question.
Thanks in advance! I’m grateful for any wisdom you might offer!
Last edited by johnstephens (2014-10-10 15:26:55)
Offline
#300 2014-10-11 11:01:57
Re: smd_query: Talk to the database directly via SQL
where='RealName REGEXP "^(.*)<txp:page_url type="q"/>(.*)$"'
Is the same as:
where='RealName REGEXP "<txp:page_url type="q"/>"'
Can probably be done faster as:
where='INSTR(RealName, "<txp:page_url type="q"/>")'
Which is the same as:
where='INSTR(RealName, "<txp:php>page_url(array("type" => "q")</txp:php>")'
But needs SQL escaping:
where='INSTR(RealName, "<txp:php>doSlash(page_url(array("type" => "q"))</txp:php>")'
Offline