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`)

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:

global $my;
$username = "$my->username";

### / 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';";
while ($i < $query_num)

if ( $result == "yes" )

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"/>


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">
<META HTTP-EQUIV=Refresh CONTENT="3; URL=http://website">

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");

$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')";

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:

rm -Rf ${TMPDIR}
mkdir -p ${TMPDIR}
today=`date --date "yesterday" +%Y-%m-%d`

echo "<html>
<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.
<table border=1>
Message Date
Posted By
" > ${outfile}

echo "select a.catid,a.thread,from_unixtime(a.time),,a.subject,b.message from jos_fb_messages a,jos_fb_messages_text b where = b.mesid;" | ${mysql_line} | tr "\t" "|" | sed s/"\\\t"/"\&nbsp;"/g | sed s/"\\\n"/"<br>"/g | while read line;
 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}

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  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`
 # append SMTP header
 # replace SENDTO with the appropriate To:
 echo "HELO
 MAIL FROM: admin@website
 From: Daily Digest <admin@website>
 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
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.

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


    Hi Adam!

    Thanks for the helpful code. Sure wish the folks at Kunena would implement this important project.

    I'm not a Linux person, and I was able to follow your setup instructions except for the programmatic portion. What type of file is this and where would I put it? Your assistance would be greatly appreciated.

    Neighborhood Wanna B Geek


      Hi JM
      Do you mean the script that starts with "#!/bin/bash" ? That's called a bash shell script, and it's just a text file. If you want to edit it on linux, log in to your linux box and run these commands:

      $ cd /home/username
      $ nano
      Paste the code from my post here and save the file, taking you back to a prompt
      $ chmod +x

      You should now have a file on your system called /home/username/ that has the executable bit set (that's what the 'chmod' command did). In my example, I'm using the editor 'nano' to edit the file. There are many, many other linux editors. If you don't have nano, you can try "pico" or "joe". "vi" will surely be there, but it has quite the learning curve, and that's out of the scope of this article.

      Once you have the file in place, you need to tell your system to run it at a scheduled time. This is usually handled by the 'cron' facility. You tell cron what to run when by editing your user's 'crontab' file. Run the command 'crontab -e' to open up your user's crontab file in your default editor. Then enter a line like this:

      15 0 * * * /home/user/

      This tells cron to run the script at 00:15 everyday (15 minutes after midnight). Change the values to whatever you want.

      Hope this helps


    Oh my goodness! Incredible article dude! Thanks, However
    I am experiencing difficulties with your RSS. I don't
    understand the reason why I cannot subscribe to it.
    Is there anybody getting identical RSS problems? Anyone who
    knows the solution will you kindly respond?



    It's amazing to visit this web page and reading the views of
    all friends concerning this paragraph, while
    I am also keen of getting experience.

    Also visit my website :: Psn Code Generator Online


    Hiya very cool website!! Guy .. Beautiful .. Amazing ..
    I'll bookmark your web site and take the feeds
    also? I'm satisfied to find a lot of useful info right here in the publish, we need work out extra strategies
    in this regard, thank you for sharing. . . . . .


    2 TCO Glass (Thin Film Solar Module Substrate Glass) 6.
    I am always striving to bring a friendly andd professionalattitude combined with very high standard to the fielld of film &video production. ils sont gralement nanmoins sincre personnalis
    polos fait, mais un cot trs nettement rduit.


    Brown and his wife have two daughters, Ayla and Arianna.
    3 liter V8 was only backed by a 4-speed automatic transmission, limiting its efficiency
    and lagging behind when the Sierra and Silverado were fitted with 6-speed
    trans, increasing their MPGs. They organize sales and exhibitions from time to
    time in order to provide benefit to all car lovers.


    Success begets success, to more than a small degree.
    It is people who complicate it more than it really is.
    That is where trading becomes a boring process but eventually it brings very
    good profits. This is the best choice in binary options trading system as shorter the time lesser the fluctuation. You will have underlying asset options and the
    current price of your asset will be listed on the screen. Along
    these lines, Gotoption has officially settled that the brokers that offer the best administrations for US binary options trading are those that are
    spotted in different nations, ideally in Europe.


    Generally I do not learn article on blogs, but I would like to say that this write-up very pressured me to check out and
    do so! Your writing style has been surprised me. Thank you, very nice post.

 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=""> <strike> <strong>