Movable Type Community Forum > Help With Mtsqlentries

Movable Type Community Forum > Additional Resources > Plugin Development and Usage

dianaschnuth

Jan 3 2009, 05:31 PM

I'm attempting to use the SQL plugin to tweak my previous/next links to exclude certain posts (twitter and delicious feed imports). The queries are pretty straightforward, and they work in PHPMyAdmin (substituting out the [MTEntryDate] for an actual date, of course), but when I plug them into my individual archive template, only the Next link works. Whenever I attempt to include the code for the Previous link, I get "Publish error in template 'Individual Entry Archive': Error in <mtSQLEntries> tag: Error in query: You have an error in your SQL syntax..."

This is what I have in my template (and only the first part breaks it - the last part is fine):

CODE

<div>Previous:
<MTSQLEntries query="SELECT entry_id, entry_authored_on FROM mt_entry WHERE entry_title NOT LIKE 'Twitter Update%' AND entry_title NOT LIKE 'links for%' AND entry_authored_on < '[MTEntryDate format_name=iso8601]' ORDER BY entry_authored_on DESC LIMIT 1">
<a href="<MTEntryPermalink>"><MTEntryTitle></a>
</MTSQLEntries>
| <a href="/archives.php">Archives</a> | Next:
<MTSQLEntries query="SELECT entry_id, entry_authored_on FROM mt_entry WHERE entry_title NOT LIKE 'Twitter Update%' AND entry_title NOT LIKE 'links for%' AND entry_authored_on > '[MTEntryDate format_name=iso8601]' ORDER BY entry_authored_on LIMIT 1">
<a href="<MTEntryPermalink>"><MTEntryTitle></a>
</MTSQLEntries>
</div>

I'm totally confused, because the two queries are so similar. Can someone help me figure out what should be painfully obvious?

OtherNiceMan

Jan 4 2009, 03:12 AM

Not used the plugin myself so this is a few guesses.

Looking at the examples vs. your code it is not clear if the plugin supports tag attributes like format_name. If they do and it is like the normal tag then I would have expected the iso8601 to be in quotes likes a normal attribute.

dianaschnuth

Jan 4 2009, 03:22 PM

Thanks for the ideas!

I tried messing around with the query that does currently work, adding quotes to the format_name. (I figured that, if it doesn't break the one that works, it should work to fix the one that doesn't.) Any quotes I put in are affecting the quote attribute of the MTSQLEntries tag, whether I try to escape them with a backslash or not. If I remove the format_name altogether, no results are returned for the query, since the default EntryDate format isn't the same as the date stamp in the mt_entry table.

So, good thoughts, but I don't think that's the problem.

Any other ideas? :-)

dianaschnuth

Jan 4 2009, 03:48 PM

Hmm, wait. I must not have tried single quotes. The single quotes don't break the one that already works (Next), but they also don't fix the one that doesn't (Previous):

CODE

<div>Previous:
<MTSQLEntries query="SELECT entry_id, entry_authored_on FROM mt_entry WHERE entry_title NOT LIKE 'Twitter Update%' AND entry_title NOT LIKE 'links for%' AND entry_authored_on < '[MTEntryDate format_name='iso8601']' ORDER BY entry_authored_on DESC LIMIT 1">
<a href="<MTEntryPermalink>"><MTEntryTitle></a>
</MTSQLEntries>
| <a href="/archives.php">Archives</a> | Next:
<MTSQLEntries query="SELECT entry_id, entry_authored_on FROM mt_entry WHERE entry_title NOT LIKE 'Twitter Update%' AND entry_title NOT LIKE 'links for%' AND entry_authored_on > '[MTEntryDate format_name='iso8601']' ORDER BY entry_authored_on LIMIT 1">
<a href="<MTEntryPermalink>"><MTEntryTitle></a>
</MTSQLEntries>
</div>

The exact error I'm getting is:

"An error occurred publishing entry '(My Entry)': Publish error in template 'Individual Entry Archive': Error in <mtSQLEntries> tag: Error in query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'iso8601']' ORDER BY entry_authored_on DESC LIMIT 1"> <a href="http://blog.dianas' at line 1"

I'll keep putzing around with this - someone give me a heads-up if you have any more ideas!

dianaschnuth

Jan 4 2009, 04:17 PM

Problem solved!

There must be a problem with the plugin wherein you can't have a "less than" in the query. I discovered this after adding a few new WHERE clauses, per the readme (http://code.sixapart.com/svn/mtplugins/trunk/SQL/README.txt), but I forgot to change my greater-than to a less-than. I ended up changing the less-than sign to the HTML escape character <, and it worked fine. (Of course, my SQL-fu isn't as strong as I'd like, so I had some more tweaks to make to the actual query, but at least it worked...)

Just wanted to be sure to post my resolution, so if anyone has a similar problem in the future, they'll know how to fix it. Now I'm off to comment on the plugin page itself to document the issue!

Thanks again, OtherNiceMan!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.