Jump to content

help integrating a auto email php file into opencart

ianhaney50

By ianhaney50
in PHP Coding Help

 Share

Recommended Posts

ianhaney50 Member

ianhaney50

Posted
Posted
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 Member

ianhaney50

Posted
  • Author
Posted

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 Member

ianhaney50

Posted
  • Author
Posted (edited)

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 by ianhaney50
ianhaney50 Member

ianhaney50

Posted
  • Author
Posted

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?

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.