May 132010
 

Kunena is a common freely available add-on for the Joomla content management system that gives you the ability to add a message board or forum to your joomla website, with moderation, threads, document uploads, and many other features. The only main feature that seems to be missing is the ability to send all users a regular digest of postings made over a certain period of time. Daily digest functionality exists in other forum software, like Yahoo! Groups and others, and a number of people on the Kunena message boards have talked about wanting such a feature.

I recently helped a local user group migrate from a Yahoo! Groups page to their own website running Joomla for content management and Kunena for forum functionality. They really like the daily digest option Yahoo! offered, so I figured out a way to implement that in the Kunena forum software.

Disclaimer: I’m not a Joomla developer, and this solution has really nothing to do with Joomla. I’m a Linux systems administrator, so my preferred method of approaching this problem was to write a script that would query the database directly, finding the posts made in the last 24 hours, putting them in an html page, and then emailing that page to all the registered users. So this solution is not as simple as ‘install this joomla module’, or ‘check this configuration option’. But it does work very well.

First, I wanted to give the users the ability to opt-out of the email if they wished. Rather than modify one of the existing joomla tables to store this opt-out option, I created a new mysql table:

mysql> describe digest;

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| username      | varchar(100) | NO   | PRI | NULL    |       |
| receivedigest | char(5)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
2 rows in set (0.25 sec)

Here is the sql used to create this table:

CREATE TABLE `digest` (
`username` varchar(100) NOT NULL,
`receivedigest` char(5) NOT NULL,
PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The thinking behind this is that everyone is going to get the digest by default. If they choose to opt-out, I will put their username in this table with a value of ‘no’ for the receivedigest field. If they change their mind and want it again, I can either erase them from the table, or change the value to ‘yes’.

Now, you need to create the page that people will use to manage their preferences. I created a directory called “custom” underneath the main directory of my Joomla install to store custom php scripts that are called within Joomla. Here is the first, digest.php:

<html><body>
<?php
global $my;
$username = "$my->username";
$dbusername="SQLUSER";
$password="SQLPASSWORD";
$database="SQLDATABASE";
$dbserver="SQLSERVER";
$dbtable="digest";

### / Initial mysql connection
mysql_connect($dbserver,$dbusername,$password) or die (" Can't connect");
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM $dbtable where username = '$username';";
$query_result=mysql_query($query);
$query_num=mysql_numrows($query_result);
$i=0;
$result="yes";
while ($i < $query_num)
{
 $result="";
 $result=mysql_result($query_result,$i,"receivedigest");
 $i++;
}

if ( $result == "yes" )
{
 $checked="checked";
}
else
{
 $checked="";
}

print "Hello $username!<br><br>";
?>

<form name="digest" id="digest" action="custom/digest_submit.php" method="POST">
<input type="hidden" name="username" value="<?php print "$username" ?>">
<input <? echo "$checked"; ?> name="receive_digest" TYPE="CHECKBOX" VALUE="yes">Check this to receive the daily digest via email<br>
<br><input type="submit" value="Submit"/>

</body>
</html>

Once the user has made their decision, they submit the form which submits to this page:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en" xml:lang="en">
<head>
<META HTTP-EQUIV=Refresh CONTENT="3; URL=http://website">
</head>
<?php
$dbusername="SQLUSER";
$password="SQLPASSWORD";
$database="SQLDATABASE";
$dbserver="SQLSERVER";
$dbtable="digest";

mysql_connect($dbserver,$dbusername,$password) or die ("Sorry! I couldn't connect to the database! Please try again later");
@mysql_select_db($database) or die( "Unable to select database");

# User submitted variables
$insert_date=date("Y-m-d H:i:s");
$count=1;

$username= mysql_real_escape_string($_POST["username"]);
$receive_digest= mysql_real_escape_string($_POST["receive_digest"]);
if ( $receive_digest == "" )
{
 $receive_digest = "no";
}

$insert = "REPLACE INTO $dbtable ( username, receivedigest ) VALUES ( '$username','$receive_digest')";
$result=mysql_query($insert);
mysql_close();

print "<h2>Preferences Updated.</h2>";
?>

Now that you have a preference page, it’s time to actually write the program that find the daily posts and sends the email. Here it is:

#!/bin/bash
TMPDIR=/tmp/digest.$$
rm -Rf ${TMPDIR}
mkdir -p ${TMPDIR}
outfile="${TMPDIR}/digest.html"
outfile2="${TMPDIR}/digest2.html"
email_file="${TMPDIR}/email"
today=`date --date "yesterday" +%Y-%m-%d`
mysql_line="mysql -s -uSQLUSER -pSQLPASSWORD -hSQLSERVER SQLDATABASE"

echo "<html>
<body>
<h2>Message Board Digest for ${today}</h2>
You are receiving this email because you are subscribed to the daily digest of the message board. To change your subscription, log into <a href=\"http://website\">website</a> and click \"Daily Digest Options\" from the User Menu.
<br>
<hr>
<table border=1>
<tr>
<td>
Message Date
</td>
<td>
Posted By
</td>
<td>
Subject
</td>
</tr>
" > ${outfile}

echo "select a.catid,a.thread,from_unixtime(a.time),a.name,a.subject,b.message from jos_fb_messages a,jos_fb_messages_text b where a.id = b.mesid;" | ${mysql_line} | tr "\t" "|" | sed s/"\\\t"/"\&nbsp;"/g | sed s/"\\\n"/"<br>"/g | while read line;
do
 catid=`echo ${line} | cut -d"|" -f1`
 msgid=`echo ${line} | cut -d"|" -f2`
 msgdate=`echo ${line} | cut -d"|" -f3`
 user=`echo ${line} | cut -d"|" -f4`
 subject=$(echo ${line} | cut -d"|" -f5 |  sed -r s/"\\\\"/""/g)
 message=$(echo ${line} | cut -d"|" -f6 |\
 sed -r s/"url\]"/"url]\n"/g |\
 sed -r s/"\[\/url\]"/"<\/a>"/g |\
 sed -r s/"\[url=(.+)\]"/"<a href=\"\\1\">"/g |\
 sed -r s/"img\]"/"img]\n"/g |\
 sed -r s/"\[\/img\]"/"\">"/g |\
 sed -r s/"\[img\]"/"<img src=\""/g |\
 sed -r s/"\[b\]"/"<b>"/g |\
 sed -r s/"\[\/b\]"/"<\/b>"/g |\
 sed -r s/"\\\\"/""/g)
 if [[ "${msgdate}" =~ ${today}.+ ]]; then
  echo "<tr><td>${msgdate}</td><td>${user}</td><td><a href=\"#${msgid}\">${subject}</a></td></tr>" >> ${outfile}
  echo "<hr><a name=\"${msgid}\">${msgdate} from ${user}</a><br><b><a href=\"http://website/message-board/${catid}/${msgid}\">${subject}</a></b><br>${message}<br><br>" >> ${outfile2}
 fi
done

echo "<hr>" >> ${outfile2}
echo "</table><br>" >> ${outfile}
cat ${outfile2} >> ${outfile}
echo "</body></html>" >> ${outfile}

# figure out who to send the digest to
# for some reason, I stored the table that indicates whether or not a user wants to get the digest in a
# different database than the joomla site was stored in. The first database is "DB1", the joomla database is "DB2"
# modify accordingly for your setup
echo "select DB2.jos_users.email  from DB2.jos_users LEFT JOIN DB1.digest on DB2.jos_users.username = DB1.digest.username where (DB1.digest.receivedigest <> 'no' or DB1.digest.receivedigest IS NULL) and DB2.jos_users.username <> 'admin';" | ${mysql_line} > ${TMPDIR}/list_to_email

for item in `cat ${TMPDIR}/list_to_email`
do
 # append SMTP header
 # replace SENDTO with the appropriate To:
 echo "HELO myserverhostname.com
 MAIL FROM: admin@website
 RCPT TO: SENDTO
 DATA
 From: Daily Digest <admin@website>
 To: SENDTO
 Subject: Daily Digest for $today
 MIME-Version: 1.0
 Content-Type: text/html; charset=us-ascii
 Content-Transfer-Encoding: 7bit" | sed "s/SENDTO/${item}/g" > ${email_file}
 # need an extra newline to conform to HTTP protocol
 # this separates the header from the content
 echo "" >> ${email_file}
 cat ${outfile} >> ${email_file}
 # Tell the mail server we are done
 echo ".
quit" >> ${email_file}
 echo "sending to ${item}"
 cat ${email_file} | /usr/bin/nc localhost 25 1> /dev/null
 sleep 1
done
rm -Rf ${TMPDIR}

This script gets every Joomla user (except those who have opted out) and emails them the html file created for the last day of messages. I run this out a user’s cron sometime after midnight (since it’s querying for yesterday). And it works and everyone is happy!
Still todo:

  • Handle the formatting in the body of the ‘psuedo-html’ that kunena uses (BBCode)
  • Make the opt-out form more AJAX-y
  • More direct links from the digest into individual threads and users

I know this isn’t the easiest thing to implement, but if you’re not that familiar with Linux, find someone who can help you set this up. Until Kunena comes out with a quicker way to do this, this is probably your best bet.

  64 Responses to “How to add a daily digest email to Kunena forums (Joomla)”

  1.  

    Hi to all, the contents present at this site are in fact awesome for
    people knowledge, well, keep up the nice work fellows.

  2.  

    The upgraded program has more than 5318 individual training
    sessions as well as mindful scrutiny of greater than 723
    medical research papers.

  3.  

    Marvelous, what a website it is! This weblog presents useful facts to us, keep it
    up.

  4.  

    There is a pull down console with cup holders, but no other
    convenience features are included standard. A leading supplier ships to
    locations within the United States and U. At $62,859, you are approaching the rarified air that was formerly breathed only by General Motors vehicles such as Cadillacs and Corvettes.

  5.  

    Appreciation to my father who told me concerning this blog, this webpage
    is in fact amazing.

  6.  

    Hі t&#1211ere! Ι қnow t&#1211iѕ іѕ kin оf &#1086ff topic buut &#8544 ѡаѕ
    wondering which blog platform ɑгe ʏou using for thіs website?
    І'm ǥetting fed &#6489р оf WordPress ƅecause ӏ'νе &#1211ad &#11427roblems աith hackers and І'm
    &#8572ooking at alternatives for another platform.
    Iwould bbe fantastic іf yօu сould &#11427oint
    mе inn tɦе direction of a
    good platform.

    &#8556ߋоk inro myy blog: dien dan rao vat

  7.  

    This tiny circle rolls around your home, sucking up dirt and dust, while you relax
    and enjoy your newly clean space. A Virtual Wall Lighthouse can be set to Virtual Wall mode or
    Lighthouse mode. Consequently no matter whether you go out to
    be effective or even spending some time together with your family outside the house, you can rely on this kind of flooring clean to complete the task for you personally.

  8.  

    The styling, inside and out, is what makes the big difference.

    Vehicle Models underneath some of the bare minimum difficult injury claims include.

    Research a company that offers all of the above in reliable retractable tie downs.

  9.  

    The Double-Cab doors are hinged to the front so that all four doors open the same
    way, which allows much easier access to the rear.
    Vehicle Models underneath some of the bare minimum difficult injury claims include.
    My wife & I took that 1975 model GMC 1500 on an extended road trip from Texas, through New Mexico and Arizona,
    and to California.

  10.  

    Hello There. I found your blog the use of msn. This is
    a really well written article. I'll be sure to bookmark it and come
    back to learn more of your helpful info. Thank you for
    the post. I'll certainly comeback.

  11.  

    hello!,I love your writing very so much!
    share we keep in touch more about your article on AOL? I require a specialist on this area to unravel
    my problem. May be that's you! Having a look forward to peer you.

  12.  

    I feel this is one of the so much significant information for me.
    And i am glad studying your article. But wanna statement on some common things, The website style
    is great, the articles is truly great : D.
    Excellent activity, cheers

  13.  

    I am in fact pleased to glance at this website posts which includes tons
    of useful data, thanks for providing these information.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)