[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=InnoDB 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:

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

[color=green]RESULT:[/color]
2, 2, '2008-04-06 12:30:00'
3, 1, '2008-04-13 12:30:00'



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

[color=green]RESULT:[/color]
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 [color=blue]MOD(DATEDIFF(date, ' . $DelDate . '), 14)[/color]=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!