UDT to populate News field definition on NewsArticleAdded

[appleyard]

Hopefully someone can help me out or point me in the right direction...

I'm writing a UDT that will fire on the event NewsArticleAdded to:
1) generate a short URL via YOURLS
2) generate a tweet using Twitter's API that uses the short URL

No problem so far. However, I want CMSMS to *remember* the short URL in that article (to use in a "Tweet" button, for example).

It seems like the most obvious way to do this is via a News Field Definition called, say, "shorturl." This way I could even go back and add short URLs to older articles (before I started the URL shortening). But is there a way to populate that field in a UDT?

Thanks!

[appleyard]

For what it's worth, my Field Definition "shorturl" has id "4" in the MySQL table "cms_module_news_fielddefs."

[Wishbone]

Do you know how to read and update database tables from a UDT? I don't think that News has a robust API like FEU does for reading and updating data. It seems that you would have to read the table corresponding with News field definition values, searching for the field name (or id) and the article id. If the value isn't set, then generate a tiny url and add it to the table.

The issue is that since there probably isn't an API in the News module to do this, it's theoretically possible that the database tables might change in future news releases and this mechanism might fail (at best) or corrupt the table (at worst)

If you don't know the commands to do this, I might be able to come up with something tonight (late) or tomorrow.

[appleyard]

I've found some examples on the forums that use UDTs to update MySQL tables (here and here, for example) but none dealing with the News module in particular. I realize I'm probably waay out of my league, and the thought of overwriting database info is scary. Any help you can provide would be greatly appreciated!

[Wishbone]

I realize I'm probably waay out of my league, and the thought of overwriting database info is scary.

OK.. I'll look at it tonight.

I don't like doing 'live' tests with my database as well. When I test out a new procedure, I implement it on a mirror install of the real site. Once I validate that it works, I deploy it to the real site Maybe I'm paranoid, but it works.

[Wishbone]

OK.. Here's a code snippet for your UDT:

Code: Select all

global $gCms;
$db = $gCms->getDb();


# Some hard-codes

$field_name = 'shorturl';
$news_id = 2;  # Replace this with the value of the News ID being saved


# Get field definition id

$sql = "SELECT id FROM " . cms_db_prefix() . "module_news_fielddefs
           WHERE name='$field_name'
           LIMIT 1;";
$dbresult =& $db->Execute($sql);
if (!$dbresult || !$row=$dbresult->FetchRow()) {
  return; # Field Definition not found. Abort!
}
$fielddef_id = $row['id'];


# Field Definition found ($fielddef_id). Now see if news article has this field filled out

$sql = "SELECT value FROM " . cms_db_prefix() . "module_news_fieldvals
           WHERE news_id='$news_id'
           AND fielddef_id=$fielddef_id
           LIMIT 1;";
$dbresult =& $db->Execute($sql);
if ($dbresult && $row=$dbresult->FetchRow()) {
  return; # Field definition already filled out. Abort!
}

# Field definition empty. Insert something into this field

$shorturl = 'http://shorturl.com/12345'; # Replace this with the real shorturl
$now = $db->DbTimeStamp(time());
$sql = "INSERT INTO " . cms_db_prefix() . "module_news_fieldvals
           (`news_id`, `fielddef_id`, `value`, `create_date`, `modified_date`)
           VALUES (?, ?, ?, $now, $now);";
$dbresult = $db->Execute(
   $sql,
   array($news_id, $fielddef_id, $shorturl)
);

This code is split into three logical sections:

1) Find field definition id. If you know it already, you can just set $fielddef_id directly. It's best to look this up by name, as I did in the code so that you can add this code to another site, where the field definition might be a different id from the id=4 that you specified. If the field can't be found, it aborts.

2) Look to see if the field has a value for the news article in question. If it's already filled out, then abort.

3) Get your short url and insert it into the database. I hard-coded $shorturl to a bogus value, but you can replace it with the real value.

Note that if you are wanting to retrofit your old News articles, you might want to add your UDT to the NewsArticleEdited event as well as NewsArticleAdded that you mentioned above. The above code was tested in CMSMS 1.9.1 and whatever News version that was default at that time.

[appleyard]

ZOMG, this is awesome! Thank you so much!! Let me work on incorporating this in my site and I'll post the full UDT later. You're the best, Wishbone!