The DB and Dynamic Publishing
Seeing as how Elise is having fun with PHP and databases I though I'd show you how to easily play around with the database in dynamic publishing. This is all stuff I've picked up when talking to Brad Choate, the man behind the amazing dynamic system.
Movable Type 3.1+ comes with a dynamic publishing system that has EZSQL rolled in for all the SQL databases MT supports and as a result interaction with and manipulating of the DB becomes a piece of cake. At the moment, however, this system works only for MySQL databases.
First of all to call the DB, you just need three simple lines:
<?php include('<$MTCGIServerPath$>/php/mt.php'); $mt = new MT(<$MTBlogID$>, '<$MTCGIServerPath$>/mt.cfg'); $db = $mt->db();
You may notice that this code is similar to the code found in the Dynamic Site Bootstrapper template. Basically it calls mt.php and points $db to the MT's database object. Using $db, you can run any EZSQL function for example, within the PHP script I could have something like this
$title = $db->get_var("select entry_title from mt_entry where entry_id = 1");
This would get the title of the entry who's ID is 1 assign its value to $title. The query you run (between the stuff between the two brackets) is pure sql.
Things change if you want to select more than one field however. For example, if I wanted to get all the fields for a given entry, I could go either of two ways.
This first method is similar to the above code, you can do something like this
$entry = $db->get_results("select * from mt_entry where entry_id = 1");
and then you would call each individual field like this
$title = $entry->entry_title;
Method Two is slightly different and perhaps more complicated to explain but it is used in MT.
$entry = $db->get_row("select * from mt_entry where entry_id = 1", ARRAY_A);
The ARRAY_A thing means it will return the data as an array of associative arrays. Putting it simply this means that to get the value of individual fields you do something like
$title = $entry['entry_title'];
where the bit between the quotes is the field name.
Through my testing, I've not found any method better, I normally use Method One because I forget the ARRAY_A bit but Brad uses Method Two in MT so perhaps it is the better of the two!
There are also several extra functions one can find in mtdb_base.php. These include things like fetching a comment to performing the equivalent of an <MTEntries> tag. Calling these functions is quite simple, for example if I wanted to call fetch_blog, I would do this
$blog_id = 1; $blog = $db->fetch_blog($blog_id);
and you can get the individual values by using the "Method Two" format i.e.
$blog_id = $blog['blog_id'];
and so on.
When calling more complex functions like fetch_entries (the equivalent of <MTEntries>) you need to specify the $args argument which is a rather complex array depending on the function. Your best bet to understand what you need to specify for $args would be to look at existing MT code, for example you could find the value of $args that is passed to fetch_entries in block.MTEntries.php and the value of $args passed to fetch_comments in block.MTComments.php and so on.
The dynamic $db object is very powerful and if much easier to work with than manually doing the mysql. Have fun with it, hopefully we'll see some cool stuff!