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 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jan 2004
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select records in between dates

    Hi,

    I have a database with d (day) m (month) and y (year) as fields....

    I also have a page with this html code in it

    Code:
    From
              <select name="frommonth" class="txtbox" id="frommonth" style="width:85">
                <option value="01" selected>January</option>
                <option value="02">February</option>
                <option value="03">March</option>
                <option value="04">April</option>
                <option value="05">May</option>
                <option value="06">June</option>
                <option value="07">July</option>
                <option value="08">August</option>
                <option value="09">September</option>
                <option value="10">October</option>
                <option value="11">November</option>
                <option value="12">December</option>
              </select>
              <select name="fromday" class="txtbox" id="fromday" style="width:45">
                <option value="01" selected>1</option>
                <option value="02">2</option>
                <option value="03">3</option>
                <option value="04">4</option>
                <option value="05">5</option>
                <option value="06">6</option>
                <option value="07">7</option>
                <option value="08">8</option>
                <option value="09">9</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
                <option value="13">13</option>
                <option value="14">14</option>
                <option value="15">15</option>
                <option value="16">16</option>
                <option value="17">17</option>
                <option value="18">18</option>
                <option value="19">19</option>
                <option value="20">20</option>
                <option value="21">21</option>
                <option value="22">22</option>
                <option value="23">23</option>
                <option value="24">24</option>
                <option value="25">25</option>
                <option value="26">26</option>
                <option value="27">27</option>
                <option value="28">28</option>
                <option value="29">29</option>
                <option value="30">30</option>
                <option value="31">31</option>
              </select>
              <select name="fromyear" class="txtbox" id="fromyear" style="width:65">
                <option value="2003">2003</option>
                <option value="2004">2004</option>
                <option value="2005" selected>2005</option>
                <option value="2006">2006</option>
                <option value="2007">2007</option>
                <option value="2008">2008</option>
              </select>
    to
    <select name="tomonth" class="txtbox" id="tomonth" style="width:85">
      <option value="01" selected>January</option>
      <option value="02">February</option>
      <option value="03">March</option>
      <option value="04">April</option>
      <option value="05">May</option>
      <option value="06">June</option>
      <option value="07">July</option>
      <option value="08">August</option>
      <option value="09">September</option>
      <option value="10">October</option>
      <option value="11">November</option>
      <option value="12">December</option>
    </select>
    <select name="today" class="txtbox" id="today" style="width:45">
      <option value="01" selected>1</option>
      <option value="02">2</option>
      <option value="03">3</option>
      <option value="04">4</option>
      <option value="05">5</option>
      <option value="06">6</option>
      <option value="07">7</option>
      <option value="08">8</option>
      <option value="09">9</option>
      <option value="10">10</option>
      <option value="11">11</option>
      <option value="12">12</option>
      <option value="13">13</option>
      <option value="14">14</option>
      <option value="15">15</option>
      <option value="16">16</option>
      <option value="17">17</option>
      <option value="18">18</option>
      <option value="19">19</option>
      <option value="20">20</option>
      <option value="21">21</option>
      <option value="22">22</option>
      <option value="23">23</option>
      <option value="24">24</option>
      <option value="25">25</option>
      <option value="26">26</option>
      <option value="27">27</option>
      <option value="28">28</option>
      <option value="29">29</option>
      <option value="30">30</option>
      <option value="31">31</option>
    </select>
    <select name="toyear" class="txtbox" id="toyear" style="width:65">
      <option value="2003">2003</option>
      <option value="2004">2004</option>
      <option value="2005" selected>2005</option>
      <option value="2006">2006</option>
      <option value="2007">2007</option>
      <option value="2008">2008</option>
    </select>
    <input type="submit" value="Sort">
    and this PHP code

    PHP Code:
    if (isset($_GET['today'])) {
    $query_timecards "SELECT * FROM timecards WHERE m >= '$frommonth' AND m <= '$tomonth' AND d >= '$fromday' AND d <= '$today' AND y >= '$fromyear' AND y <= '$toyear' ORDER by y DESC, m DESC, d DESC";

    although this seems logical, it doesn't work

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have you echoed the query to make sure your variables are being set correctly?

    I would suggest, though, that you store your dates as one column of the DATE type. It makes dealing with dates much easier and more efficient.

  • #3
    Regular Coder
    Join Date
    Jan 2004
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, everything seems ok....

    Here's an example:

    PHP Code:
    SELECT FROM timecards WHERE project_number 20011 AND client_symbol 10012 AND >= '01' AND <= '01' AND >= '01' AND <= '01' AND >= '2005' AND <= '2006' ORDER by y DESCm DESCd DESC 
    This is when i do "show me everything from January 1, 2005 to January 1, 2006...

    which in my DB means everything

  • #4
    Regular Coder
    Join Date
    Jan 2004
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wait,

    I'm re-reading the query...and it doesn't make sense...

    I'm asking it

    "Give me a m (month) that is bigger than or equal to '01' AND a m (month) that is smaller than or equal to '01'

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, in that case, what you're really asking for is January 1, 2005 or January 1, 2006. 'm >= 1 and m <= 1' means m can only equal 1.

  • #6
    Regular Coder
    Join Date
    Jan 2004
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yeah....that makes sense.....ur right....is there a way around it without changing my DB field to DATE, because that would require me to re-enter the records?

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It wouldn't be that hard to recreate your data as a DATE type, and it would be worth the trouble. Create the new field, then try this query:

    Code:
    UPDATE
     table
    SET
     newdatefield = CONCAT(y,'-',m,'-',d)
    Then drop your three other fields.

    The usual caveats apply, though -- backup your data, test it on a non-live table first, etc.

  • #8
    Regular Coder
    Join Date
    Jan 2004
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok....will do ...


    thanx


  •  

    Posting Permissions

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