SQL Plugin

Now that Movable Type supports a real database with the MySQL support in version 2.2, it's time for some new tags that can let you select entries, comments and categories using any criteria you'd like. Click the 'more' link for full docs and download link.

Availability

You can download this plugin here: mtsql-1_52.zip

Installation

To install, place the 'sql.pl' file in your Movable Type 'plugins' directory. The 'sql.pm' file should be placed in a 'bradchoate' subdirectory underneath your Movable Type 'extlib' directory. Your installation should look like this:

  • (mt home)/plugins/sql.pl
  • (mt home)/extlib/bradchoate/sql.pm

Refer to the Movable Type documentation for more information regarding plugins.

This plugin has a tag called 'SQLAuthors' that requires the 'authors' plugin, also available from my web site.

Description

This plugin allows you to select entries, categories and comments from your blog database using a regular SQL query.

Tags made available through this plugin:

  • <MTSQL>: Allows for the selection of arbitrary data from your MySQL database. Use in conjunction with MTSQLColumn.
  • <MTSQLColumn>: Selects a single column value from a MTSQL query.
  • <MTSQLHeader>: Defines a header for your SQL query.
  • <MTSQLFooter>: Defines a footer for your SQL query.
  • <MTSQLEntries>: Allows selection of entries using a SQL query. This tag may contain any tags that will work in a <MTEntries> container tag.
  • <MTSQLComments>: Allows selection of comments using a SQL query. This tag may contain any tags that will work in a <MTComments> container tag.
  • <MTSQLCategories>: Allows selection of categories using a SQL query. This tag may contain any tags that will work in a <MTCategories> container tag.
  • <MTSQLPings>: Allows selection of TrackBack pings using a SQL query. This tag may contain any tags that will work in a <MTPings> container tag.
  • <MTSQLAuthors>: Allows selection of authors using a SQL query. This tag may contain any tags that will work in a <MTAuthors> container tag (this tag is a separate MT plugin).

<MTSQL>

These attributes are allowed:

  • query: The SQL to execute. It can be any valid SQL select statement.
  • default: A default value to output in case the query returns no rows.

<MTSQLColumn>

These attributes are allowed:

  • column: The number of the column (the first column is 1).
  • format: A 'printf' format in case you want to format your output.
  • default: A default value to output in case the column has a NULL value.

<MTSQLHeader>

Defines a block of HTML to be used when the first row of the query is processed.

<MTSQLFooter>

Defines a block of HTML to be used when the last row of the query is processed.

<MTSQLEntries>

These attributes are allowed:

  • query: The SQL query to execute. This should be a SELECT statement that returns as it's first column the entry_id column from the mt_entry table.
  • unfiltered: If assigned '1', the tag will not attempt any additional filtering on the comments returned by your SELECT statement. (Normally, entries that don't belong to the active blog or don't have a 'Published' status are excluded automatically.)
  • default: A default value to output in case the query returns no rows.

Here's an example (please note: the MTSQLEntries tag should all be on one line in your template):

<MTSQLEntries query="select entry_id from mt_entry
where entry_title like '%Movable Type%'">
  <a href="<MTEntryLink>"><MTEntryTitle></a><br />
</MTSQLEntries>

The above will return all blog entries you have created that have the phrase 'Movable Type' in the entry title.

<MTSQLComments>

These attributes are allowed:

  • query: The SQL query to execute. This should be a SELECT statement that returns as it's first column the comment_id column from the mt_comment table.
  • unfiltered: If assigned '1', the tag will not attempt any additional filtering on the comments returned by your SELECT statement. (Normally, comments that don't belong to the active blog are excluded automatically.)
  • default: A default value to output in case the query returns no rows.

This tag is designed to select comments without respect to the current entry.

<MTSQLCategories>

These attributes are allowed:

  • query: The SQL query to execute. This should be a SELECT statement that returns as it's first column the category_id column from the mt_category table.
  • unfiltered: If assigned '1', the tag will not attempt any additional filtering on the categories returned by your SELECT statement. (Normally, categories that don't belong to the active blog are excluded automatically.)
  • default: A default value to output in case the query returns no rows.

<MTSQLPings>

These attributes are allowed:

  • query: The SQL query to execute. This should be a SELECT statement that returns as it's first column the tbping_id column from the mt_tbping table.
  • unfiltered: If assigned '1', the tag will not attempt any additional filtering on the pings returned by your SELECT statement. (Normally, pings that don't belong to the active blog are excluded automatically.)
  • default: A default value to output in case the query returns no rows.

<MTSQLAuthors>

These attributes are allowed:

  • query: The SQL query to execute. This should be a SELECT statement that returns as it's first column the author_id column from the mt_author table.
  • unfiltered: If assigned '1', the tag will not attempt any additional filtering on the authors returned by your SELECT statement. (Normally, authors that don't have permissions for the active blog are excluded automatically.)
  • default: A default value to output in case the query returns no rows.

Performance

Please note: SQL queries are very powerful, and if you're not familiar with the language, it will take some time to learn how to use it. The links provided below are very helpful. Also, be patient as you are creating them-- if they aren't working, don't assume that this plugin is at fault-- more than likely, it is an improperly constructed query.

Usage Notes

As a bonus, the 'query' and 'default' parameters for each of these tags allows you to embed Movable Type variables which can be evaluated! Here's how you would form a query that uses a Movable Type variable (please note: the MTSQLCategories tag should all be on one line in your template):

<MTSQLCategories query="select category_id from
mt_category where category_label like '[MTArchiveTitle]%'
and category_label != '[MTArchiveTitle]'">
  <a href="<MTCategoryArchiveLink>">
    <MTCategoryLabel></a><br />
</MTSQLCategories>

If the above is invoked inside your category archive template, it will list any other categories that begin with the name of the category being processed. For example, on my blog, I have 3 categories that start with 'Movable Type': 'Movable Type', 'Movable Type Plugins', and 'Movable Type Tips'. For the category archive of the 'Movable Type' category, the above query would select the other 2 categories listing and linking to them. Note that you have to use the HTML entities for embedded less-than, greater-than, quotes and so forth.

Reference for Tables

Current as of Movable Type version 2.5.

Table: mt_entry

FieldType
entry_idint(11)
entry_blog_idint(11)
entry_statustinyint(4)
entry_author_idint(11)
entry_allow_commentstinyint(4)
entry_allow_pingstinyint(4)
entry_convert_breakstinyint(4)
entry_category_idint(11)
entry_titlevarchar(255)
entry_excerpttext
entry_texttext
entry_text_moretext
entry_to_ping_urlstext
entry_pinged_urlstext
entry_created_ondatetime
entry_modified_ontimestamp(14)
entry_created_byint(11)
entry_modified_byint(11)
entry_keywordstext
entry_tangent_cachetext

Table: mt_comment

FieldType
comment_idint(11)
comment_blog_idint(11)
comment_entry_idint(11)
comment_ipvarchar(16)
comment_authorvarchar(100)
comment_emailvarchar(75)
comment_urlvarchar(255)
comment_texttext
comment_created_ondatetime
comment_modified_ontimestamp(14)
comment_created_byint(11)
comment_modified_byint(11)

Table: mt_category

FieldType
category_idint(11)
category_blog_idint(11)
category_allow_pingstinyint(4)
category_labelvarchar(100)
category_descriptiontext
category_author_idint(11)
category_ping_urlstext

Table: mt_placement (needed for some selects between category and entry)

FieldType
placement_idint(11)
placement_entry_idint(11)
placement_blog_idint(11)
placement_category_idint(11)
placement_is_primarytinyint(4)

Table mt_tbping:

FieldType
tbping_idint(11)
tbping_blog_idint(11)
tbping_tb_idint(11)
tbping_titlevarchar(255)
tbping_excerpttext
tbping_source_urlvarchar(255)
tbping_ipvarchar(15)
tbping_blog_namevarchar(255)
tbping_created_ondatetime
tbping_modified_ontimestamp(14)
tbping_created_byint(11)
tbping_modified_byint(11)

Table mt_author:

FieldType
author_idint(11)
author_namevarchar(50)
author_nicknamevarchar(50)
author_passwordvarchar(40)
author_emailvarchar(75)
author_urlvarchar(255)
author_can_create_blogtinyint(4)
author_can_view_logtinyint(4)
author_hintvarchar(75)
author_created_byint(11)
author_public_keytext
author_preferred_languagevarchar(50)

Performance Notes

It is up to you to optimize your queries for maximum performance. If you have several blogs in your database, it would be a good idea to include selection on 'blog_id' in your WHERE clause for each table you retrieve from.

For More Information

For the MySQL database, this link should give you the official MySQL documentation for the 'SELECT' statement:
      http://www.mysql.com/doc/S/E/SELECT.html

And this looks like a good introduction to learning SQL and specifically, SQL SELECT statements:
      http://www.w3schools.com/sql/sql_select.asp

Support

If you have any questions or need assistance with this plugin, please add your questions and/or comments using the form below.

License

Released under the MIT License.

Changelog

  • 1.52: Fixed warning issue for SQLCategories tag.
  • 1.51: Fixed test for checking for inner 'MTEntries' tag. Thanks to Kevin Shay (www.staggernation.com) for the pointer.
  • 1.5: Queries for SQLEntries, SQLAuthors, SQLPings, SQLCategories, SQLComments can now select any number of fields (but the respective 'id' column must be among them). Also, those tags can now use the SQLHeader, SQLFooter and SQLColumn tags.
  • 1.4: The blog context will now change properly when using 'unfiltered' queries.
  • 1.31: Corrected closure tags for embedded expressions.
  • 1.3: Added SQL, SQLColumn, SQLHeader, SQLFooter and default attribute to all SQL* routines.
  • 1.1: Added SQLAuthors, SQLPings. Rearchitected plugin by separating code into sql.pl and sql.pm.
  • 1.0: Initial release

TrackBack

TrackBack URL for this entry:
http://bradchoate.com/mt/feedback/tb/24

Listed below are links to weblogs that reference SQL Plugin:

» I wish.. from RoughingIT
SQL Plugin really looks cool. [Read More]

» Minor changes from johnwb.com
Some minor (semi-major?) changes to the layout here. It's not quite as clean and uncluttered as before, but it presents a whole lot more information and seems to be a good tradeoff. I even got the "recently commented" thing working with the old version... [Read More]

» or lack thereof from the blivit
i had planned to wake up today and accomplish things. what exactly i'm not sure, but nonetheless i didn't. [Read More]

» Solution to MT SQL plugin DBI error from Tread lightly on the things of earth
In trying to use Brad Choate's cool SQL Plugin, I got this error on rebuilding this site: Can't get DBI::st=HASH(0x81240ec)->{NAME_hash}: unrecognised attribute at /path/to/extlib/bradchoate/sql.pm line 132. A comment clue from Chris at the bottom of t... [Read More]

» Using SQL to populate rightcontent photos from Tread lightly on the things of earth
Now generating main page rightcontent at each posting/manual rebuild via the following SQL select (uses Brad's SQL Plugin) instead of including a static file via SSI (idea from Mark). (Blog id 2 below is my photolog blog, which before wrote out the pho... [Read More]

» So far, so good from Jonathon Delacour
Happily, the move seems to have worked. I was able to see the new server less than 24 hours after changing the DNS. It's such a relief to be on a Linux server instead of IIS—the support people at my previous host were excellent but I love being a... [Read More]

» So far, so good from Jonathon Delacour
Happily, the move seems to have worked. I was able to see the new server less than 24 hours after changing the DNS. It's such a relief to be on a Linux server instead of IIS—the support people at my previous host were excellent but I love being a... [Read More]

» Sideblog "Interesting Links" setup HOWTO from Tread lightly on the things of earth
Brad Choate mentions his sideblog idea -- in which he "lists things that I wish to share when I have no time to supply my own witty commentary" -- which in turn came from Anil. Until Brad describes how he set up his sideblog and displays its content i... [Read More]

» Sideblog "Interesting Links" setup HOWTO from Tread lightly on the things of earth
Brad Choate mentions his sideblog idea -- in which he "lists things that I wish to share when I have no time to supply my own witty commentary" -- which in turn came from Anil. Until Brad describes how he set up his sideblog and displays its content i... [Read More]

» Sideblog "Interesting Links" setup HOWTO from Tread lightly on the things of earth
Brad Choate mentions his sideblog idea -- in which he "lists things that I wish to share when I have no time to supply my own witty commentary" -- which in turn came from Anil. Until Brad describes how he set up his sideblog and displays its content i... [Read More]

» Sideblog "Interesting Links" setup HOWTO from Tread lightly on the things of earth
Brad Choate mentions his sideblog idea -- in which he "lists things that I wish to share when I have no time to supply my own witty commentary" -- which in turn came from Anil. Until Brad describes how he set up his sideblog and displays its content i... [Read More]

» Solution to MT SQL plugin DBI error from Tread lightly on the things of earth
In trying to use Brad Choate's cool SQL Plugin, I got this error on rebuilding this site: Can't get DBI::st=HASH(0x81240ec)->{NAME_hash}: unrecognised attribute at /path/to/extlib/bradchoate/sql.pm line 132. A comment clue from Chris at the bottom of t... [Read More]

» Minor blog changes from Bits
Added the 'Support Democracy in Iraq' banner to the right. Go here to get one for yourself (thx, Dustin). Also [Read More]

» log20030317 from HOHBUKURO
「記事の更新日順一覧をつくる方法」... [Read More]

» Sideblog "Interesting Links" setup HOWTO from Tread lightly on the things of earth
Brad Choate mentions his sideblog idea -- in which he "lists things that I wish to share when I have no time to supply my own witty commentary" -- which in turn came from Anil. Until Brad describes how he set up his sideblog and displays its content i... [Read More]

» Site Changes from deanpence
I’ve made a few minor site changes. [Read More]

» Trackbacks integrated in comments feed from public virtual MemoryStream
I finally got around to integrating trackbacks into my comments feed. I initially followed Paul Freeman's suggestion of using Brad Choate's MTSQL plugin. This is an extremely powerful plugin, though it added a level of complexity to my template that I ... [Read More]

» Installed Plugins from Trial Blog
Revised: May 1, 2003 Related Entries Required me to manupulate MySQL and install Brad Choate’s MT SQL plugin... category archives Topic Icon comment SimpleComments entry category ExcludeCategories Related Entries entry date BlogTimes general collect ... [Read More]

» Related Entries Redux and Implementation from inluminent/weblog
Thanks to all the comments on my most recent Related Entries post, I've installed Adam Kalsey's Related Entries code. The [Read More]

» Tenuously Related Entries from nicholasjon.com : a weblog
I've added a new related entries section for each post based on lazywebbing together the work of at least two... [Read More]

» Tenuously Related Entries from nicholasjon.com : a weblog
I've added a new related entries section for each post based on lazywebbing together the work of at least two... [Read More]

» Tidbits from Jeremy Zawodny's blog
There's some stuff that I've been meaning to check out. But I haven't had a chance yet: Hackers and Painters (Paul Graham) The Hundred-Year Language (Paul Graham) Related Entries (Kalsey) MTSQL (Brad Choate) Rsync Snapshots (Mike Rubel) Blog Buttons Ma... [Read More]

» MTSQL Tags and ColdFusion from Joe Grossberg
Brad Choate has released an SQL Plugin for Movable Type. Example: "> Look familiar? If you use ColdFusion, it sure [Read More]

» SQL Enabled MovableType from Ordinary-Life.net
An SQL Plugin for MovableType, could prove useful for many a blogger. via Joe Grossberg... [Read More]

» show the previous and next entry in category from Al-Muhajabah's Movable Type Tips
On my individual entry archive page I provide links to the next and previous entries in the category instead of overall. This is particularly important for the way that I have my blog set up, since the three categories (A... [Read More]

» simulating a semantic search of your blog from Al-Muhajabah's Movable Type Tips
Using the SQL plugin, you can simulate a semantic search of your blog to find entries related to a given entry. A clever idea from Adam Kalsey. Just follow his instructions and you're good to go. Note that you will... [Read More]

» SQL Plugin from SQL Resources Logfile
Link: SQL Plugin Snippet: Now that Movable Type supports a real database with the MySQL support in version 2.2, its time for some new tags that can let you select entries, comments and categories using any criteria youd like ...via:... [Read More]

» SQL Plugin from SQL Resources Logfile
Index for SQL-Weblog-Articles - "SQL Plugin" by bradchoate.com [Read More]

» Nothing personal from dive into mark
Are you tired of all the personal stuff posted here recently? [Read More]

» Multiple blogs on a single index page from Legends of the Sun Pig

For a while back in March I had changed the look of my main blog page so that it showed my Quick Reviews and my ordinary blog entries together in the main body of the page.

[Read More]

» Done: from The Last Word
Templates for main indexes complete √ Templates for archives... [Read More]

» How Long Has Your Blog Been Up? from David Raynes
With the latest release of my Countdown plugin, I had a neat idea that I figured I'd share with everyone.... [Read More]

» MovableType 2.64 from Knight of Reflections
Just a minor upgrade. Changelog. [Read More]

» I'm a New-Fangled Quip from nicholasjon.com : a weblog
Through the use of Brad Choate's magical MTSQL plugin I was able to permanently resurrect the Quips section. Even better... [Read More]

» MT Plugins Installed from Technology Updates
The following MovableType plugins were installed: From Brad Choate IncludeEx - Improves on MT’s Include tag by processing the included file for MT tags. MTAuthors - This plugin allows you to list the authors for your blog. You can also... [Read More]

» Related Pages from Bryan Strawser's Weblog
Have now also installed a "related entries" section that utilizes Adam Kalsey's great Related Entries Revisited information as well as Brad Choate's MT SQL Plugin. Outstanding information. I am beginning to understand why alot of folks choose to use Mo... [Read More]

» Bits and Pieces from Blog de Halavais
Added two things to the UBlog: A listing of recent posts and an alphabetized list of blogs on the site.... [Read More]

» MT-Anpassung Phase I abgeschlossen from WWWorker
So, frs erste bin ich fertig mit meiner MT-Anpassung.... [Read More]

» Global Recently Commented On from Blog
Movable Type has a built-in way of displaying the most recently commented on entries for a given blog: in any <MTEntries> tag, simply add the recently_commented_on="n" attribute. What I wanted though, was a way to show the most recently commented... [Read More]

» sort entry by id from . cynics' - /mak'in-trash`/ .
one my fotos index page, I wanted to have the latest photo I uploaded to be displayed. however, using MT’s default behavior, lastn to show the last entry, it will sort according to the date of the entry. As I... [Read More]

» Sort Categories by Date from . cynics' - /mak'in-trash`/ .
Sort categories by last modified or last created. [Read More]

» Popular Posts from Procrastination
I have added two different ways of looking at popular posts on the sidebar. One looks at my webserver logs and selects the posts who individual entry archive pages have been visited the most in the past 3 days (extension... [Read More]

» Popular Posts from Procrastination
I have added two different ways of looking at popular posts on the sidebar. One looks at my webserver logs and selects the posts who individual entry archive pages have been visited the most in the past 3 days. (Extension... [Read More]

» MT Plugins from Life. Love. Faith.
Wanted to give some shoutouts to the kickin' MovableType plugins that are making the new design of the site rock... [Read More]

» Adding User-Defined Fields in Movable Type from Gadgetopia
I've complained off and on about the lack of user-defined fields in Movable Type. Today was finally the day I got off my high-horse and messed with some code... Here is a method to add a new field to the... [Read More]

» RSSToMySQL from Ari Paparo Dot Com
[Read More]

» Even iets testen from het hondje van Dirkie
Wie kletst hier het vaakst? NaamAantal praatjes Met dank aan de SQL Plugin van Brad Choate.... [Read More]

» Working around obstacles from Mind of Knowledge
Interesting link: Testing Grounds Slowly working on my new design. Having a bit of a struggle with implementing this plugin (MTSQL). Anyone with extreme SQL/MySQL knowledge -- feel free to contact me :)... [Read More]

» related entries from 小六的塗鴨本
a fulltext search in MySQL with entry title, keywords and excerpt to generate a list of related entries [Read More]

» mr web tech 2.1 from mr web tech 2.1
sideblog [Read More]

» Mikan Moblog Dayblog from The Mikan Chronicles
Way back at the end of June, I wrote about creating what I called a 'dayblog'- a blog, or more specifically a new view of the entries in an existing blog, where the entries were arranged not by date, but... [Read More]

» Mikan Moblog Dayblog from The Mikan Chronicles
Way back at the end of June, I wrote about creating what I called a 'dayblog'- a blog, or more specifically a new view of the entries in an existing blog, where the entries were arranged not by date, but... [Read More]

» While not blogging here... from benshead
I've been busy recently, with work, a cross country move, a massive snow storm... But one of my projects has been a comprehensive restructuring of the Institutes Learning Community's MT installation. Tasks have included: · Syndicating the content ... [Read More]

» Kottke-ization Complete (I Think) from Capn Design
As you can see (if you're looking at this through a browser), I have Kottke-ized my site. Essentially, I brought the reviews into the main blog. I made a few small changes to the far right side as well. I'm... [Read More]

» QuickLinks from mapu.de: Weblog
Auch ich habe nun meine QuickLinks in der Seitenspalte. Wenn man seine MT-Installation auf MySQL laufen lsst, dann braucht man... [Read More]

» Bloggie Scripty Reverb from Bloggie Broad
I like to keep an up-to-date listing of all the... [Read More]

» MT SQL from Mind of Knowledge
Brad Choate: SQL Plugin... [Read More]

» Integrated Quicklink Redesign from hit-or-miss
I've redesigned my weblog to combine the regular blog entries with the Quicklinks in one column -- basically copying Jason... [Read More]

» MT ÷, hack, ũƮ from EOUIA
ũƮ ϳ ߰ ߴµ, ʴ´. ð 鿩 . Ƹ, MT ÷ΰ hack, ũƮ ޾ƺ , ؼ ѹ ߰ ʿ . ݱ ÷, ũƮ, hack ... [Read More]

» About the Site from Thinkless Static
A colophon of sorts. [Read More]

» New QuickLinks Box from Stratified
I decided to implement a box that would appear once under each day heading that would contain all of the links I post for that day. It is mostly done at this point, and you should (crossing fingers) see an... [Read More]

» Redesign? What Redesign? from The Stillness of the Woods
I know what you're thinking: Hey, It took three weeks for this? Slacker. Well, I'll just say that looks can... [Read More]

» Lastn Entries By modified_on from Sundown
I just killed most of a perfectly good Sunday afternoon trying to solve this stupid problem in Movable Type, and now that I've got it I had better preserve it for posterity. [Read More]

» Lastn Entries By modified_on from Sundown
I just killed most of a perfectly good Sunday afternoon trying to solve this stupid problem in Movable Type, and now that I've got it I had better preserve it for posterity. [Read More]

» Lastn Entries By modified_on from Sundown
I just killed most of a perfectly good Sunday afternoon trying to solve this stupid problem in Movable Type, and now that I've got it I had better preserve it for posterity. [Read More]

» Lastn Entries By modified_on from Sundown
I just killed most of a perfectly good Sunday afternoon trying to solve this stupid problem in Movable Type, and now that I've got it I had better preserve it for posterity. [Read More]

» Automatically Closing Old MT Entries from Full Speed
There has been quite a bit of talk about preventing weblog comment spam lately. Jeremy Zawodny and David Sifry have each come up with similar solutions for automatically turning off comments on older posts. While both of these solutions work... [Read More]

» Automatically Closing Old MT Entries from Full Speed
There has been quite a bit of talk about preventing weblog comment spam lately. Jeremy Zawodny and David Sifry have each come up with similar solutions for automatically turning off comments on older posts. While both of these solutions work... [Read More]

» Category Subset Template from life
The Objective: Create a page that displays the full text of entries X, Y, and Z, which are a subset... [Read More]

» Lastn Entries By modified_on from Sundown
I just killed most of a perfectly good Sunday afternoon trying to solve this stupid problem in Movable Type, and now that I've got it I had better preserve it for posterity. [Read More]

» Working MySQL for MT from Life At Metzger.Ws
This is more of a note to myself I need to take some time this weekend to delve into Brad Choate's SQL Plugin. I think I could get lost in this code...... [Read More]

» Speed from Ed's Blog
The server seems to be having a few problems speed-wise at the moment, so this means that the comments scripts... [Read More]

» Speed from Ed's Blog
The server seems to be having a few problems speed-wise at the moment, so this means that the comments scripts... [Read More]

» Speed from Ed's Blog
The server seems to be having a few problems speed-wise at the moment, so this means that the comments scripts... [Read More]

» Speed from Ed's Blog
The server seems to be having a few problems speed-wise at the moment, so this means that the comments scripts... [Read More]

» Speed from Ed's Blog
The server seems to be having a few problems speed-wise at the moment, so this means that the comments scripts... [Read More]

» MTのプラグイン増やした from ブログ@ギャラクシーズ
現在、このMovableType、plu... [Read More]

» MT 3.0? from Full Speed
John Gruber: "The current versions of both of my MT plug-ins — SmartyPants and Markdown — are fully compatible with MT 3.0." OK, that was going to be the biggest obstacle for the MT3 upgrade. With that out of the... [Read More]

» Tenacious me from iMark
Good grief, that actually proved surprisingly difficult. The problem is one I'm sure every blogger encounters sooner rather than later: what exactly to do with those amusing links you stumble across on a semi-regular basis, which you'd like to share... [Read More]

» Movable Type 4.0 from subbu.org
After some hesitation, I have upgraded this blog to Movable Type 4.0 Beta 6 today. The upgrade process itself went smoothly without errors. I had to disable to some old Movable Type plugins (e.g. Brad Choate's SQL plugin) and remove relevant tags from ... [Read More]

33 Comments

Jay said:

Brad, I haven't used this yet, but in theory, it ROCKS. Nice job...

Brad said:

Doesn't it? If you go to one of my category pages, you'll see that I can show recent comments for just that category. I'm doing that using a MTSQLComments tag. Here's how it looks:

<MTSQLComments query="select distinct com.comment_id from mt_comment com, mt_placement plc, mt_category cat where com.comment_entry_id=plc.placement_entry_id and plc.placement_category_id=cat.category_id and cat.category_label like '&lt;MTArchiveTitle&gt;%' order by com.comment_created_on desc limit 15">
  &gt; <a href="/past/<$MTCommentEntryID pad="1"$>.php"><$MTCommentBody remove_html="1" trim_to="25"$></a><br /><br />
</MTSQLComments>

Cool, eh?

Jason Mevius said:

Can this plugin be used for pagination? Brenna helped me work up some php code that would query the sql backend and return a row count and generate a number of pages based on rowcount, with 'previous page' & 'next page' entries, but I can't use MT to it's potential like that.

Any ideas? The code I currently have resides at:

http://mt.sixapart.com/cgi-bin/ikonboard/ikonboard.cgi?s=3d4adc447c1dffff;act=ST;f=14;t=4658;st=15

Thanks,
Jason

Brad Choate said:

Not really. The problem is that index templates only generate 1 page. There isn't a way for index templates to build multiple pages. Even if there were, I would recommend you do this using some dynamic means as in PHP or CGI. It looks like you've got a good start on a PHP solution.

Jason Mevius said:

What about two sorting operations? What if I wanted to sort by MTEntryTitle then to run a secondary sort on MTEntryBody?

The code I currently have is:

order by entry_title asc

Any ideas?

Brad Choate said:

No problem: order by entry_title, entry_text asc

Jason Mevius said:

Sorry. I just stumbled upon it. For those who might be curious:

order by entry_title,entry_text asc

Adam Keys said:

Is it possible to nest SQL queries using the SQL tag? Just off the top of my head I'm thinking something like

<MTSQLEntries query="select entry_id from mt_entry where entry_category_id="<MTSQL query="select category_id from mt_category where category_label='Foo Bar'>">

In the end, my goal is to have categories that aren't published to my blog, but instead are published to other pages on my website, such as essays, biographical information, etc.

Am I bending MT too far towards a CMS? :)

Adam Keys said:

I feel like a dope now :)

After actually looking at the MT manual, this example jumped up and bit me in the face:


<MTEntries lastn="5" offset="5">
...
</MTEntries>

So, I could just set the lastn to zero and set the offset to the number of the article I want to add and include individual entries on any page I want, eh?

Similarly, I could add only the categories I want to my blog via

<MTEntries lastn="5" offset="5"> ... </MTEntries>

So, my discoveries are correct, is there any benefit to performance/feature benefit to doing this with the SQL tag rather than the standard tags?

Todd said:

I tried installing both the mtsql plugin and the authors plugin and then put


"> ()

into my template. When I rebuild, I get

Can't get DBI::st=HASH(0x87057f4)->{NAME_hash}: unrecognised attribute at /var/www/mt/extlib/bradchoate/sql.pm line 132

I'm using mt version 2.5. Thanks.

Nikki said:

Brad, you're awesome. Thank you for your contributions and all the hard work.

Adam Katz said:

I tried installing mtsql and mtifempty plugins. When I rebuilt my site I got an error "Build error in template 'Individual Entry Archive': Error in tag: This tag may only be used with a SQL-capable datastore. "

However.... mt-check tells me that I have DBD:mysql. Any idea what I did wrong? Is it possible that I'm running berkely db - how do I check?

CHECKING FOR DATA STORAGE MODULES:

The following modules are used for Movable Type data storage. You must
use either the Berkeley DB or MySQL database backend. In order to run
Movable Type, your server needs to have at least one of these modules
installed.

DB_File...
Your server has DB_File installed (version 1.72).

DBD::mysql...
Your server has DBD::mysql installed (version 2.0416).

Thanks!
-Adam

sherwin said:

Hi,

i got the following error after installing your plug-in (along with the author one too):

"Can't get DBI::st=HASH(0x860cc44)->{NAME_hash}: unrecognised attribute at extlib/bradchoate/sql.pm line 339."

this occured after rebuilding all files...any suggestions on how to go about solving this problem?

thanks in advance.

Chris James said:

Brad,

Regarding sherwin's error message - I traced the problem to an old version of perl-DBI (prior to 1.2.0 - when NAME_hash was introduced afai can tell).

Sadly, my linux admin skills aren't the best in the world - but with the kind help of Ryan W. Maple of Guardian Digital (makers of the first class Engarde Linux for running secure linux servers) I was able to get it running by updating my version of perl-DBI to 1.3.2

For other users running Engarde, the url for the updated packages is here: http://ftp.engardelinux.org/pub/engarde/people/ryan/stash/ESL-misc/

Best regards, and thanks for a very useful site.

Chris.

sherwin said:

"but with the kind help of Ryan W. Maple of Guardian Digital (makers of the first class Engarde Linux for running secure linux servers)"

hahaha thanks chris for the shameless-plug =p

sherwin said:

btw... i guess this solution can only work for you or ones using Engarde correct? how do i know if my webspace provider is running that, can i just type in a command in Shell?

thanks in advance.

Mike said:

If you have a shell account, you can implement Chris's hint above this way:

mkdir ~/lib
wget http://cpan.org/authors/id/T/TI/TIMB/DBI-1.32.tar.gz
tar xzvf DBI-1.32.tar.gz
cd DBI-1.32
perl Makefile.PL LIB=~/lib PREFIX=~/lib
make
make install

Then specify the new DBI to MT by inserting a 'use lib' in front of the 'use DBI' in /path/to/mt/lib/MT/ObjectDriver/DBI/mysql.pm:

# MWJ 20021224: use newer DBI 1.32 in ~/lib
use lib qw(/home/mwjames/lib/i386-linux);
use DBI;

Brad Lauster said:

Hi there. I've been playing with the MTSQL plugin, so I thought I'd share some code examples:

This one should pull the 5 most recent Trackback pings from the given category, when used in a Category Archive Template:
<MTSQLPings query="select distinct tbping_id from mt_tbping, mt_placement plc, mt_category cat where tbping_blog_id=[MTBlogID] and tbping_id=plc.placement_entry_id and plc.placement_category_id=cat.category_id and cat.category_label like '[MTArchiveTitle]%' order by tbping_created_on desc limit 5" default="&lt;div class=&quot;recent-body&quot;&gt;The [MTArchiveTitle] Cateogry Archive doesn't have any Trackback pings, yet.&lt;/div&gt;">
<div class="recent-body">
<$MTPingBlogName$><br /> sent a ping to this category on <$MTPingDate format="%b %e, %Y" $>, from the post, "<a href="<$MTPingURL$>" title="<$MTPingTitle$>"><$MTPingTitle$></a>."
</div>
</MTSQLPings>

This one should pull the 5 most recent Comments from the given month, when used in a Monthly Archive Template:
<MTSQLComments query="select distinct com.comment_id from mt_comment com, mt_placement plc where comment_blog_id=[MTBlogID] and com.comment_entry_id=plc.placement_entry_id and com.comment_created_on > '[MTArchiveDate format='%Y-%m-%d %H-%M-%S']' and com.comment_created_on < '[MTArchiveDateEnd format='%Y-%m-%d %H-%M-%S']' order by com.comment_created_on desc limit 5" default="&lt;div class=&quot;recent-body&quot;&gt;The [MTArchiveTitle] Category Archive doesn't have any Comments, yet.&lt;/div&gt;">
<div class="recent-body">
<a href="<$MTCommentEntryID pad="1"$>" title="Click to read the entire entry."><$MTCommentAuthor$></a> said:
<$MTCommentBody trim_to="50" convert_breaks="0" remove_html="1"$>...
</div>
</MTSQLComments>

This one should pull the 5 most recent Trackback pings from the given month, when used in a Monthly Archive Template:
<MTSQLPings query="select distinct tbping_id from mt_tbping where tbping_blog_id=[MTBlogID] and tbping_created_on > '[MTArchiveDate format='%Y-%m-%d %H-%M-%S']' and tbping_created_on < '[MTArchiveDateEnd format='%Y-%m-%d %H-%M-%S']' order by tbping_created_on desc limit 5" default="&lt;div class=&quot;recent-body&quot;&gt;The [MTArchiveTitle] Cateogry Archive doesn't have any Trackback pings, yet.&lt;/div&gt;">
<div class="recent-body">
<$MTPingBlogName$><br /> sent a ping to this category on <$MTPingDate format="%b %e, %Y" $>, from the post, "<a href="<$MTPingURL$>" title="<$MTPingTitle$>"><$MTPingTitle$></a>."
</div>
</MTSQLPings>

If you try to use any of these, be sure to replace the closing double-quotes (") with regular straight quotes. [Edited by Brad: you should be able to copy it straight now. Special characters have been removed.]

I'm by no means a SQL expert, so if you have optimization suggestions, please let me know. Cheers!

Hey Brad,
This plugin is turning out to be amazingly valuable. Thanks so much!

I wanted to let you know that some of your other plugins are working inside the MTSQLEntries tag and some aren't. For instance, MTIfNotEmpty is working but MTIfEmpty is not. Also, the wonderful MTEmbedImage refuses to work as well.

I don't know if there's anything you can do about it, but here I'd tell you fyi.

Silly me. No tags are being processed inside of an MTSQL tag. If you or anyone else knows of a way to process nested plugin tags, do tell.

Greg Thorne said:

Brad-
I'd like to use this plugin to select values out of a non-MT table. This table contains URL's and site names of sites I have bookmarked. Can I use this plugin for that purpose? If so, how can I use the MTSQLColumn tag to build the link?

Thanks for a great plugin.

Jennifer said:

I'm trying to use the MTSQLEntries tag on a category archive page - I'm trying to sort all the entries (for the current category archive page) alphabetically (by entry title)... But I can't seem to get it to recognize WHICH category the current archive page is...

There's a few other things I"m trying to do too... I explained it more over here

Would greatly appreciate some help! :)

first off, MTSQL is great! thanks very much, brad.

my objective is to leverage MTSQL to display categories associated with an individual entry in order of primary -> secondary (there are 2 and only 2 categories for each entry), rather than the MT default of alphabetical. i'm close, but am having some problems with the template.

please refer to this string for the latest problem i'm having:

http://www.movabletype.org/cgi-bin/ikonboard/ikonboard.cgi?s=3e7274de4255ffff;act=ST;f=9;t=16246;st=0

thanks,
gabe

Andrew Brown said:

Is is possible to use MTMYSQL as a way to generate annual indexes with a CGI script? I can write a template that will show all entries from any given year, using an MTMySQL query. What I would like to do, though, is to be able to pass the year as a parameter from a form which would be on the main index page. The CGI would then generate a variant of my static template page with the year (and perhaps the category) read off the form. Is there any obvious reason why this won't work?

The static template page, obviously, makes use of MT tags.

So I suppose what I am asking is whether the MT tags in my cgi-generated oage will be automatically processed by the web server.

I'm trying to combine the SQL plugin with the Amazon plugin to query a database with Amazon ASINs and to display a random title from that database.

Both plugins works perfectly well standalone, but I don't get the results from your SQL plugin to the Amazon plugin.

My source looks like this:

" target="_blank">" align="left" border="0" style="padding-right: 5px;">

Any hints?

I think MT's future is not as CGI scripts for blogging, but as a full-fledged tag-based language.

With its access to Perl, I think it'll be like a ColdFusion that doesn't suck.

Maybe I'm insane, but I think it's an avenue worth pursuing:
http://www.joegrossberg.com/archives/000632.html

m@ said:

I'm attempting to pull out a single column value from a custom table based on a date comparison...

unfortunately, i need to be able to use this:

<$MTEntryDate format="%m.%d.%Y"$>

inside the query statement.

i haven't been able to get it to work, using any combination of quotations, single quotes, backslashes, forward slashes, etc.

i've also been unable to get MTSetVar to accept its input to set a variable--and even if i could, i can't use MTGetVar inside the select statement, again, because i can't get MTSQL to ignore the mandatory quotation marks around the variable name.

any chance MTSQL will be made smarter, to better handle MT tags with attributes?

any other ideas for how i might accomplish my task?


thanks,

m@

m@ said:

ha! flash of insight (funny what happens when you say "i give up" and threaten to walk away from the computer) and i figured out the solution:

SELECT mtc.entry_oldID
FROM mt_custom AS mtc
LEFT JOIN mt_entry AS mte
ON (mtc.entry_date = mte.entry_created_on
AND mtc.blogID = mte.entry_blog_id)
WHERE mte.entry_id=[MTEntryID]

still, it'd be nice to be able to use more of MT's native tags *inside* the select statements.

but, hey, i'm happy now!

cheers,

m@

Michael Stucker said:

Is anybody else having trouble using greater than or less than signs inside the query? If I do this, it tells me "Build error in template 'Test Index': Error in <MTSQLEntries> tag: You did not specify a query " when I rebuild.

Michael Stucker said:

Nevermind, looks like this can be fixed by replacing those signs with the appropriate html entity.

kathy said:

hi brad,

thanks for the plugin.

i'm using the MTSQLEntries tag on my index page to show entries from all but one category.

my query looks like
select entry_id, entry_created_on from mt_entry, mt_placement
where entry_id = placement_entry_id AND placement_category_id != 6 AND placement_is_primary = 1 AND entry_blog_id = 1 AND entry_status = 2 ORDER BY entry_created_on desc LIMIT 4"

i'm finding that for days with more than one entry, the second entry doesn't display the date & time, just the title.

would you have any ideas on what's going on?

thanks.

liz said:

brad this is beyond excellent. thank you so much for providing such a useful solution to many of my MT "there's gotta be a way of pulling this..." thoughts!

Great stuff, thanks a lot! Are the actual values these fields can take documented somewhere? For instance, if I want to group posts by primary category only (i.e. not taking into account their other categories), what should placement_is_primary be?

About

This article was published on July 11, 2002 2:44 AM.

The article previously posted was Lights Out.

The next article is EzPop.

Many more can be found on the home page or by looking through the archives.

Powered by Movable Type