smd_query: Talk to the database directly via SQL

kuopassa · 2011-12-19 11:44:12

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. :-)

txpdevcoder · 2012-06-15 09:54:15

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)

maruchan · 2012-06-27 22:28:13

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)

Bloke · 2012-06-27 22:34:38

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)

maruchan · 2012-06-27 23:09:33

Works great, Stef. Thanks.

piyashrija · 2012-07-06 00:00:53

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)

uli · 2012-07-06 12:16:35

piyashrija, are you actually looking for the time="any" attribute/value pair in article_custom?

piyashrija · 2012-07-07 07:09:38

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

piyashrija · 2012-07-07 09:29:00

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)

Gocom · 2012-07-07 14:17:11

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)

johnstephens · 2014-10-10 15:26:07

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)

ruud · 2014-10-11 11:01:57

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>")'

Textpattern CMS

Textpattern CMS support forum

#289 2011-12-19 11:44:12

Re: smd_query: Talk to the database directly via SQL

#290 2012-06-15 09:54:15

Re: smd_query: Talk to the database directly via SQL

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

Re: smd_query: Talk to the database directly via SQL

#292 2012-06-27 22:34:38

Re: smd_query: Talk to the database directly via SQL

#293 2012-06-27 23:09:33

Re: smd_query: Talk to the database directly via SQL

#294 2012-07-06 00:00:53

Re: smd_query: Talk to the database directly via SQL

#295 2012-07-06 12:16:35

Re: smd_query: Talk to the database directly via SQL

#296 2012-07-07 07:09:38

Re: smd_query: Talk to the database directly via SQL

#297 2012-07-07 09:29:00

Re: smd_query: Talk to the database directly via SQL

#298 2012-07-07 14:17:11

Re: smd_query: Talk to the database directly via SQL

#299 2014-10-10 15:26:07

Re: smd_query: Talk to the database directly via SQL

#300 2014-10-11 11:01:57

Re: smd_query: Talk to the database directly via SQL

Board footer