[RESOLVED] Select records fortnightly

[retro]

My current project is for a delivery sheet creator.

Whilst I have not yet implemented it, I have to have it so that upon entering the order, you must select whether the order is a one-off, or whether it should be repeated weekly on the same day, or whether it should be repeated fortnightly.

What I think I will do is have a 'recur' field in my 'orders' table. The data from the form (e.g. a combo box) will go in this form, and be interpreted as follows:

0 = One-off order
1 = Weekly order
2 = Fortnightly order

I have an input box with a calendar, which is used to select the required date. This then redirects to a page with a query, which displays the results.

For testing purposes, I have used the following query, which works for weekly orders:

$result = mysql_query ("SELECT * FROM orders LEFT JOIN customers ON (customers.ID = orders.customer) WHERE DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . ')"); 

This works great - it shows items on the correct day.

I cannot work out how to do it for fortnightly orders, though.

I'll clarify what I mean here.

Say I have the following orders:

Order 1: 29 April, Weekly
Order 2: 6 April, Fortnightly
Order 3: 13 April, Weekly

These are all Tuesdays. If I were to search the 13 April, currently it would show me all 3. However, I don't want it to show me order 2 - that should appear for the 6th or the 20th, but not the 13th.

How can I go about this?

Someone suggested I use the following, but it gives me an error:

WHERE DATEDIFF(date, ' . $DelDate . ') MOD 14 = 0

[bpat1434]

Sounds to me like you need a set of AND and OR clauses in your where:

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)

Seems to work for me when I tested locally with some very very raw data.

Here's a table dump for you:

DROP TABLE IF EXISTS `orders`;
CREATE TABLE  `orders` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `recur` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

In it I have:

INSERT INTO `orders` 
VALUES (NULL, '0', '2008-04-29'), 
       (NULL, '2', '2008-04-06'), 
       (NULL, '1', '2008-04-13')

And my result as I run the query based on two different dates:

QUERY:
SELECT * FROM orders
WHERE (`recur` = 1 AND DAYOFWEEK(date)=DAYOFWEEK('2008-04-20'))
   OR (`recur` = 2 AND DATEDIFF(date, '2008-04-20')%14=0)

RESULT:
2, 2, '2008-04-06 12:30:00'
3, 1, '2008-04-13 12:30:00'



QUERY:
SELECT * FROM orders
WHERE (`recur` = 1 AND DAYOFWEEK(date)=DAYOFWEEK('2008-04-13'))
   OR (`recur` = 2 AND DATEDIFF(date, '2008-04-13')%14=0)

RESULT:
3, 1, '2008-04-13 12:30:00'

Hope that helps.

Oh, and the "%" is a modulo operator. I think what they forgot to tell you was that MOD is a function and is to be used like:

SELECT * FROM orders
WHERE (`recur` = 1 AND DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . '))
   OR (`recur` = 2 AND MOD(DATEDIFF(date, ' . $DelDate . '), 14)=0)

[retro]

Thanks very much for the explanation! Sorry for the delay in replying.

I have tried using the modulo operator as suggested. To keep it simple, I took my current, working query and substituted it thusly:

$DelDate = $_POST["DelDate"];
$result = mysql_query ("SELECT * FROM orders LEFT JOIN customers ON (customers.id = orders.customer) WHERE DATEDIFF(date, ' . $DelDate . ')%14=0");
$total = mysql_num_rows($result); 

I still get the same error I did before:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ../viewreport.php on line 19

Line 19 is the $total line.

Do you have any ideas why I am getting this error? It worked fine for DAYOFWEEK.

[bpat1434]

Show us the entire code section dealing with the query and the error. So like lines 14 to 20 or so (or more if necessary).

[retro]

It is the very start of the php file, bar the header and the code to connect to and check the database.

<?
//RETURN RESULT
$DelDate = $_POST["DelDate"];
$result = mysql_query ("SELECT * FROM orders LEFT JOIN customers ON (customers.id = orders.customer) WHERE DATEDIFF(date, ' . $DelDate . ')%14=0");
$total = mysql_num_rows($result);
$query = mysql_real_escape_string("SELECT id,item FROM products ORDER BY id");
$result2 = mysql_query ($query);

//store product names in array
$c = 0;
$itemNo = Array();

while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
{
  $itemNo[$c] = Array($c,$row["item"]);
  $c++;
}
?>

<h2><? echo($total . " RECORD(S) FOUND."); ?></h2></div>
<p>

I ran a Query in phpMyAdmin as follows:

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE DAYOFWEEK( date ) = DAYOFWEEK( '2008-04-24' )

This returned results.

I then changed the query to:

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE DATEDIFF(date, '2008-04-24')%14=0

This threw up an error:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( date , '2008-04-24' ) % 14 = 0
LIMIT 0, 30' at line 1

Incidentally, it is a MySQL v4 database.

[bpat1434]

Ah, I see....

You need to edit the query to this:

SELECT * FROM orders LEFT JOIN customers ON (customers.id = orders.customer) WHERE DATEDIFF(date, '$DelDate')%14=0

[retro]

Oops, I am an idiot! I put $DelDate in the query on phpMyAdmin. Of course, I meant to change that to '2008-04-24' also. I did so (updated the above) and it still threw up the error.

I changed the code as you suggested. I still got the error. So I put your code into the phpMyAdmin query, changing $DelDate for a date, and still got the error.

I noticed that date is changed to DATE in the query. Is it a reserved keyword? I tried changing it, but it made no difference.

[bpat1434]

Date is a reserved keyword, meaning you should encase it in "`" backticks. Of course, to mitigate future issues, you should always encase column names in back-ticks It's just good practice.

[retro]

Ah, I'll bear that in mind, thanks!

Well, I did that, but it still didn't help. I then noticed that in phpMyAdmin, DATEDIFF was in red. I thought that was just because they coloured everything differently, but it got me wondering. So I looked up DATEDIFF, and it needs v4.1.1. My host are running 4.0.26! So I asked them why that was, and they said they have now implemented a MySQL 5 server, set up a database, I copied the data across and hey presto, it works!!

Why they didn't update to the last version of 4, I don't know! Grr!

Thanks very much for the help!