Monday, 09 October 2017 02:18

K2 Comment Notification

Rate this item
(0 votes)

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 = "This email address is being protected from spambots. You need JavaScript enabled to view it."; // toEmail = What email address it is sent to $toEmail = "This email address is being protected from spambots. You need JavaScript enabled to view it."; // 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> <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!

Read 82 times Last modified on Monday, 09 October 2017 05:15

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.