escaping '

testdeputy · 2011-09-05 20:00:36

i would like to customize my sortorder: ordering without, for example, starting The, De, Die, Der, etc.
Analog this tip (maybe old but still relevant..) I tried
<txp:article sort="replace(Title,'The ',''),Title" />

However, the ‘ is replaced (by Txp doSlash = mysql_real_escape_string, which is perfectly safe!).
But, how do I escape this, while writing my tag?

[edit] textile formatting

Last edited by testdeputy (2011-09-06 13:50:03)

els · 2011-09-05 20:40:15

If you don’t mind using a plugin, soo_article_filter will do what you want.

testdeputy · 2011-09-06 11:19:15

Thanks for suggesting, but the plugin soo_article_filter will not suffice:
“main issue is that if the page context is search results (i.e., the q query parameter is set), the tag will simply parse its contents and return them as is”.
My goal is, amongst other, ordering a search. The plugin will do nothing when a search is performed.

[edit] tried this a bit, also needs an xtra custom field … bummer

Remains the question: how to escape ‘?
I guess the answer is: can’t be done.

Last edited by testdeputy (2011-09-06 13:51:13)

uli · 2011-09-06 13:23:50

Your SQL probably worked if you’d use ’ or a ‘ in your titles, which – on top – is typographically correct. Is that enticing enough?

testdeputy · 2011-09-06 13:47:08

My working sql looks like ORDER BY REPLACE(Title,'The ','') DESC or similar.
A single quote is used here?

uli · 2011-09-06 14:03:38

testdeputy wrote:

or similar.

What exactly do you want sorted out, or am I missing something? I was referring to cases like D'Artagnan, where no straight quotes should be used.

testdeputy · 2011-09-06 14:28:03

the tag I would like to write looks similar to
<txp:article limit="5" sort="REPLACE(REPLACE(REPLACE(Title,'The ',''),'A ',''),'An ','') ASC,Title" />

Bloke · 2011-09-06 14:53:10

Don’t think there’s a way round it because doSlash() is immutable.

The only way I can find is to use smd_query because it trusts you to escape your own data or provide paranoia-fuelled filtering rules. It only escapes GET/POST data by default, not your own query attribute, since the plugin assumes you trust yourself :-)

The following approach works for me, but you’ll need to be creative with the WHERE clause and quotes if you want to pull stuff out from the current section only:

<txp:smd_query query="SELECT * FROM textpattern
    WHERE 1=1
    ORDER BY TRIM(LEADING 'The ' FROM TRIM(LEADING 'A ' FROM TRIM(LEADING 'An ' FROM Title))) ASC,Title"
    populate="article" limit="10">
   <txp:permlink><txp:title /></txp:permlink>
   <txp:excerpt />
</txp:smd_query>

You can also paginate the results if you wish.

Last edited by Bloke (2011-09-06 14:53:40)

testdeputy · 2011-09-06 18:46:20

For now, underlying case is a private website, I decided to hack publish.php and disabled the doSlash on the sort clause.
Maybe I trust myself, hahaha (already hacked that file anyway, to enable search in custom fields), or will learn the hard way.

The smd_query plugin looks very interesting for another project, bookmarked!

And thanx very mucho for your WAAAAAY better suggestion of handling starting The, A, etc. with TRIM(LEADING).

[edit] taking it up a notch..
ORDER BY TRIM(LEADING LOWER('The ') FROM TRIM(LEADING LOWER('A ') FROM TRIM(LEADING LOWER('An ') FROM LOWER(Title)))) ASC,Title
saves a bit when handling ‘the’ against ‘The’, ‘a’ against ‘A’, etc.
It’s a long way to perfectness, but a fine road to ride.

Last edited by testdeputy (2011-09-06 23:48:51)

Gocom · 2011-09-06 19:34:15

testdeputy wrote:

already hacked that file anyway, to enable search in custom fields

Textpattern has preference option for adding fields to the search, hacking the file isn’t needed if you are fine with the default matching method. See wet_haystack for automated index builder.

testdeputy · 2011-09-06 21:25:02

The wet_haystack plugin alters textpattern DB and adds a record to prefs DB.
I hacked it manually, both methods are a one-time-thing-to-do when upgrading.

There are many ways to nirvana…

Gocom · 2011-09-06 22:13:57

testdeputy wrote:

I hacked it manually, both methods are a one-time-thing-to-do when upgrading.

While you need to patch manually modified files again, you do not have to re-do changes wet_haystack does. The indexes and preferences are kept when updating TXP.

Textpattern CMS

Textpattern CMS support forum

#1 2011-09-05 20:00:36

escaping '

#2 2011-09-05 20:40:15

Re: escaping '

#3 2011-09-06 11:19:15

Re: escaping '

#4 2011-09-06 13:23:50

Re: escaping '

#5 2011-09-06 13:47:08

Re: escaping '

#6 2011-09-06 14:03:38

Re: escaping '

#7 2011-09-06 14:28:03

Re: escaping '

#8 2011-09-06 14:53:10

Re: escaping '

#9 2011-09-06 18:46:20

Re: escaping '

#10 2011-09-06 19:34:15

Re: escaping '

#11 2011-09-06 21:25:02

Re: escaping '

#12 2011-09-06 22:13:57

Re: escaping '

Board footer