help integrating a auto email php file into opencart

ianhaney50 · July 25, 2015

Hi

I am using opencart to make a website that allows users to order a service for car tax, mot and insurance reminders, it all works perfect but need to integrate the email reminder and SMS reminder php coding into opencart

I have made the car tax expiry date, car mot expiry date and car insurance expiry date as a product option and then has a date field so they can enter the expiry date, all works perfect and gets stored in the database when I did a test order so I just need bit of help on how to get the expiry date and option title from the database and displayed within the email php coding

I found the oc_order_option table in the database with the date and name but how do I get that from the database and displayed within a email, below is my coding for the email reminder php file

I uploaded the file to the web server and got the following error on the page

Unknown column 'value' in 'order clause'

The value though is the column name in the oc_order_option within the database

The updated code is below

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<html>
<title>Automatic Email</title>
<body>

<?php

$db = mysqli_connect("" , "", "") or die("Check connection parameters!"); 
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname)  
mysqli_select_db($db,"") or die(mysqli_error($db));

if (mysqli_connect_error()) {
    die ('Failed to connect to MySQL');
} else {
   /*SUCCESS MSG*/
   echo '';
}

$sqlCommand = "SELECT 
        order_option_id
        , name
        , DATE_FORMAT(value, '%e %M %Y')
        FROM oc_order_option
            INNER JOIN oc_order u USING (order_id)
            INNER JOIN oc_order v USING (customer_id)
        WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY
            AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 14 DAY

        UNION

        SELECT 
        order_option_id
        , name
        , DATE_FORMAT(value, '%e %M %Y')
        FROM oc_order_option
            INNER JOIN oc_order u USING (order_id)
            INNER JOIN oc_order v USING (customer_id)
        WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
            AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 7 DAY
        ORDER BY order_option_id, value";

$query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db));

//fetch tha data from the database 
/*while ($row = mysqli_fetch_array($query)) {

echo "<br><br>";
echo $row['id'];
echo "<br><br>";
echo $row ['name'];
echo "<br><br>";
echo date("d F Y",strtotime($row['datedue']));
  echo "<br />";

}*/

$current_visitor=0;
$email = '';
$headers = "From: noreply@taxelephants.uk\r\n";    
$subject = "Expiry Date(s)";
$message = '';
$renewals = array();
$notifications = array();

//fetch the data from the database 
while ($row = mysqli_fetch_array($query)) {
   
   // has visitor_id changed
   if ($row['id'] != $current_visitor) {
      // send email to current visitor
      if ($current_visitor != 0) {
         $to = $email;
         $sendmail = mail($to, $subject, $message, $headers);
         if ($sendmail) {
            echo nl2br($message);
             echo "<b>Email Successfully Sent</b><br><br>";
             // success, so add renewal ids to notifications
             $notifications = array_merge($notifications,$renewals);
         } else { 
             echo "<b>Error in Sending of Email to $to</b><br><br>";
         }
      }
      $current_visitor = $row['id'];
      $email = $row['email'];
      $message = "Name: {$row['name']} \n\n";
      $renewals = array();
   }
   $message .= "{$row['value']} expiry date: {$row['value']}\n";

}
// send email to final visitor
if ($current_visitor != 0) {
   $to = $email;
   $sendmail = mail($to, $subject, $message, $headers);
   if ($sendmail) {
      echo nl2br($message);
      
      echo "<b>Email Successfully Sent</b><br><br>";
      // success, so add to notifications
      $notifications = array_merge($notifications,$renewals);
   } else { 
      echo "<b>Error in Sending of Email to $to</b><br><br>";
   }
}

// Free the results  
mysqli_free_result($query);

//close the connection
mysqli_close($db);
?>

</body>
</html>

I have asked the question in the opencart forum ages ago but have not got any reply

ianhaney50 · July 25, 2015

Sorry got a update, I have sorted the error, it was in my ORDER clause as I actually read the error and took value out from the order clause, so am now error free and the page is blank white now but thought it would output info on the page as got echo and the php variables in, I am just going to try a var_dump of the sql to see if the query is working and will post a update in a min

ianhaney50 · July 25, 2015

I have done a var_dump($sqlCommand); and outputted the query which is good as guess is working, I just thought I am going to input it directly in phpmyadmin and see if it returns anything, does the query output look ok?

string(778) "SELECT order_option_id , name , DATE_FORMAT(value, '%e %M %Y') FROM oc_order_option INNER JOIN oc_order u USING (order_id) INNER JOIN oc_order v USING (customer_id) WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 14 DAY UNION SELECT order_option_id , name , DATE_FORMAT(value, '%e %M %Y') FROM oc_order_option INNER JOIN oc_order u USING (order_id) INNER JOIN oc_order v USING (customer_id) WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY order_option_id"

Just entered the query directly in phpmyadmin and returned no results but the date in the database is 24-7-2015 so thought it would output the info unless the data is not being returned from the database, I know it is connecting to the DB as not getting a failed to connect message

Shall I export the DB and attach the sql on here?

Edited July 25, 2015 by ianhaney50

ianhaney50 · July 26, 2015

I def think is a sql issue as just done a new order with a date of 31st July 2015 so it should show when I do the sql directly in phpmyadmin or should show on the page instead of just blank

the query is below

SELECT 
        order_option_id
        , name
        , DATE_FORMAT(value, '%e %M %Y')
        FROM oc_order_option
            INNER JOIN oc_order u USING (order_id)
            INNER JOIN oc_order v USING (customer_id)
        WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY
            AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 14 DAY

        UNION

        SELECT 
        order_option_id
        , name
        , DATE_FORMAT(value, '%e %M %Y')
        FROM oc_order_option
            INNER JOIN oc_order u USING (order_id)
            INNER JOIN oc_order v USING (customer_id)
        WHERE value BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY
            AND IFNULL(value, '1901-01-01') < CURDATE()-INTERVAL 7 DAY
        ORDER BY order_option_id

I can't attach the SQL file to this post, does anyone know how I can attach it or know a link to use to attach it?

Sign In

help integrating a auto email php file into opencart

Recommended Posts

ianhaney50

Link to comment

Share on other sites

ianhaney50

Link to comment

Share on other sites

ianhaney50

Link to comment

Share on other sites

ianhaney50

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information