[RESOLVED] Select records fortnightly
Results 1 to 9 of 9

Thread: [RESOLVED] Select records fortnightly

  1. #1
    Member
    Join Date
    May 2008
    Posts
    52

    resolved [RESOLVED] Select records fortnightly

    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:

    PHP Code:
    $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:

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

  2. #2
    NMaOtBG bpat1434's Avatar
    Join Date
    Oct 2004
    Location
    Around 255.255.255.0
    Posts
    7,850
    Sounds to me like you need a set of AND and OR clauses in your where:

    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    SELECT * FROM orders
    WHERE (`recur` = 1 AND DAYOFWEEK(date)=DAYOFWEEK(' . $DelDate . '))
       OR (`recur` = 2 AND MOD(DATEDIFF(date, ' . $DelDate . '), 14)=0)
    Last edited by bpat1434; 05-15-2008 at 02:17 AM.

  3. #3
    Member
    Join Date
    May 2008
    Posts
    52
    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:

    PHP Code:
    $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:

    Code:
    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.

  4. #4
    NMaOtBG bpat1434's Avatar
    Join Date
    Oct 2004
    Location
    Around 255.255.255.0
    Posts
    7,850
    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).

  5. #5
    Member
    Join Date
    May 2008
    Posts
    52
    It is the very start of the php file, bar the header and the code to connect to and check the database.


    PHP Code:
    <?
    //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($result2MYSQL_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:

    Code:
    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:

    Code:
    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.
    Last edited by retro; 05-17-2008 at 12:53 PM.

  6. #6
    NMaOtBG bpat1434's Avatar
    Join Date
    Oct 2004
    Location
    Around 255.255.255.0
    Posts
    7,850
    Ah, I see....

    You need to edit the query to this:

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

  7. #7
    Member
    Join Date
    May 2008
    Posts
    52
    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.

  8. #8
    NMaOtBG bpat1434's Avatar
    Join Date
    Oct 2004
    Location
    Around 255.255.255.0
    Posts
    7,850
    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.

  9. #9
    Member
    Join Date
    May 2008
    Posts
    52
    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!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •