Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#291 2012-06-27 22:28:13

maruchan
Member
From: Ukiah, California
Registered: 2010-06-12
Posts: 583
Website

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 9,113
Website

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

maruchan
Member
From: Ukiah, California
Registered: 2010-06-12
Posts: 583
Website

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

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

Gocom
Plugin Author
From: Helsinki, Finland
Registered: 2006-07-14
Posts: 4,533
Website

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

johnstephens
Plugin Author
From: Woodbridge, VA
Registered: 2008-06-01
Posts: 989
Website

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

ruud
Developer emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

Board footer

Powered by FluxBB