Barand

See what mysql_error() outputs when it fails

From the sample data I posted you can see that member id 1 is a member of two committees (Procurement and Paper Clips) so shouls have access to the links for those two committees. The query lists all committees and shows which ones member 1 can view (non null member id in results), so loop through results output committee name if (member_id is not null) output link to committee page endif endloop

You tell me, you're the one doing the testing

It was only to help with testing. If you have finished testing it can go.

Good luck with that, cyberRobot. He's been asked several times already

If you are setting this up as a cron job then you would be better logging the emails sent in a file rather than echoing messages to the screen. And no, do not set the test script up as cron job - it will screw up your notification dates. It was merely to test the queries, it's job is done now.

The test script current starts on the 2015-06-12 and queries the data for every day for the next 30 days to see which renewals would be notified by email. So it shows that on the 12th June, renewal 111 for 2015-06-26 was inside 14 days and a notification would be sent. on the 20th June, renewal 111 for 2015-06-26 was inside 7 days and a notification would be sent again on the 12th July, renewal 110 for 2015-07-26 was now inside 14 days and a notification would be sent.

Yes, a separate page. Though it is unlikely that 26 June will be re-sent as it already well inside the 7 day limit.

Perhaps if (time() >= strtotime($convoydate) )

That is precisely what that test script I sent you does.

Sorry, had to make a couple of other adjustments having combined the two queries into a UNION Try 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 , renewal_date FROM visitor 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 , renewal_date FROM visitor 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 visitor_id, renewal_date

Yes

The last code I gave you was a separate script to verify which renewal dates would be process when. It was not for inclusion in your other code. You should be able to combine the 14 and 7 day queries into a single query using a UNION. $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));

try this SELECT CASE WHEN thiswk.rank > prevwk.rank THEN 'v' WHEN thiswk.rank < prevwk.rank THEN '^' ELSE '-' END as movement , thiswk.rank as this_rank , prevwk.rank as prev_rank , users.firstname , users.surname , thiswk.total as overall , points.score FROM users LEFT JOIN points USING (userID) LEFT JOIN ( -- subquery for this week overall total and rank SELECT userID , @seqa:=@seqa+1 as seq , @ranka:=IF(total=@preva,@ranka,@seqa) as rank , @preva:=total as total FROM ( SELECT userID, SUM(score) as total FROM points GROUP BY userID ORDER BY total DESC ) as cum JOIN (SELECT @preva:=NULL,@ranka:=1,@seqa:=0) as init ) as thiswk USING (userID) LEFT JOIN ( -- subquery for previous week overall total and rank SELECT userID , @seqb:=@seqb+1 as seq , @rankb:=IF(total=@prevb,@rankb,@seqb) as rank , @prevb:=total as total FROM ( SELECT userID, SUM(score) as total FROM points WHERE week < (SELECT MAX(week) FROM points) GROUP BY userID ORDER BY total DESC ) as cum JOIN (SELECT @prevb:=NULL,@rankb:=1,@seqb:=0) as init ) as prevwk USING (userID) WHERE points.week = (SELECT MAX(week) FROM points) ORDER BY overall DESC +----------+-----------+-----------+-----------+---------+---------+-------+ | movement | this_rank | prev_rank | firstname | surname | overall | score | +----------+-----------+-----------+-----------+---------+---------+-------+ | - | 1 | 1 | Joe | Bloggs | 11 | 2 | | ^ | 1 | 2 | Adam | Smith | 11 | 3 | | - | 3 | 3 | Tony | Stark | 10 | 3 | | v | 4 | 3 | Bruce | Wayne | 7 | 0 | | - | 5 | 5 | Matt | Murdoch | 5 | 1 | +----------+-----------+-----------+-----------+---------+---------+-------+

Forever? I hope not, I expect invoices to be settled within 30 days.

Yes, the rankings are based on the weekly scores and not the overall scores. You did not specify which it should be based on, so I chose weekly. To base rank on the overall total will require you to completely rewrite of the query, not just a couple of changes to field names here and there, but the techniques used will be the same.

I should have initialized @ranka and @rankb as 1 instead of NULL SELECT CASE WHEN thiswk.rank > prevwk.rank THEN 'v' WHEN thiswk.rank < prevwk.rank THEN '^' ELSE '-' END as movement , thiswk.rank as this_rank , prevwk.rank as prev_rank , users.firstname , users.surname , tot.overall , thiswk.score FROM users LEFT JOIN ( -- subquery for overall total SELECT userID , SUM(score) as overall FROM points GROUP BY userID ) as tot USING (userID) LEFT JOIN ( -- subquery for this week rank and score SELECT userID , @seqa := @seqa+1 as seq , @ranka := IF(score=@prevscorea, @ranka, @seqa)+0 as rank , @prevscorea := score as score FROM points JOIN (SELECT @prevscorea:=null,@seqa:=0,@ranka:=1) as inita WHERE week = (SELECT MAX(week) FROM points) ORDER BY score DESC ) as thiswk USING (userID) LEFT JOIN ( -- subquery for prev week rank SELECT userID , @seqb := @seqb+1 as seq , @rankb := IF(score=@prevscoreb, @rankb, @seqb)+0 as rank , @prevscoreb := score as score FROM points JOIN (SELECT @prevscoreb:=null,@seqb:=0,@rankb:=1) as inita WHERE week = (SELECT MAX(week)-1 FROM points) ORDER BY score DESC ) as prevwk USING (userID) ORDER BY overall DESC +----------+-----------+-----------+-----------+---------+---------+-------+ | movement | this_rank | prev_rank | firstname | surname | overall | score | +----------+-----------+-----------+-----------+---------+---------+-------+ | v | 3 | 2 | Joe | Bloggs | 11 | 2 | | - | 1 | 1 | Adam | Smith | 11 | 3 | | ^ | 1 | 4 | Tony | Stark | 10 | 3 | | v | 5 | 3 | Bruce | Wayne | 7 | 0 | | ^ | 4 | 5 | Matt | Murdoch | 5 | 1 | +----------+-----------+-----------+-----------+---------+---------+-------+

Change the ORDER BY to get a different order. As for the null/blanks, I don't know without your data. Missing weeks or users maybe?

I got renewal and run dates in a twist with previous results. This time it looks OK RunDate Qry RenewDate(id) ----------------------|---|-------------------------------------- 2015-06-12 14 2015-06-26( <-+ 2015-06-13 | 14 2015-06-27(12) <-+ | 2015-06-15 | | 14 2015-06-29(10) | | <-+ 2015-06-18 | | | 14 2015-07-02(11) | | | <-+ 2015-06-20 | | | | 7 2015-06-26( | <-+ | | 2015-06-21 | | | 7 2015-06-27(12) <-+ | | 2015-06-23 | | 7 2015-06-29(10) <-+ | 2015-06-24 | 14 2015-07-08(9) <-+ | 2015-06-26 | | 14 2015-07-10(7) | <-+ | 7 2015-07-02(11) | | <-+ 2015-07-02 | | 7 2015-07-08(9) <-+ | 2015-07-04 | 7 2015-07-10(7) <-+ Code $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 visitor 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)"; } }

Forget the last post of mine - full of errors. Working on a revised version.

I set up a test script to run each day for 15 days (to simulate running each day, it's quicker than waiting couple of weeks for CURDATE() to catchup) $db->query("UPDATE renewals SET date_notified=NULL"); // clear dates $results = array(); $dt1 = new DateTime('2015-06-24'); // date of first run $dp = new DatePeriod($dt1, new DateInterval('P1D'), 15); 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 visitor 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_date, 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[$rdate][$days][] = $rid; } } The results show the renewals emailed/updated each day by the 14 day and 7 day versions of your query. Note that, as I suspected, some are notified twice on the same day. None were notified again after 7 days. RESULTS 2015-06-26 14 8 2015-06-27 14 12 2015-06-29 14 10 2015-07-02 14 11 7 11 2015-07-08 14 9 7 9

Store message ids and which id you are replying to, if any message original: MessageID = 1 From: miguel To: admin Message: Hello ReplyToID = null reply1: MessageID = 2 From: admin To: miguel Message: Hi, how are you ReplyToID = 1 reply2: MessageID = 3 From: miguel To: miguel Message: IT'S NOT WORKING ReplyToID = 1

I know I could have given you that same query at the start, but in the long run I wouldn't be doing you any favours teaching you how to build db tables that are like spreadsheets. That isn't what relational databases like mysql are for.

When I first bought myself a Mac computer back in 1984/5 one of the first things I did was to get a BCPL compiler (there wasn't much else available in those early Mac days) and write an application to draw data flow diagrams, entity relation diagrams and entity life histories, all will draggable objects and smart connectors. Made my job as systems designer easier. So I suppose something to do the ascii diagrams should be easy, but if I have to define the db tables first I may as well use Workbench to produce a model.

Normalize your data.