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


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


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

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.

Yeah, everything seems ok....

Here's an example:


SELECT * FROM timecards WHERE project_number = 20011 AND client_symbol = 10012 AND m >= '01' AND m <= '01' AND d >= '01' AND d <= '01' AND y >= '2005' AND y <= '2006' ORDER by y DESC, m DESC, d DESC


This is when i do "show me everything from January 1, 2005 to January 1, 2006...

which in my DB means everything

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'

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.

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? :eek:

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:


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.

ok....will do ...


thanx