Add the $_itemArray property shown here in bold to the definitions at the top of the page:

protected $_feedTitle;
protected $_feedLink;
protected $_feedDescription;
protected $_useNow;
protected $_itemArray;

The setter method for the <title> nodes of each <item> is very simple. It takes one argument: the name of the database column you want to use and assigns it to the title element of the $_itemArray property. It doesn't matter where you put this method in the class definition, but I prefer to keep methods in the same order as they're used, so put the following definition between setLastBuildDate() and generateXML():

public function setItemTitle($columnName)
{
  $this->_itemArray['title'] = $columnName;
}

The setter method for the <description> element is similar, but the RSS 2.0 specification suggests that this element should be a summary, so you don't want to include the entire article if it's more than a couple of lines long. After all, the idea of an RSS feed is to attract visitors to a site; the <description> element should be a teaser. Unless you have a separate database column for this element, you need to extract the first couple of sentences only. Add the following definition after the one for setItemTitle():

public function setItemDescription($columnName, $numSentences =
2)
{
  $this->_itemArray['description'] = $columnName;
  $this->_numSentences = $numSentences;
}

This takes two arguments: the name of the database column to be used for the <description> element, and the number of sentences you want to extract from the beginning of the text. I have given the second argument a default value of 2, so it becomes optional. The value is assigned to a new protected property called $_numSentences, which you need to add to the list of property definitions at the top of the page. I'll come back later to the extraction of the sentences from the beginning of the text.

Building the <link> and <pubDate> nodes is relatively complex, so before dealing with them, let's turn our attention to building the SQL query. Once you see how the query is built, the code in the remaining methods will make more sense. The query needs to know which table to draw the data from, so you need to create a property for the table name and a setter method for it. You also need to set the maximum number of records to be retrieved.

Add $_tableName and $_maxRecords as protected properties to the list of definitions at the top of the page, and insert the combined setter method for both properties after the definition for setItemDescription(). The code looks like this:

public function setTable($tableName, $maxRecords = 15)
{
  $this->_tableName = $tableName;
  $this->_maxRecords = is_numeric($maxRecords) ? 

    (int) abs($maxRecords) : 15;
}

The conditional operator (?:) tests the value passed in as the second argument. If it's a number, the value is assigned to the $_maxRecords property. Since is_numeric() accepts fractions and negative numbers, the value of the second argument is cast to an integer and passed to the abs() function, which converts a negative number into its positive equivalent. If the second argument isn't a number, the default value of 15 is used.

You're now ready to build the SQL query. This method is used internally by the class, so it needs to be protected. I usually keep all protected methods together after the public ones. Add the following code to the bottom of the class definition, just inside the closing brace:

protected function buildSQL()
{
  if (!isset($this->_tableName)) {
    throw new LogicException('No table defined. Use setTable().');
  }
  if (!isset($this->_itemArray['description']) && 

    !isset($this->_itemArray['title'])) {
    throw new LogicException('RSS items must have at least a 

      description or a title.');
  }
  // Initialize an empty array for the column names
  $select = array();
  // Loop through the $_itemArray property to build the list of aliases
  foreach ($this->_itemArray as $alias => $column) {
    $select[] = "$column AS $alias";
  }
  // Join the column/alias pairs as a comma-delimited string
  $select = implode(', ', $select);
  // Build the SQL
  $this->_sql = "SELECT $select FROM $this->_tableName";
  // Add a LIMIT clause if $_maxRecords is not 0
  if ($this->_maxRecords) {
    $this->_sql .= " LIMIT $this->_maxRecords";
  }
  // Display the SQL for testing purposes
  echo $this->_sql;
}

The foreach loop and the following line highlighted in bold do all the hard work for you. The loop iterates through the elements in the $_itemArray property, assigning the key to $alias and the value to $column. Inside the loop, the order of the variables is reversed, and they're inserted into a double-quoted string with the SQL keyword AS before being added to an array called $select. If the current element of the $_itemArray property looks like this:

$this->_itemArray['description'] = 'article';

it's added to $select in this form:

article AS description

$select is then joined as a comma-delimited string by implode() before being incorporated into the $_sql property.

The query is built in two parts: the first part selects the columns from the table, while the second part adds a LIMIT clause if the $_maxRecords property is not 0. Separating the LIMIT clause like this means you can retrieve all records by setting the second argument of setTable() to 0. Make sure you leave a space at the beginning of the LIMIT clause to avoid a SQL syntax error when both halves of the query are joined together.

Finally, the buildSQL() method displays the $_sql property onscreen for testing purposes.

Before you can test the revised class, the generateXML() method needs to call the buildSQL() method. Insert it after the series of conditional statements that check for errors and just before the code that gets the last build date. The code is shown here in bold with the surrounding lines for context:

if ($error) {
  throw new LogicException('Cannot generate RSS feed. Check the 

    following item(s): ' . implode(', ', $error) . '.');
}
if (is_null($this->_sql)) {
  $this->buildSQL();
}
if ($this->_useNow) {
  $lastBuildDate = date(DATE_RSS);
}

I have wrapped the call to the buildSQL() method in a conditional statement that checks whether the $_sql property is null. This allows you to set the SQL manually using the setQuery() method inherited from the parent class. The only reason you might want to do this is if you need to join two or more tables in the query.

Save RssFeed.php, and amend the try block in generate_rss.php like this (the code is in generate_rss_02.php):

try {
  $xml = new Pos_RssFeed('localhost', 'psadmin', 'kyoto', 'phpsolutions');
  $xml->setFeedTitle('OOP News');
  $xml->setFeedLink('http://www.example.com/oop_news.xml');
  $xml->setFeedDescription('Get the lowdown on OOP and PHP.');
  $xml->setLastBuildDate(true);
  $xml->setFilePath('oop_news.xml');
  $xml->setItemTitle('title');
  $xml->setItemDescription('article');
  $xml->setTable('blog')
  $result = $xml->generateXML();
  if ($result) {
    // echo 'XML file created';
  }
  else {
    echo 'Error';
  }
}

This sets the columns that you want to use for the <title> and <description> nodes, as well as the table from which they are to be drawn. If you are using your own database, substitute the names of the columns and tables with the ones you want to use.

Note also that I have commented out the line that displays XML file created. This is because I want to display only the $_sql property.

Save generate_rss.php, and load it into a browser (or use generate_rss_02.php). You should see the SQL query displayed onscreen as shown in Figure 9-5. Make sure that the column names and aliases are correct.

Figure 9.5. The SQL query uses aliases to give each column the right name for use in the RSS feed.

Change the arguments to the setTable() method by adding 0 as the second argument like this:

$xml->setTable('blog', 0);

Save the page, and test it again. This time, the LIMIT clause should be omitted.

Change the second argument to a number other than 15, and test the page again. The LIMIT clause should be restored, but with the new number.

If anything goes wrong, you can compare your class definition so far with RssFeed_02.php in the ch9_exercises folder.

Insert the following code in the Pos_RssFeed class definition between the setItemDescription() and setTable() methods:

public function setItemPubDate($columnName, $type = 'MySQL')
{
  $this->_itemPubDate = $columnName;
  $rssFormat = '%a, %d %b %Y %H:%i:%S';
  if (stripos($type, 'MySQL') === false) {
    $this->_itemArray['pubDate'] = "FROM_UNIXTIME($columnName, 

      '$rssFormat')";
  } else {
    $this->_itemArray['pubDate'] = "DATE_FORMAT($columnName, 

      '$rssFormat')";
  }
}

If the second argument is anything other than its default value, the pubDate element of the $_itemArray property wraps the column name in the MySQL FROM_UNIXTIME() function; otherwise, it uses the DATE_FORMAT() function. Both functions use the same formatting characters, which are stored in $rssFormat to format the output in the way stipulated by the RSS 2.0 specification minus the time zone offset.

Because the value of $_itemArray['pubDate'] contains more than just the column name, the setter method needs to store the column name separately in another property: $_itemPubDate. Add this as a protected property to the list of properties at the top of the page.

You can now use this property to add an ORDER clause inside the first half of the SQL query. Amend the section of the buildSQL() method shown here in bold:

$select = implode(', ', $select);
// Build the SQL
$this->_sql = "SELECT $select FROM $this->_tableName
               ORDER BY $this->_itemPubDate DESC";
// Add a LIMIT clause if $_maxRecords is not 0
if ($this->_maxRecords) {
  $this->_sql .= " LIMIT $this->_maxRecords";
}

To test the setItemPubDate() method, amend generate_rss.php by calling it between the setItemDescription() and setTable() methods like this (the code is in generate_rss_03.php):

$xml->setItemDescription('article');
$xml->setItemPubDate('updated');
$xml->setTable('blog');

Save both pages, and load generate_rss.php (or generate_rss_03.php) into a browser. You should see output similar to Figure 9-6.

Figure 9.6. The column used for <pubDate> requires special handling in the SQL query.

Make sure that the SQL query is formatted correctly. This is one area where things are likely to go wrong if you get the wrong combination of single and double quotes. A common mistake is to leave a gap between the MySQL function and the opening parenthesis. The problem won't show up until you attempt to run the query, but now is the time to track down any errors.

Amend the code in step 4 to add a second argument to setItemPubDate(). It doesn't matter what you add, but I used 'ts' (for timestamp) like this:

$xml->setItemPubDate('updated', 'ts');

Save the page, and test it again. This time, the updated column should be wrapped in the FROM_UNIXTIME() function.

If anything goes wrong, you can compare your code with RssFeed_03.php in ch9_exercises.

As with the <pubDate> elements, you need a separate property to refer to the <link> column name. You also need a Boolean property that indicates whether the <link> column contains a URL, or whether the class needs to build one. Begin by adding the two protected properties highlighted in bold to the list at the top of the class definition:

protected $_feedTitle;
protected $_feedLink;
protected $_feedDescription;
protected $_useNow;
protected $_itemArray;
protected $_numSentences;
protected $_maxRecords;
protected $_itemPubDate;
protected $_itemLink;
protected $_useURL;

The setter method for a link that's stored in a database column is very similar to those for the <title> and <description> elements. Add the following code after the setItemPubDate() method that you defined in the previous section:

public function setItemLink($columnName)
{
  if (isset($this->_useURL)) {
    throw new LogicException('The methods setItemLink() and 

      setItemLinkURL() are mutually exclusive. Use one or the other.');
  }
    $this->_itemArray['link'] = $columnName;
    $this->_useURL = false;
}

The method begins by checking whether the $_useURL property has been set. Although the property is defined at the top of the script, it has no value, so isset() returns false unless a value has been assigned elsewhere. The only time this would happen is if you use both setItemLink() and setItemLinkURL() (you'll define the second method in the next step). Since they're mutually exclusive, you need to throw an exception if this happens.

Then the column name is assigned to the link element of the $_itemArray property, and the $_useURL property is set to false.

The method that builds a URL with the primary key is, naturally, more complex. Insert the following code immediately after the preceding method, and I'll explain how it works.

public function setItemLinkURL($url)
{
  if (isset($this->_useURL)) {
    throw new LogicException('The methods setItemLink() and 

      setItemLinkURL() are mutually exclusive. Use one or the other.');

}
  if (!isset($this->_tableName)) {
    throw new LogicException('You must set the table name with 

      setTable() before calling setItemLinkURL().');
  }
  parent::usePrimaryKey($this->_tableName);
  if (is_array($this->_primaryKey)) {
    $this->_primaryKey = $this->_primaryKey[0];
  } else {
    throw new RuntimeException("Cannot determine primary key for 

      $this->_tableName.");
  }
  $this->_itemArray['link'] = $this->_primaryKey;
  $this->_itemLink = $url . "?$this->_primaryKey=";
  $this->_useURL = true;
}

The method takes one argument: the URL that you want to use in combination with the record's primary key. Two conditional statements check that it's OK to proceed. The first is the same conditional statement as in setItemLink(), and it prevents the mutually exclusive methods from being used in the same script. The second conditional statement checks that the $_tableName property has been set by the setTable() method. This is necessary because you need the table name to find its primary key.

Assuming everything is OK, the method passes $_tableName to parent:: usePrimaryKey(). Although usePrimaryKey() is inherited from the parent class, I plan to disable it in this class to prevent the wrong primary key from being selected by accident.

As you might recall from Chapter 8, the usePrimaryKey() method returns the name(s) of the primary key column(s) as an array. In the case of a blog or news site, the table should not have a composite primary key, so you're interested in only the first array item.

Once the name of the primary key column has been found and assigned to the $_primaryKey property, the final section of the method prepares the SQL query and builds the URL. These lines are important, so let's take a look at them individually. The following line assigns the name of the primary key column to the link element of the $_itemArray property:

$this->_itemArray['link'] = $this->_primaryKey;

This passes the name of the primary key column to the SQL query with link as its alias.

The next line builds the URL and assigns it to the $_itemLink property like this:

$this->_itemLink = $url . "?$this->_primaryKey=";

$url is the URL passed as an argument to the method. The rest of the line concatenates a query string on the end of the URL. Let's say the argument passed to setItemLinkURL() is this:

http://www.example.com/detail.php

If the primary key column is called article_id, the value of $_itemLink evaluates to this:

http://www.example.com/detail.php?article_id=

The value of article_id is later added dynamically to this URL when the generateXML() method loops through the database results.

The final line sets the Boolean $_useURL property to true. This tells the generateXML() method to use the base URL stored in $_itemLink.

Quite a lot is going on in those few lines!

As I said earlier, I want to prevent anyone from setting the wrong primary key by accident, so add this code immediately after the setItemLinkURL() method:

public function usePrimaryKey()
{}

This is an empty method. It accepts no arguments and does nothing. The reason for creating it is that you can't change the visibility of an inherited public method. Ideally, I would have liked to make usePrimaryKey() protected in the child class, but doing so triggers a fatal error. Overriding the method like this renders it harmless, but the parent method is still accessible through the parent keyword.

Save the class definition, and test the setItemLinkURL() method by adding the following line to generate_rss.php (the code is in generate_rss_04.php):

$xml->setTable('blog');
$xml->setItemLinkURL('http://www.example.com/detail.php');
$result = $xml->generateXML();

Save generate_rss.php, and load it into a browser (or use generate_rss_04.php). You should see output similar to Figure 9-7. You can't see the URL, but the table's primary key column has been added to the SQL query with link as its alias.

Figure 9.7. The SQL query is now ready to generate the data for the <item> child elements.

Test the setItemLink() method by commenting out the line you added in step 5 and adding the following code:

$xml->setTable('blog');
// $xml->setItemLinkURL('http://www.example.com/detail.php');
$xml->setItemLink('url');
$result = $xml->generateXML();

This time, the SQL should contain url AS link.

Check that the SQL query is being built correctly, and then comment out this line at the end of the buildSQL() method:

// echo $this->_sql;

I recommend disabling the line rather than removing it completely, because you might need to display the SQL query later for debugging.

If you encounter any problems, compare your code with RssFeed_04.php in ch9_exercises.

To use the Pos_Utils::getFirst() static method, you need access to the class definition, so include it at the top of the Pos_RssFeed definition.

require_once 'XmlExporter.php';
require_once 'Utils.php';
class Pos_RssFeed extends Pos_XmlExporter

The remaining changes all go inside the generateXML() method. First, you need to run the SQL query. Add the following line highlighted in bold immediately after the query has been built:

if (is_null($this->_sql)) {
  $this->buildSQL();
}
$resultSet = $this->_dbLink->getResultSet($this->_sql);
if ($this->_useNow) {
  $lastBuildDate = date(DATE_RSS);
}

This is the same as in the parent class, so should need no explanation.

The $_useNow property determines whether the <channel> element <lastBuildDate> uses the current time or the time of the most recently updated <item>. Now that you have the results from the database, you can use LimitIterator to inspect the first row and get the time from the most recent item. Add an else clause to the conditional statement that assigns the value of $lastBuildDate like this:

$resultSet = $this->_dbLink->getResultSet($this->_sql);
if ($this->_useNow) {
  $lastBuildDate = date(DATE_RSS);
} else {
  foreach (new LimitIterator($resultSet, 0, 1) as $row) {
    $lastBuildDate = $row['pubDate'];
  }
}
$rss = new XMLWriter();

The SQL query orders the results with the most recently updated item first, so this takes the value of the pubDate field from the first result and assigns it to $lastBuildDate. Because the result set implements the Iterator interface, you can inspect the first row like this without needing to reset it manually before looping through the entire set of results later.

Create the loops to generate the XML for the <item> elements. Locate the following comment:

// Code to generate <item> elements goes here

Replace it with this code:

foreach ($resultSet as $row) {
  $rss->startElement('item');
  foreach ($row as $field => $value) {
    if ($field == 'pubDate') {
      $value = $this->getTimezoneOffset($value);
    } elseif ($field == 'link' && $this->_useURL) {
      $value = $this->_itemLink . $value;
    } elseif ($field == 'description') {

$extract = Pos_Utils::getFirst($value, $this->_numSentences);
      $value = $extract[0];
    }
    $rss->writeElement($field, $value);
  }
  $rss->endElement();
}