Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Selecting data within certain dates problem

    I am trying to make a report generation page that will display data between certain dates. I first need to select all data that is in the current month. I want to be able to select all data in any given month by the user entering in the month.

    The second thing I need is to be able to select the data on a weekly basis...like week 1 2006 = bla bla, week 2 2006 = bla bla, week 3 2006 = bla bla, etc. I would like to be able to have it go through week 52 of the year, but be able to show like Month = June, but Week 1 of June 2006 = bla bla, Week 2 of June 2006 = bla bla, etc.

    The third thing I need is to be able to generate Y-T-D reports for the current year only. I have used the links below as help, but I get errors. I will post my errors & code below. I can't even get this to work to get the data from the last 7 days.

    http://codingforums.com/showthread.php?t=80896

    My Error: Parse error: syntax error, unexpected T_STRING in C:\XAMPP\xampp\htdocs\My Sites\Members - MGE\cp\reports\monthly.php on line 18
    PHP Code:
        $sql2 "SELECT SUM(profit) AS sum_profit2
                FROM $order_table
                WHERE order_date <= Date_Format(Now(), "
    %Y-%m-%d") //This is line 18
                AND order_date >= Date_Format(Sub_Date(Now(), INTERVAL 7 DAY), "
    %Y-%m-%d")";
        
    $result2 mysql_query($sql2) or die(mysql_error());
        
    $i2 mysql_fetch_array($result2);
        
        echo 
    "<br><hr>";
        echo 
    $i2['sum_profit2']; 
    Thanks in advance for your help!

  • #2
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    change to '%Y-%m-%d' on line 18 and 19..

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by fci
    change to '%Y-%m-%d' on line 18 and 19..
    I did that I got this error 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 '), '%Y-%m-%d')' at line 4

    I think it is talking about line 4 of my query since there is nothing on line 4 of my php page.

    This is the entire page
    PHP Code:
    <?PHP
        $sql 
    "SELECT SUM(profit) AS sum_profit FROM $order_table WHERE order_status = 'Filled' ";
        
    $result mysql_query($sql) or die(mysql_error());
        
    $i mysql_fetch_array($result);
        
        echo 
    "<br><strong>";
        echo 
    $i['sum_profit'];
        echo 
    "</strong><hr>";

        
    $sql2 "SELECT SUM(profit) AS sum_profit2
                FROM $order_table
                WHERE order_date <= Date_Format(Now(), '%Y-%m-%d')
                AND order_date >= Date_Format(Sub_Date(Now(), INTERVAL 7 DAY), '%Y-%m-%d')"
    ;
        
    $result2 mysql_query($sql2) or die(mysql_error());
        
    $i2 mysql_fetch_array($result2);
        
        echo 
    "<br><hr>";
        echo 
    $i2['sum_profit2'];
    ?>

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I think you want to use the "Date_Sub" function rather than the "Sub_Date" function (which doesn't exist).

  • #5
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by lansing
    I am trying to make a report generation page that will display data between certain dates. I first need to select all data that is in the current month. I want to be able to select all data in any given month by the user entering in the month.
    in the where clause you could do: DATE_FORMAT(your_date, '%m-%Y') = '12-2006'

    The second thing I need is to be able to select the data on a weekly basis...like week 1 2006 = bla bla, week 2 2006 = bla bla, week 3 2006 = bla bla, etc. I would like to be able to have it go through week 52 of the year, but be able to show like Month = June, but Week 1 of June 2006 = bla bla, Week 2 of June 2006 = bla bla, etc.
    in a group by you could do: DATE_FORMAT(your_date, '%U-%Y')

    refer to http://dev.mysql.com/doc/refman/5.0/...functions.html for details

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    Quote Originally Posted by Fumigator
    I think you want to use the "Date_Sub" function rather than the "Sub_Date" function (which doesn't exist).
    subdate() exists, and is an alias for date_sub and will require fewer keystrokes to change


  •  

    Posting Permissions

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