Barand

A couple of problems 1. You do not have a form field with the name of "renewal_date". As I told you in reply #76 they are "visitor_mot", "visitor_tax" and "visitor_insurance". 2. Dates should always be stored in YYYY-mm-dd format. Change $insurance_date = mysqli_real_escape_string($conn, $_POST['renewal_date']); $mot_date = mysqli_real_escape_string($conn, $_POST['renewal_date']); $tax_date = mysqli_real_escape_string($conn, $_POST['renewal_date']); to $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'])); EDIT: Just noticed you gave all three dates in the form the same name. Why would you want to do that?

The date notified is a TIMESTAMP, therefore updates automatically when the record is updated. If you only want to update when a notification is sent then define as date_notified DATETIME DEFAULT NULL and, when you send a notification, SET date_notified = NOW() As for those 0 dates, what is the value originally sent to the page in $_POST['visitor_insurance'] ?

A couple of things would help it to work better 1) Mysqli_real_escape_string requires the connection as the first parmeter $visitor_name = mysqli_real_escape_string($conn, $_POST['visitor_name']); or $visitor_name = $conn->real_escape_string($_POST['visitor_name']); 2 ) The item table would be set up once and forgotten. No need to update it when processing the form 3 ) As I have already said 2 or 3 times already, you would get the new insert_id AFTER you have inserted the visitor record. $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')"; $conn->query($sql); $visitor_id = $conn->insert_id; $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')"; $conn->query($sql); edit: PS The index errors are because your form field names are visitor_mot, visitor_tax and visitor_insurance.

or SELECT firstname , surname , week AS gameweek , SUM(score) AS total FROM points INNER JOIN users ON users.userID = points.userID WHERE week = (SELECT MAX(week) FROM points) GROUP BY points.userID ORDER BY total DESC

The item table +------------+------------------------+ | item_id | description | +------------+------------------------+ | 2 | Insurance | | 1 | M.O.T. | | 2 | Tax | +------------+------------------------+ When you insert the visitor record exclude visitor_id column - that will be generated by the AUTO_INCREMENT After inserting, get the new id that was generated Then insert the 3 dates from your form into the renewals You should never put POST values directly into a query. Sanitize them with mysqli_real_escape_string() or use a prepared statement. is set up manually and probably will never change

Yes, using the method I posted in my previous reply

If you have a new visitor Insert visitor record Get the new id using $newId = $mysqli->insert_id; Insert the dates into renewals with INSERT INTO renewals (visitor_id, item_id, renewal_date) VALUES ($newId, 1, '$insurance_date'), ($newId, 2, '$mot_date'), ($newId, 3, '$tax_date');

If you remove the visitor_id from the renewals table how the hell do you expect to match which renewals belong to which visitor? Ditto item_id and items. With the keys you have +------------+-----------+------------+ +------------+--------+----------------+ | vehicle_id | model | plate | | vehicle_id | item_id| renewal_date | +------------+-----------+------------+ +------------+--------+----------------+ | 1 | Ford | AB 14 XYZ | | 1 | 1 | 2015-06-28 | | 2 | Jeep | CD 15 STU | | 1 | 2 | 2015-06-30 | +------------+-----------+------------+ | 1 | 3 | 2015-08-10 | | 2 | 1 | 2015-08-12 | | 2 | 2 | 2015-08-12 | | 2 | 3 | 2015-10-01 | +------------+--------+----------------+ Remove them and you have no idea what goes with what, just a lot of dates +------------+-----------+------------+ +----------------+ | vehicle_id | model | plate | | renewal_date | +------------+-----------+------------+ +----------------+ | 1 | Ford | AB 14 XYZ | | 2015-06-28 | | 2 | Jeep | CD 15 STU | | 2015-06-30 | +------------+-----------+------------+ | 2015-08-10 | | 2015-08-12 | | 2015-08-12 | | 2015-10-01 | +----------------+

PS time() - 15; // = 15 seconds ago!

Consider this situation. A visitor's mot is due in 7 days and his tax is due in 9 days time. You create an email today for the mot and set notified to today. You run the job tomorrow but check the notify date so you do not resend another reminder for the mot. The next day when you run the job the tax is due so you check the notified date and you do not send. Oops! You needed to send this new one. So you decide you need a second notify date for tax and a third for insurance. The case for normalizing the data now grows. Table visitor : current visitor data without the dates Table item : Descriptions for Insurance, tax and MOT with ids Table renewal : visitor_id, item_id, renewal_date, date_notified The query is also simplified and only returns the dates you need SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , 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 BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY AND date_notified < CURDATE()-INTERVAL 7 DAY;

Multiple insert INSERT INTO tablename (col1, col2) VALUES (1, 'AAA'),(2,'BBB'),(3,'CCC'); Multiple update UPDATE tablename SET col2 = CASE id WHEN 1 THEN 'DDD' WHEN 2 THEN 'EEE' WHEN 3 THEN 'FFF' ELSE col2 END

http://dev.mysql.com/doc/refman/5.6/en/integer-types.html

You could make use of that timestamp to prevent the same emails being sent on every refresh or page load

So if you were running live you would have bombarded your users with emails again?

Looks OK. Did you you check that date_last_modified had been updated with the correct date and time?

Just to illustrate: +-------------+-------------------+--------+ | category_id | name | parent | +-------------+-------------------+--------+ | 1 | Pets | 0 | | 2 | Dogs | 1 | | 3 | Cats | 1 | | 4 | Birds | 1 | | 5 | Fish | 1 | | 6 | Reptiles | 1 | added these: | 22 | Amphibians | 6 | | 23 | Frogs | 22 | | 24 | Toads | 22 | | 25 | Alligator | 22 | | 26 | Salt-water | 25 | | 27 | Fresh-water | 25 | +-------------+-------------------+--------+

That also explains the 01 January 1970 echo date('d F Y', strtotime('n/a')); //---> 01 January 1970

If you are formatting the date in the query why are you trying to reformat it with PHP? Just output $row['vidate'] Define the timestamp field as `date_last_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Then in the update query you can use SET date_last_modified = NULL

You should not be using time() to update the TIMESTAMP type column. Set it to CURRENT_TIMESTAMP or NULL (if defined as NOT NULL)

First of all, from the query create an array where the keys are the parent and each value is an array of that parent's children Array ( [0] => Array ( [1] => Pets ) [1] => Array ( [2] => Dogs [3] => Cats [4] => Birds [5] => Fish [6] => Reptiles ) [2] => Array ( [11] => Bulldog [12] => Bullmastiff [13] => Chow Chow [14] => Cocker Spaniel [15] => German Shepherd [16] => Gordon Setter ) [3] => Array ( [17] => American Bobtail [18] => Balinese [19] => Birman [20] => British Shorthair [21] => Burmese ) ) The function is recursive (ie it calls itself). The function is passed the above array and a parent id. So if "1" is passed as the parent it starts a new <ul> group then processes the children of parent 1. The first child is [2] => Dogs It outputs this as a list item and checks if id 2 has any children ( isset($cats[2]) ). If it has children it calls the same function passing "2" as the parent and the process is repeated. So the result is to output each category followed by a list of its children. If those children have children, they are listed after the parent. An advantage of this approach is that if you now add "Amphibians" as a subcat of "Reptile" and that subcat has further subcats of "Frog", "Toad", "Alligator" then it will still work. With LEFT JOIN approach you would have to change the query and processing if another level were introduced.

I need to go foraging for food so I'll be AFK for an hour or two

I would be surprised if that were the cause. IMHO it might be more useful to make that extra field "date_last_notified" instead of just "status"

01 January 1970 is day 0 in the Unix world and usually signifies trying to format a zero or invalid date with php. Odd that SQL should do for 2015-06-28 eg mysql> SELECT DATE_FORMAT('2015-06-28', '%e %M %Y') as date; +--------------+ | date | +--------------+ | 28 June 2015 | +--------------+ mysql> SELECT DATE_FORMAT('', '%e %M %Y') as date; +------+ | date | +------+ | NULL | +------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT DATE_FORMAT('0', '%e %M %Y') as date; +------+ | date | +------+ | NULL | +------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT DATE_FORMAT('2015-06-31', '%e %M %Y') as date; +------+ | date | +------+ | NULL | +------+

Sorry! Missed a couple of ")"s when I was pasting. SELECT visitor_id , visitor_name , visitor_email , IF(visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_tax, '%e %M %Y') , 'n/a') as taxdate , IF(visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_mot, '%e %M %Y') , 'n/a') as motdate , IF(visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY, DATE_FORMAT(visitor_insurance, '%e %M %Y') , 'n/a') as vidate FROM visitors WHERE visitor_tax BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY OR visitor_mot BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY OR visitor_insurance BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY

Like this function displayList(&$cats, $parent, $level=0) { switch ($level) { case 0: $class = "list-unstyled categorychecklist"; break; case 1: $class = "children"; break; case 2: $class = "children2"; break; } if ($parent==0) { foreach ($cats[$parent] as $id=>$nm) { displayList($cats, $id); } } else { echo "<ul class='$class'>\n"; foreach ($cats[$parent] as $id=>$nm) { echo "<li><input type='checkbox' value=''> $nm</li>\n"; if (isset($cats[$id])) { displayList($cats, $id, $level+1); //increment level } } echo "</ul>\n"; } }