ianhaney50 Posted July 25, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
ianhaney50 Posted July 25, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
ianhaney50 Posted July 25, 2015 Author Share Posted July 25, 2015 (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 July 25, 2015 by ianhaney50 Quote Link to comment Share on other sites More sharing options...
ianhaney50 Posted July 26, 2015 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.