...

View Full Version : select records in between dates



grudz
06-15-2005, 08:38 PM
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

Kid Charming
06-15-2005, 09:11 PM
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.

grudz
06-15-2005, 09:46 PM
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

grudz
06-15-2005, 09:49 PM
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'

Kid Charming
06-15-2005, 09:53 PM
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.

grudz
06-15-2005, 09:54 PM
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:

Kid Charming
06-15-2005, 09:57 PM
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.

grudz
06-15-2005, 11:12 PM
ok....will do ...


thanx



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum