ianhaney50

Hi ch0cu3r No probs, I have added below the structure for the two tables, is it ok? -- phpMyAdmin SQL Dump -- version 4.0.7 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jun 28, 2015 at 11:34 AM -- Server version: 5.5.42 -- PHP Version: 5.3.28 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `database name` -- -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `username` varchar(30) NOT NULL, `password` varchar(40) DEFAULT NULL, `usersalt` varchar( NOT NULL, `userid` varchar(32) DEFAULT NULL, `userlevel` tinyint(1) unsigned NOT NULL, `email` varchar(50) DEFAULT NULL, `timestamp` int(11) unsigned NOT NULL, `actkey` varchar(35) NOT NULL, `ip` varchar(15) NOT NULL, `regdate` int(11) unsigned NOT NULL, `visitor_id` int(11) NOT NULL, PRIMARY KEY (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `visitors` -- CREATE TABLE IF NOT EXISTS `visitors` ( `visitor_id` int(11) NOT NULL AUTO_INCREMENT, `visitor_name` varchar(255) NOT NULL, `visitor_email` varchar(255) NOT NULL, `visitor_firstline` varchar(255) NOT NULL, `visitor_secondline` varchar(255) NOT NULL, `visitor_town` varchar(255) NOT NULL, `visitor_county` varchar(255) NOT NULL, `visitor_postcode` varchar(255) NOT NULL, `visitor_tel` varchar(255) NOT NULL, `visitor_mobile` varchar(255) NOT NULL, `visitor_model` varchar(50) NOT NULL, `visitor_plate` varchar(255) NOT NULL, PRIMARY KEY (`visitor_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Hi Destramic Thank you for the reply, appreciate it I got something similar - see below <?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 ''; } $visitor_name = ''; if(isset($_POST['visitor_name'])){ $visitor_name = $_POST['visitor_name']; } $sqlCommand3 = "SELECT visitor_id, visitor_name FROM visitors INNER JOIN users USING (visitor_id)"; $query = mysqli_query($db, $sqlCommand3) or die (mysqli_error($db)); $visitor_name=0; //fetch the data from the database while ($row = mysqli_fetch_array($query)) { if ($row['visitor_id'] != $visitor_name) { if ($visitor_name != 0) { $visitor_name = $row['visitor_name']; } } } ?> <?php $title = "My Account - The Tax Elephants"; $pgDesc=""; $pgKeywords=""; include ( 'includes/header.php' ); ?> <!--CONTENT--> <div id="column-whole"> <?php /* Requested Username error checking */ $req_user = trim($_GET['user']); if(!$req_user || strlen($req_user) == 0 || !preg_match("/^[a-z0-9]([0-9a-z_-\s])+$/i", $req_user) || !$database->usernameTaken($req_user)){ die("Username not registered"); } /* Logged in user viewing own account */ if(strcmp($session->username,$req_user) == 0){ echo "<h1>My Account</h1>"; } /* Visitor not viewing own account */ else{ echo "<h1>User Info</h1>"; } /* Display requested user information - add/delete as applicable */ $req_user_info = $database->getUserInfo($req_user); /* Username */ echo "<b>Username: ".$req_user_info['username']."</b><br>"; /* Email */ echo "<b>Email:</b> ".$req_user_info['email']."<br>"; echo "<strong>Name:</strong> ".$visitor_name['visitor_name']."<br>"; /** * Note: when you add your own fields to the users table * to hold more information, like homepage, location, etc. * they can be easily accessed by the user info array. * * $session->user_info['location']; (for logged in users) * * $req_user_info['location']; (for any user) */ /* If logged in user viewing own account, give link to edit */ if(strcmp($session->username,$req_user) == 0){ echo '<br><a href="useredit.php">Edit Account Information</a><br>'; } /* Link back to main */ echo "<br><a href='process.php'>Logout</a><br>"; ?> </div> <?php // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> I got no errors but it is not displaying the full name from the visitors table?

Sorry on my userinfo.php page it currently retrieves and displays the username and email address but now I want to retrieve and display the Full name of the visitor from the visitors table I know it can be done using JOIN but not 100% sure how to code it in I did try from another php file that uses JOIN but it only only displays the first letter of the full name The code I have is below in the userinfo.php file <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <?php /** * UserInfo.php * * This page is for users to view their account information * with a link added for them to edit the information. * * Updated by: The Angry Frog * Last Updated: October 26, 2011 */ include("include/session.php"); global $database; $config = $database->getConfigs(); if (!isset($_GET['user'])) { header("Location: ".$config['WEB_ROOT'].$config['home_page']); } ?> <?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 ''; } $sqlCommand3 = "SELECT v.visitor_id, visitor_name FROM visitors v JOIN users"; $query = mysqli_query($db, $sqlCommand3) or die (mysqli_error($db)); //fetch the data from the database while ($row = mysqli_fetch_array($query)) { $visitor_name = $row['visitor_name']; } ?> <?php $title = "My Account - The Tax Elephants"; $pgDesc=""; $pgKeywords=""; include ( 'includes/header.php' ); ?> <!--CONTENT--> <div id="column-whole"> <?php /* Requested Username error checking */ $req_user = trim($_GET['user']); if(!$req_user || strlen($req_user) == 0 || !preg_match("/^[a-z0-9]([0-9a-z_-\s])+$/i", $req_user) || !$database->usernameTaken($req_user)){ die("Username not registered"); } /* Logged in user viewing own account */ if(strcmp($session->username,$req_user) == 0){ echo "<h1>My Account</h1>"; } /* Visitor not viewing own account */ else{ echo "<h1>User Info</h1>"; } /* Display requested user information - add/delete as applicable */ $req_user_info = $database->getUserInfo($req_user); /* Username */ echo "<b>Username: ".$req_user_info['username']."</b><br>"; /* Email */ echo "<b>Email:</b> ".$req_user_info['email']."<br>"; echo "<strong>Name:</strong> ".$visitor_name['visitor_name']."<br>"; /** * Note: when you add your own fields to the users table * to hold more information, like homepage, location, etc. * they can be easily accessed by the user info array. * * $session->user_info['location']; (for logged in users) * * $req_user_info['location']; (for any user) */ /* If logged in user viewing own account, give link to edit */ if(strcmp($session->username,$req_user) == 0){ echo '<br><a href="useredit.php">Edit Account Information</a><br>'; } /* Link back to main */ echo "<br><a href='process.php'>Logout</a><br>"; ?> </div> <?php // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> <!--CONTENT--> <?php include( 'includes/footer.php' ); ?> I have added visitor_id in the users table and made it INT(11) at the end of the users table and is currently saying 0 but the visitor_id of the user I am trying to match it with is 66 I looked in database.php as going by another php file using JOIN, it has INSERT INTO as well so it looks like it inserts the visitor_id into two two tables so I thought it already adds visitor_id into visitors table and thought it would also add visitir_id into the users table but has not worked that way the database.php coding I have is below function addNewUser($username, $password, $email, $token, $usersalt){ $time = time(); $config = $this->getConfigs(); /* If admin sign up, give admin user level */ if(strcasecmp($username, ADMIN_NAME) == 0){ $ulevel = ADMIN_LEVEL; /* Which validation is on? */ }else if ($config['ACCOUNT_ACTIVATION'] == 1) { $ulevel = REGUSER_LEVEL; /* No activation required */ }else if ($config['ACCOUNT_ACTIVATION'] == 2) { $ulevel = ACT_EMAIL; /* Activation e-mail will be sent */ }else if ($config['ACCOUNT_ACTIVATION'] == 3) { $ulevel = ADMIN_ACT; /* Admin will activate account */ } $password = sha1($usersalt.$password); $userip = $_SERVER['REMOTE_ADDR']; $query = "INSERT INTO ".TBL_USERS." SET username = :username, password = :password, usersalt = :usersalt, userid = 0, userlevel = $ulevel, email = :email, timestamp = $time, actkey = :token, ip = '$userip', regdate = $time, visitor_id = $visitor_id"; $stmt = $this->connection->prepare($query); return $stmt->execute(array(':username' => $username, ':password' => $password, ':usersalt' => $usersalt, ':email' => $email, ':token' => $token)); }

Sorted, I added in $result = $conn->query($sql); just under the first query and has added it in the database sorry for being stupid thank you for replying though, appreciate it

Ahh guess that is this line after the second query? $result = $conn->query($sql); so I need the same line after the first query?

Hi I just thought I would test the info form again to make sure it still works and I fill it out and it comes up added to database so I check the database and the only data it has added is the dates into the renewal table, it has not added any data to the visitors table The php page is not displaying any errors Is it ok if anyone can check the code over please <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <?php $title = "Information Form - The Tax Elephants"; $pgDesc=""; $pgKeywords=""; include ( 'includes/header.php' ); ?> <!--CONTENT--> <div id="column-left"> <div class="form"> <form action="" method="post"> <label>Name :</label> <input type="text" name="visitor_name" required="required" placeholder="Please Enter Name"/> <br /><br /> <label>Email :</label> <input type="email" name="visitor_email" required="required" placeholder="john123@gmail.com"/> <br/><br /> <label>Address Line 1 :</label> <input type="text" name="visitor_firstline" required="required" placeholder="Please Enter First Line of your address"/> <br><br> <label>Address Line 2 :</label> <input type="text" name="visitor_secondline" required="required" placeholder="Please Enter Second Line of your address"/> <br><br> <label>Town :</label> <input type="text" name="visitor_town" required="required" placeholder="Please Enter your Town"/> <br><br> <label>County :</label> <input type="text" name="visitor_county" required="required" placeholder="Please Enter Your County"/> <br/><br /> <label>Postcode :</label> <input type="text" name="visitor_postcode" required="required" placeholder="Please Enter Your Postcode"/> <br><br> <label>Telephone Number :</label> <input type="text" name="visitor_tel" required="required" placeholder="Please Enter Your Telephone Number"/> <br><br> <label>Mobile Number :</label> <input type="text" name="visitor_mobile" required="required" placeholder="Please Enter Your Mobile Number"/> <br><br> <label>Model of Car/Van :</label> <input type="text" name="visitor_model" required="required" placeholder="Please Enter the Model of your Car or Van"/> <br><br> <label>License Plate Number :</label> <input type="text" name="visitor_plate" required="required" placeholder="Please Enter your License Number Plate"/> <br><br> <label>Car Tax Renewal Date :</label> <input type="text" id="datepicker" name="visitor_tax" required="required" placeholder="Please Enter your Car Tax Renewal Date"/> <br><br> <label>MOT Expiry Date :</label> <input type="text" id="datepicker2" name="visitor_mot" required="required" placeholder="Please Enter your MOT Expiry Date"/> <br><br> <label>Insurance Expiry Date :</label> <input type="text" id="datepicker3" name="visitor_insurance" required="required" placeholder="Please Enter your Insurance Expiry Date"/> <br><br> <div class="box"> <label>I agree to the <a class="button" href="#popup1">terms</a></label> </div> <input type="checkbox" class="checkbox" id="the-terms" value="I Agree"> <div id="popup1" class="overlay"> <div class="popup"> <h2>Terms</h2> <a class="close" href="#">×</a> <div class="content"> TERMS TEXT. </div> </div> </div> <br /><br /> <input type="submit" value=" Submit " name="submit" disabled="disabled" id="submitBtn" /> </form> </div> </div> <div id="column-right"> </div> <!--CONTENT--> <script> $(document).ready(function() { var the_terms = $("#the-terms"); the_terms.click(function() { if ($(this).is(":checked")) { $("#submitBtn").removeAttr("disabled"); } else { $("#submitBtn").attr("disabled", "disabled"); } }); }); </script> <?php if(isset($_POST["submit"])){ $servername = ""; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // escape variables for security $visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']); $visitor_email = mysqli_real_escape_string($conn, $_POST['visitor_email']); $visitor_firstline = mysqli_real_escape_string($conn, $_POST['visitor_firstline']); $visitor_secondline = mysqli_real_escape_string($conn, $_POST['visitor_secondline']); $visitor_town = mysqli_real_escape_string($conn, $_POST['visitor_town']); $visitor_county = mysqli_real_escape_string($conn, $_POST['visitor_county']); $visitor_postcode = mysqli_real_escape_string($conn, $_POST['visitor_postcode']); $visitor_tel = mysqli_real_escape_string($conn, $_POST['visitor_tel']); $visitor_mobile = mysqli_real_escape_string($conn, $_POST['visitor_mobile']); $visitor_model = mysqli_real_escape_string($conn, $_POST['visitor_model']); $visitor_plate = mysqli_real_escape_string($conn, $_POST['visitor_plate']); $sql = "INSERT INTO visitors (visitor_name, visitor_email, visitor_firstline, visitor_secondline,visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate) VALUES ('$visitor_name', '$visitor_email', '$visitor_firstline', '$visitor_secondline', '$visitor_town', '$visitor_county', '$visitor_postcode', '$visitor_tel', '$visitor_mobile', '$visitor_model', '$visitor_plate')"; $visitor_id = $conn->insert_id; $insurance_date = date('Y-m-d', strtotime($_POST['visitor_insurance'])); $mot_date = date('Y-m-d', strtotime($_POST['visitor_mot'])); $tax_date = date('Y-m-d', strtotime($_POST['visitor_tax'])); $sql = "INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES ($visitor_id, 1, '$insurance_date'), ($visitor_id, 2, '$mot_date'), ($visitor_id, 3, '$tax_date')"; /*echo "<pre>$sql</pre>";*/ /*$conn->query($sql);*/ $result = $conn->query($sql); if ($result !== false) { echo "<script type= 'text/javascript'>alert('Your Information has been added successfully to our database');</script>"; } else { echo "<script type= 'text/javascript'>alert('Error: " . $sql . "<br>" . $conn->error."');</script>"; } $conn->close(); } ?> <?php include( 'includes/footer.php' ); ?> It seems to be skipping past the INSERT INTO visitors sql and jumps to the second INSERT INTO renewal?

Done that I have managed to get it working with the following code <?php if(isset($_POST["submit"])){ $servername = ""; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // escape variables for security $email = mysqli_real_escape_string($conn, $_POST['email']); $sql = "INSERT INTO subscribers (email) VALUES ('$email')"; header("location:add.php"); $conn->query($sql); $email = $conn->insert_id; $result = $conn->query($sql); //define the receiver of the email $to = $_POST['email']; //define the subject of the email $subject = 'Thank you for signing up'; //define the message to be sent. Each line should be separated with \n $message = "Thank you for signing up to receive regular updates on the launch of our website."; //define the headers we want passed. Note that they are separated with \r\n $headers = "From: noreply@taxelephants.uk"; //send the email $mail_sent = mail( $to, $subject, $message, $headers ); //if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" echo $mail_sent ? "Mail sent" : "Mail failed"; if ($result !== false) { echo"fail"; } $conn->close(); header("location:add.php"); } ?> I just added in $_POST['email']; on the $to line and is working Is that ok to be in there as before I did not have it in there and the email would not send

Ahh ok had a quick read through and understood the bit bout disabling error reporting the php is not producing any errors which is good but is still not sending any email to the email address I sign up with but is storing the email address in the database

I've done them two things

Ahh ok what does the @ in front do, does it open it up to attacks or something or is just not good practice? Will do both them two things now

Hi I am trying to make the form add the data to the mysql database which it does perfect but am trying to get the code to send a auto repsonse email to the email address of the user who just submitted their email address to store within the database but the email is not being sent The coding I got is below <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <?php $title = "The Tax Elephants"; $pgDesc=""; $pgKeywords=""; include ( 'includes/header.php' ); ?> <!--CONTENT--> <div id="column-center"> <div id="content"> <div class="text"> <h2>Welcome to Our Pre-launch Page</h2> </div> <div class="counter"> <h3 class="top-text">Because us elephants are perfectionists. We are just doing some more improvements and ensuring our website is perfect for you.</h3> <br /> <div class="love-text"> <img src="images/elephants-family.png" alt="" title="" /> <br /> Lots of Love <br /> Tusky, Honey & Eloise </div> <h3>Estimated Time Remaining Before Launch:</h3> <div id="defaultCountdown"></div> <div class="details"> <h3>Enter your email address below to be notified of website updates</h3> <form action="" method="post"> <div id="email_input"> <input type="email" name="email" id="email" size="30" placeholder="Enter your email address"> <input type="submit" name="submit" id="submit" value="SUBMIT" size="80" /> </div> </form> <!-- End Subscription Form --> <br /><br /> <h3>Click below to share our page with your friends:</h3> <div class="social"> <!-- Facebook --> <a href="http://www.facebook.com/sharer.php?u=http://www.taxelephants.uk" target="_blank"><img src="./images/facebook.png" alt="Facebook" /></a> <!-- Twitter --> <a href="http://twitter.com/share?url=http://www.taxelephants.uk&text=Tax Elephants&hashtags=websitedesign" target="_blank"><img src="./images/twitter.png" alt="Twitter" /></a> <!-- Email --> <a href="mailto:?Subject=Tax Elephants &Body=I%20saw%20this%20and%20thought%20of%20you!%20 http://www.taxelephants.uk"><img src="./images/email.png" alt="Email" /></a> </div> </div><!--end details--> </div> </div> </div> <?php if(isset($_POST["submit"])){ $servername = ""; $username = ""; $password = ""; $dbname = ""; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // escape variables for security $email = mysqli_real_escape_string($conn, $_POST['email']); $sql = "INSERT INTO subscribers (email) VALUES ('$email')"; header("location:add.php"); $conn->query($sql); $email = $conn->insert_id; $result = $conn->query($sql); //define the receiver of the email $to = '$email'; //define the subject of the email $subject = 'Test email'; //define the message to be sent. Each line should be separated with \n $message = "Hello World!\n\nThis is my first mail."; //define the headers we want passed. Note that they are separated with \r\n $headers = "From: webmaster@example.com"; //send the email $mail_sent = @mail( $to, $subject, $message, $headers ); //if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" echo $mail_sent ? "Mail sent" : "Mail failed"; if ($result !== false) { echo"fail"; } $conn->close(); } ?> <!--CONTENT--> <?php include( 'includes/footer.php' ); ?> I have tried moving the email script part right at the end after the close of the mysql, also moved it above the header location line but nothing seems to work, am I missing something?

Sorry sorted it now

Hi PaulRyan That does make more sense I will try that way Thank you appreciate it

Hi I got a registration, login script in my little test project I am working on and it all works perfect and the forgot password works but it is set to enter username and I am trying to change it to email as users remember their email address more than what username they use I got the script from the following link, it is all in mysqli etc. https://app.box.com/s/zrwnsa36x8iifou1zdim the coding looks like it is attached to four different php files which I have posted in pastebin and provided the links below I have had a go myself at changing it from username to email but not had any joy so far http://pastebin.com/N8G3Ua0T - forgotpass.php http://pastebin.com/mV5w28ni - process.php http://pastebin.com/N8RApV6m - include/session.php http://pastebin.com/RyceNBFK - include/database.php I have not changed the session.php and database.php as could not see anything in there to change so just changed the forgotpass.php and process.php file from username to email I have commented out the coding for username in process.php and put the email coding just below it, it starts on line 120 Sorry

Ahh ok cool so the auto-email.php script will now send emails if the renewal_date is 14 days away and then send another if 7 days away, that right?

Ahh ok cool so I just keep the test script php file on the web server or can it be taken off the server now?

Ahh ok that's perfect so if I set that up as a cron job as well to check every day?

Hi I just put it in a seperate php file and uploaded it to the web server and has outputted the following on the php page 2015-06-12 14 2015-06-26(111)2015-06-20 7 2015-06-26(111)2015-07-12 14 2015-07-26(110) ahh ok so think I will create another visitor with other dates so just to confirm the coding checks for dates outside of the 14 days and 7 days, that right as guessing within 14 and 7 days would be the same result? hope that makes sense? sorry if not

Ahh sorry that other one that you said was separate so I just make that a php page upload it and test it?

Works perfect the date I put in is 26 June 2015 is there a way to test it to see if it does send another email when the date is within 7 days?

Looks ok now, got a blank white page with no errors I'll reset the date_notified column so it sends a email and outputs the data on the php page, one sec

Ok sorry, done that but getting the following error Unknown column 'v.visitor_id' in 'order clause'

Ahh ok, sorry I have put the coding back to how it was before I think, I am not getting any errors and getting the email so guessing is all ok so the following script $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY UNION SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db)); Do I put that in in place of the current coding below? $sqlCommand = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db));

Hi Sorry for my late reply I have the following coding but am getting the error Fatal error: Call to a member function fetch_row() on a non-object in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 143 $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 v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 14 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 14 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand) or die (mysqli_error($db)); $sqlCommand2 = "SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , renewal_id , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue FROM visitors v INNER JOIN renewal USING (visitor_id) INNER JOIN item USING (item_id) WHERE renewal_date BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND IFNULL(date_notified, '1901-01-01') < CURDATE()-INTERVAL 7 DAY ORDER BY v.visitor_id, renewal_date;"; $query = mysqli_query($db, $sqlCommand2) or die (mysqli_error($db)); $current_visitor=0; $current_email = ''; $headers = "From: noreply@irhwebsites.co.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['visitor_id'] != $current_visitor) { // send email to current visitor if ($current_visitor != 0) { $to = $current_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['visitor_id']; $current_email = $row['visitor_email']; $message = "Name: {$row['visitor_name']} \n\n"; $renewals = array(); } $message .= "{$row['description']} expiry date: {$row['datedue']}\n"; $renewals[] = $row['renewal_id']; // store renewal id for updates } // send email to final visitor if ($current_visitor != 0) { $to = $current_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>"; } } // update successful notifications $idlist = join(',', $notifications); $db->query("UPDATE renewal SET date_notified=NULL"); // clear dates $results = array(); $dt1 = new DateTime('2015-06-12'); // date of first run $dp = new DatePeriod($dt1, new DateInterval('P1D'), 30); foreach ($dp as $d) { $rundate = $d->format('Y-m-d'); reminders($db, $rundate, 14, $results); reminders($db, $rundate, 7, $results); } echo '<pre>'; foreach ($results as $dt=>$darray) { echo $dt."\n"; foreach ($darray as $days=>$ids) { printf ("\t%2d\t%s\n", $days, join(', ', $ids)); } } echo '<pre>'; function reminders($db, $rundate, $days, &$results) { $sql = "SELECT v.visitor_id , renewal_id , renewal_date FROM visitors v INNER JOIN renewal USING (visitor_id) WHERE renewal_date BETWEEN '$rundate' AND '$rundate'+INTERVAL $days DAY AND IFNULL(date_notified, '1901-01-01') < '$rundate'-INTERVAL $days DAY ORDER BY renewal_id"; $res = $db->query($sql); while (list($vid, $rid, $rdate) = $res->fetch_row()) { $db->query("UPDATE renewal SET date_notified = '$rundate' WHERE renewal_id=$rid"); $results[$rundate][$days][] = "$rdate($rid)"; } } $db->query($sql) ; // Free the results mysqli_free_result($query); //close the connection mysqli_close($db); ?> UPDATE: I changed the visitor to visitors as didn't realise it had visitor as the table name near the end in the sql query line so changed it and the auto-email.php page is outputting the following Name: Ian Haney Tax expiry date: 26 June 2015 Email Successfully Sent 2015-06-12 14 2015-06-26(105)2015-06-20 7 2015-06-26(105)2015-07-12 14 2015-07-26(104) Notice: Undefined variable: sql in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 150 Warning: mysqli::query(): Empty query in /home/sites/broadwaymediadesigns.co.uk/public_html/sites/the-tax-elephants/auto-email.php on line 150

Ok no worries, thank you so much really appreciate it