Barand

WHERE lastvisit BETWEEN '2015-01-01' AND '2015-12-31' would use the index (assuming there is one on the lastvisit column) and therefore much faster

Is that the whole of the code or have you just posted sections? BTW, that (F j, Y) is a useless format for storing dates in a DB. You cannot compare, sort or use datetime functions directly on that format. Always store dates as YYYY-MM-DD. Store dates for functionality, not prettiness. You can reformat on output of the data. Make "today" a DATETIME field and write NOW() to it.

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

Do you have error reporting ON. It would appear that either $_POST['userid'] is empty or not being POSTed.

Now you have NULL dates I had to modify the query slightly SELECT v.visitor_id , visitor_name , visitor_email , visitor_model , visitor_plate , item.description , DATE_FORMAT(renewal_date, '%e %M %Y') as datedue 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 ORDER BY v.visitor_id, renewal_date; Note that the query results will now look like this, a row for each due date +------------+--------------+---------------+---------------+---------------+---------------+--------------+ | visitor_id | visitor_name | visitor_email | visitor_model | visitor_plate | description | datedue | +------------+--------------+---------------+---------------+---------------+---------------+--------------+ | 1 | Visitor A | [email protected] | Ford Ka | AB12CDE | Car insurance | 25 June 2015 | | 1 | Visitor A | [email protected] | Ford Ka | AB12CDE | Car tax | 27 June 2015 | | 1 | Visitor A | [email protected] | Ford Ka | AB12CDE | Car MOT | 5 July 2015 | | 2 | Visitor B | [email protected] | Mercedes C300 | MN45XYZ | Car MOT | 24 June 2015 | | 2 | Visitor B | [email protected] | Mercedes C300 | MN45XYZ | Car insurance | 25 June 2015 | +------------+--------------+---------------+---------------+---------------+---------------+--------------+ so your processing will need changing for multiple rows per visitor.

Assuming you have something like this mysql> SELECT * FROM committee; +--------------+----------------+ | committee_id | committee_name | +--------------+----------------+ | 1 | Procurement | | 2 | Welfare | | 3 | Paper clips | +--------------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM member; +-----------+--------------+ | member_id | committee_id | +-----------+--------------+ | 1 | 1 | | 1 | 3 | | 2 | 1 | | 2 | 2 | | 3 | 1 | | 3 | 2 | | 4 | 3 | | 5 | 3 | | 6 | 3 | +-----------+--------------+ Then SELECT c.committee_id ,c.committee_name ,m.member_id FROM committee c LEFT JOIN member m ON c.committee_id = m.committee_id AND member_id = 1 Gives +--------------+----------------+-----------+ | committee_id | committee_name | member_id | +--------------+----------------+-----------+ | 1 | Procurement | 1 | | 2 | Welfare | NULL | | 3 | Paper clips | 1 | +--------------+----------------+-----------+ You would provide a link where the member_id is NOT NULL

$result = $conn->query($sql); if ($result !== false) {

You call it twice.

The first one executes the sql to insert the visitor record, the second executes the renewal insert. Are you sure there isn't a call to mysqli_query lower down the page?

That should only happen if you are executing the query twice

I notice the renewal_id contains all 0. Should be defined as "INT NOT NULL AUTO_INCREMENT" and should be the PRIMARY KEY When I ran it mysql> INSERT INTO renewal (visitor_id, item_id, renewal_date) VALUES -> (22, 1, '2015-06-25'), -> (22, 2, '2015-06-24'), -> (22, 3, '2015-06-23'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM renewal; +------------+------------+---------+--------------+---------------+ | renewal_id | visitor_id | item_id | renewal_date | date_notified | +------------+------------+---------+--------------+---------------+ | 1 | 22 | 1 | 2015-06-25 | NULL | | 2 | 22 | 2 | 2015-06-24 | NULL | | 3 | 22 | 3 | 2015-06-23 | NULL | +------------+------------+---------+--------------+---------------+ And my table mysql> describe renewal; +---------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+----------------+ | renewal_id | int(11) | NO | PRI | NULL | auto_increment | | visitor_id | int(11) | YES | | NULL | | | item_id | int(11) | YES | | NULL | | | renewal_date | date | YES | | NULL | | | date_notified | datetime | YES | | NULL | | +---------------+----------+------+-----+---------+----------------+

So it is working then?

After this code $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')"; can you echo "<pre>$sql</pre>"; and post the result

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