Barand

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"; } }

The value of "$level" will tell you whether is the main or nested.

You can test the dates are in range when you output with PHP, eg if (date('Y-m-d',strtotime($row['vidate'])) >= date('Y-m-d') && strtotime($row['vidate'])<= date('Y-m-d',strtotime("+7 days"))) { echo "Insurance Expiry Date: {$row['vidate']}<br/>"; } or you can adjust the SQL 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

Let's take the problems one by one Checking for date < today + 7 days will also return records from last week, last month, last year, as those dates all fit the condition. What you need is dates between today and today + 7 days. You need to check the dates individually. I am assuming that if any one of those dates is due then an email is sent (therefore using OR condition). If all three must be due then change to AND. The problem with SELECT * is it selects all columns. If you have 12 columns in the table and only need 4 then you you retrieve 3x more data (approx) and it therefore takes 3x longer to transfer the data from the server. If, in future, someone decides to add,say, a BLOB column to store an image of the insurance document, then your queries suddenly slow down as instead of the 20 bytes you need, you now transfer 50,000 bytes for each record. If you had specified the columns then that wouldn't happen. SELECT visitor_id , visitor_name , visitor_email , DATE_FORMAT(visitor_tax, '%e %M %Y') as taxdate , DATE_FORMAT(visitor_mot, '%e %M %Y') as motdate , DATE_FORMAT(visitor_insurance, '%e %M %Y') 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

You need to format the dates separately "SELECT * , DATE_FORMAT(visitor_tax, '%e %M %Y') as taxdate , DATE_FORMAT(visitor_mot, '%e %M %Y') as motdate , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate SELECT * !!! - ok, don't listen, that's the best way to stop people helping Exactly what date conditions are you wanting here?

If your queries are only concerned with DATE types then you should use CURDATE() and not NOW(). The NOW() function also contains the time of day. Alternatively you can format the date in the query SELECT DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate Don't use SELECT * unless you really do want every column retrieved, it's inefficient and obscures the purpose of the query. Specify what you need. SELECT visitor_id , visitor_name , DATE_FORMAT(visitor_insurance, '%e %M %Y') as vidate FROM visitors WHERE visitor_insurance > DATE_ADD(CURDATE(), INTERVAL -1 DAY)

If visitor_mot is a datetime then you need to ignore the time portion. SELECT * FROM visitors WHERE DATE(visitor_mot) = DATE_ADD(curdate(), INTERVAL 1 DAY) or SELECT * FROM visitors WHERE DATE(visitor_mot) = CURDATE() + INTERVAL 1 DAY

It's the programming equivalent of sitting on a branch while you saw through it. You are processing an array but in that process you overwrite the very array you are trying to process

Pass a level value down the function calls and use that function displayList(&$cats, $parent, $level=0) { switch ($level) { case 0: $class = "level0"; break; case 1: $class = "level1"; break; case 2: $class = "level2"; break; } if ($parent==0) { foreach ($cats[$parent] as $id=>$nm) { displayList($cats, $id); } } else { echo "<ul>\n"; foreach ($cats[$parent] as $id=>$nm) { echo "<li>$nm</li>\n"; if (isset($cats[$id])) { displayList($cats, $id, $level+1); //increment level } } echo "</ul>\n"; } }

The calculation in that position does nothing - $price and $weight have not been defined. $request = mysql_query("SELECT * FROM database WHERE ean = '12' ") or die (mysql_error()); while ($row = mysql_fetch_array($request)) { if ($row['weight']==0) { $calculation1 = 0; } else { $calculation1 = $row['price'] / $row['weight']; } echo "$row[name] and $row[weight]<br>"; echo "$row[price] and $calculation1 <br>"; echo "<br><br>"; } Alternatively SELECT name , price , weight , CASE weight WHEN 0 THEN 0 ELSE price/weight END as calculation FROM database WHERE ean = '12' You should move off mysql_ functions and change to mysqli_ or PDO libraries instead.

Probably. On reflection I would prefer to recurse with this simpler query and method though, and it should work for any number of levels and doesn't display the top level. $sql = "SELECT category_id , name , IFNULL(parent, 0) FROM category"; $res = $db->query($sql); while (list($id,$name,$parent) = $res->fetch_row()) { $cats[$parent][$id] = $name; } displayList($cats, 0); function displayList(&$cats, $parent) { if ($parent==0) { foreach ($cats[$parent] as $id=>$nm) { displayList($cats, $id); } } else { echo "<ul>\n"; foreach ($cats[$parent] as $id=>$nm) { echo "<li>$nm</li>\n"; if (isset($cats[$id])) { displayList($cats, $id); } } echo "</ul>\n"; } } With your data, gives <ul> <li>Dogs</li> <ul> <li>Bulldog</li> <li>Bullmastiff</li> <li>Chow Chow</li> <li>Cocker Spaniel</li> <li>German Shepherd</li> <li>Gordon Setter</li> </ul> <li>Cats</li> <ul> <li>American Bobtail</li> <li>Balinese</li> <li>Birman</li> <li>British Shorthair</li> <li>Burmese</li> </ul> <li>Birds</li> <li>Fish</li> <li>Reptiles</li> </ul>

Use code tags if you want things to line up neatly

Or you can use recursion $res = $db->query($sql); while (list($lev1,$lev2,$lev3) = $res->fetch_row()) { $cats[$lev1][$lev2][] = $lev3; } displayList($cats); function displayList($cats) { echo "<ul>\n"; foreach ($cats as $k=>$v) { if (is_array($v)) { echo "<li>$k</li>\n"; displayList($v); } else echo "<li>$v</li>\n"; } echo "</ul>\n"; }