I am a relative beginner where it comes to PHP, and had practically no experience of MySQL, but I (perhaps foolishly!) agreed to help a friend with his website. It has been an interesting experience - I have learnt a lot, although the learning curve was steep!
I had the site working, to the point where there would be a page that displays orders by date for delivery.
The following query filtered results out based on a weekly or fortnightly delivery criteria (e.g. a weekly order for a Tuesday that started on the 10 June wouldn't show on the delivery sheet for Tuesday 17 June, but it would for 24 and so on). This query worked on the recur field in the db, with a value of 1 for weekly and 2 for fortnightly (0 was for one-off orders, but my friend requested it be removed). Also, $DelDate is determined from an input box, and is the date for which the user requires a delivery sheet.
SELECT *
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer )
WHERE (
(
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '$DelDate' )
)
OR (
recur =2
AND DATEDIFF( date, '$DelDate' ) %14 =0
)
)
AND date <= '$DelDate'
AND '$DelDate' NOT
BETWEEN holdstart
AND holdend
As I said, this worked fine. And then my friend dropped a bombshell! He decided that he now wanted to be able to determine which items on the order be weekly, fortnightly or now even one-off, individually, but keep them all on one order!!
What I have done so far is to remove the recur field, replacing it with recur1, recur2... up to recur10. These correspond with the item1, item2 etc. fields respectively.
I am now at a complete loss, though! How can I filter the different individual entries from each record?
I am thinking perhaps of using the following query:
SELECT *
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer )
WHERE (
DAYOFWEEK( date ) = DAYOFWEEK( '$DelDate' )
)
AND date <= '$DelDate'
AND '$DelDate' NOT
BETWEEN holdstart
AND holdend
and then having a PHP loop to determine whether each field matches the criteria, and filter unwanted records.
Is this a good way to tackle it? If so, what is the PHP equivalent of DATEDIFF, i.e. how can I determine whether $DelDate falls on the fortnightly period of date?
Thanks in advance for any advice!
