Today, I was messing around with the K2 Joomla Plugin. I got it working and everything was great. The only problem I found is if someone leaves a comment on an article, no one will know unless the page is visited again. I searched around and found a plugin that cost some moolah, but I didn’t want to go that route. Another one I found was not available for downloading. So, away I went on a development hunt to figure out what I could do.
I ended up writing my own PHP script. It creates a new column in K2’s comment table. When the script runs, it looks for 0’s in the new column and sends an email with the information it finds in the record. It’s not a perfect solution, but it works for me. I set it to run once a day so I’ll receive all the comments, in separate emails, daily.
It’s not a Joomla plugin, but it is still really easy to install. I have two .php files. One for authenticating and the other is the script. I did it this way in case someone wants to put the script in the ‘public_html’ folder. However, I recommend creating a folder within the Home folder so it’s not accessible from the Internet and run it as a cron job.
For my example below, I’ll use the folder ‘K2Send’. Let’s begin:
Create a folder to hold two .php files.
/home/<<user name>>/K2Send
Create two files within this folder (ex. K2Send.php and K2SendAuth.php)
Copy the following into the K2SendAuth.php file and then edit it to reflect your server and database information:
<php
// Information for K2 Send Comments
$dbserver = 'localhost';
$dbuser= 'Database User';
$dbpass = 'Database Password';
$db = 'Database';
$dbtable = "K2 comments Table";
// This is the name of the column that will be added to the K2 Comment Table.
// It can be labeled anything
$dbcolumn = "Notify";
// fromEmail = What email address it is sent from
$fromEmail = "change@email.com";
// toEmail = What email address it is sent to
$toEmail = "change@email.com";
// Server domain where K2 is running
$fromdomain = "mywebdomain.com";
// textEmail =0 (email is formatted HTML)
// textEmail = 1 (email is formatted Text)
$textEmail = "0";
?>Now that you have the auth file done, open up the K2Send.php file and paste the following in it and edit the ‘require_once’ line to reflect the path:
<?php
/*
To Do....
1. Check if the notification column is in the K2 comments table
2. If not, create it
3. Check if column contains a 0
4. If so, send email
5. Replace the 0 with a 1
*/
//Require Authenication File
require_once "/home/---UserNAME---/K2Send/K2SendAuth.php";
$servername = $dbserver;
$username = $dbuser;
$password = $dbpass;
$dbname = $db;
$columnexist = 0;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->;connect_error) {
die("Connection failed: " . $conn->;connect_error);
}
// 1. Check if the notification column is in the K2 comments table
$sql = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_NAME`='{$dbtable}'";
$result = $conn->;query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
if ($row["COLUMN_NAME"]==$dbcolumn)
{
echo "This column- ".$dbcolumn." - Exists!\n\n";
$columnexist=1;
}
}
if ($columnexist==0)
{
// 2. If not, create it
$add = "ALTER TABLE ".$dbtable." ADD ".$dbcolumn." INT( 11 ) NOT NULL";
$result = $conn->query($add) or die(mysqli_error($conn));
}
}
else
{
echo "0 results";
}
// 3. Check if column contains a 0
$sql = "SELECT * FROM ".$dbtable;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
/* echo "-". $row["id"]."-".$row["userName"]."-".$row["commentDate"].
"-" . $row["commentText"]. "-" . $row["commentEmail"]. "-" . $row["commentURL"]. "-" .
$row["published"]. "-" . $row[$dbcolumn]."\n\n";
*/
echo "Searching Comments Table.\n\n";
if ($row[$dbcolumn]==0)
{
// 4. If so, send email in text or HTML
// $textEmail = 0 (HTML)
echo "Found a new comment.\n\n";
if ($textEmail == 0)
{
$message = "
<html>
<head>
<title>K2 Commment</title>
</head>
<body>
<p>New K2 Comment</p>
<table>
<tr>
<th>id</th>
<th>Item</th>
<th>User</th>
<th>User Name</th>
<th>Date</th>
<th>Comment</th>
<th>User Email</th>
<th>User URL</th>
</tr>
<th>id</th>
<th>Item</th>
<th>User</th>
<th>User Name</th>
<th>Date</th>
<th>Comment</th>
<th>User Email</th>
<th>User URL</th>
</tr>
<tr>
<td>".$row["id"]."</td>
<td>".$row["itemID"]."</td>
<td>".$row["userID"]."</td>
<td>".$row["userName"]."</td>
<td>".$row["commentDate"]."</td>
<td>".$row["commentText"]."</td>
<td>".$row["commentEmail"]."</td>
<td>".$row["commentURL"]."</td>
</tr>
</table>
</body>
</html>
";
// Always set content-type when sending HTML email
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
}
else
{
//Send Text Email
$message = "New K2 Comment\nID = ".$row["id"]."\n ItemID = ".$row["itemID"].
"\n UserID = ".$row["userID"]."\n User Name= ".$row["userName"]."\n Date = ".
$row["commentDate"]."\n Comment = ".$row["commentText"]."\n User Email = ".
$row["commentEmail"]."\n User URL = ".$row["commentURL"]."\n";
}
$to = $toEmail;
$subject = "K2 Comments from ".$fromdomain;
// More headers
$headers .= 'From: '.$fromEmail . "\r\n";
// $headers .= 'Cc: <<Put another EMAIL Address>>' . "\r\n";
mail($to,$subject,$message,$headers);
// 5. Replace the 0 with a 1
$sql = "UPDATE {$dbtable} SET {$dbcolumn}=1 WHERE id={$row["id"]}";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully\n\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
echo "No new comment found.\n\n";
}
} else {
echo "0 results";
}
$conn->close();
echo "Exiting...\n\n";
?>Add a cron job to run the K2Send.php file as often as you like.
Post a comment if you see any errors.
Enjoy!