query database between 2x dates

[jacko_162]

I thought i would start a new thread to make it easier for people to understand what im rying to achieve.

 

i am querying my database to pull down data between 2x dates set in the URL.

 

i will have the following url:

 

test.php?graph_start=November+3%2C+2009&graph_end=May+9%2C+2010

 

which is the following dates;

Start

November 3, 2009

End

May 9, 2010

 

my format in the database is stored as May 9, 2010

$todaydate = date("F j, Y");

 

what im trying to do is query database between the 2x variables passed in the URL.

 

can anyone help me?

[litebearer]

maybe...

 

$query="select * from table where date_column between '$startdate' and '$enddat'";

[jacko_162]

thats a version i have triend with no results being received, but also no errors;

 

 

here is my code snippet. (yes im connected to DB in header file also;

 

<table width="100%" border="0" cellspacing="0" cellpadding="4">
                            <tr>
                              <td colspan="2" valign="top">
						  <?php
                              $todaydate = date("F j, Y");
						  $oldDate = date("F j, Y", strtotime("6 months ago"));
						  ?>
                              <form method="GET">
Start: 
    <input name="graph_start" type="text" class="date" id="datepicker" value="<?php echo $oldDate; ?>" size="16"/>
End: 
<input name="graph_end" type="text" class="date" id="datepicker2" value="<?php echo $todaydate; ?>" size="16"/>
<input type="submit" value="Submit" />
<br />
                              <br />
                              Viewing Results Between: <strong><span class="time"><?php
$start = $_GET['graph_start'];
if ($start)
{
echo "$start";
}
else
{
echo "$oldDate";
} ?></span></strong>
- <strong><span class="time">
<?php
$end = $_GET['graph_end'];
if ($end)
{
echo "$end";
}
else
{
echo "$todaydate";
} ?></span></strong>
                                                            <br />
                              </form> </td>
                            </tr>
                            <tr>
                              <td colspan="2" valign="top"><?php

function imp($char,$tag){
foreach($char as $key=>$value){
$char[$key] = $value;
}
$char = implode($tag,$char);
return $char;
}

$sql = "SELECT * FROM tests WHERE date BETWEEN '$start' AND '$end' ORDER BY ID ASC LIMIT 0,10";

$query = mysql_query($sql);
while($row = mysql_fetch_array($query)){
$date[] = $row[date];
$day[] = $row[day];
$test1[] = $row[test1];
$test2[] = $row[test2];
$test3[] = $row[test3];
$test4[] = $row[test4];
$test5[] = $row[test5];
$test6[] = $row[test6];
$test7[] = $row[test7];
$test8[] = $row[test8];
$test9[] = $row[test9];
$test10[] = $row[test10];
$test11[] = $row[test11];
$test12[] = $row[test12];
$test13[] = $row[test13];
$test14[] = $row[test14];
}

?>

[PFMaBiSmAd]

That's because you can only do greater-than/less-than date comparisons when the fields making up the date are left-to-right, year, month, day, i.e. yyyy-mm-dd. This is why DATE data types in databases use a yyyy-mm-dd format, so that you can directly order them and do greater-than/less-than comparisons.

[jacko_162]

ok so if i wanted to write the query to be more than and less than how would i do this from the variables with my above coding?

 

and also the page is viewed without the variables, if this is the case i have the start date 6 months less than the current date.

 

can this be done?

[jacko_162]

can anyone help me?

[jacko_162]

ok i have tried to test the following (knowing i have 4 values in the DB with member_id='1';

 

SELECT * FROM `tests` WHERE date <= 'May 9, 2010' AND  date >= 'January 1, 2010' AND member_id = '1'

 

this should pull in the following 4x results;

 

member id;            date;            test1   

      1                  May 6, 2010        1

      1                  May 4, 2010        9

      1                  April 20, 2010      8

      1                  April 14, 2010      7

 

but upon running the above query i only get the top 2x rows returned?

 

but if i modify the sql query to be less than May 9, 2010 ONLY i getthe above 4 results?

[PFMaBiSmAd]

In both of your threads on this, I have mentioned the yyyy-mm-dd format for a reason. If you read my post above in this thread, you will discover that you cannot compare dates unless they are in a yyyy-mm-dd format. You can only compare dates (greater-than/less-than) when the same position in each of the values being compared has the same magnitude (you must use leading zeros in mm and dd) and each position to the left in a value has a greater magnitude than the position to its' right.

 

Until your query looks like the following (or using the BETWEEN ... AND ... syntax) and with the values coming from the date field also in the yyyy-mm-dd format, your query will not work -

 

SELECT * FROM `tests` WHERE date <= 'yyyy-mm-dd' AND  date >= 'yyyy-mm-dd' AND member_id = '1'

 

You can use mysql date functions in your query to get your date column values into the correct format, however, the DATE data type exists for several reasons and you should change your design to store values as a DATE data type. Once you do so, youy queries will be faster, your data will take less storage space, and you will be able to directly compare and sort your dates in a query.

[jacko_162]

In both of your threads on this, I have mentioned the yyyy-mm-dd format for a reason. If you read my post above in this thread, you will discover that you cannot compare dates unless they are in a yyyy-mm-dd format. You can only compare dates (greater-than/less-than) when the same position in each of the values being compared has the same magnitude (you must use leading zeros in mm and dd) and each position to the left in a value has a greater magnitude than the position to its' right.

 

Until your query looks like the following (or using the BETWEEN ... AND ... syntax) and with the values coming from the date field also in the yyyy-mm-dd format, your query will not work -

 

SELECT * FROM `tests` WHERE date <= 'yyyy-mm-dd' AND  date >= 'yyyy-mm-dd' AND member_id = '1'

 

You can use mysql date functions in your query to get your date column values into the correct format, however, the DATE data type exists for several reasons and you should change your design to store values as a DATE data type. Once you do so, youy queries will be faster, your data will take less storage space, and you will be able to directly compare and sort your dates in a query.

 

ok so i will work on changing the date formate now to yyyy-mm-dd.

 

once this is done manually i can then sort date ranges i hope.

 

can you also help me with the coding for the sql query when i view the page with variables in the URL and if no variables exists run another query?

[jacko_162]

ok so now all dates are in the right setup "yyyy-mm-dd"

 

and i am using your coding above;

$sql = "SELECT * FROM `tests` WHERE date <= '$end' AND  date >= '$start' AND member_id = '1' ORDER BY ID ASC LIMIT 0,10";

 

it all works great, unless i view the page with no variables in the URL yet. how can i edit the database query to check if variables are in the URL and if they dont exists run a different query?

 

here is my DB query code snippet;

 

<?php

function imp($char,$tag){
foreach($char as $key=>$value){
$char[$key] = $value;
}
$char = implode($tag,$char);
return $char;
}
$sql = "SELECT * FROM `tests` WHERE date <= '$end' AND  date >= '$start' AND member_id = '1' ORDER BY ID ASC LIMIT 0,10";

$query = mysql_query($sql);
while($row = mysql_fetch_array($query)){
$date[] = $row[date];
$day[] = $row[day];
$test1[] = $row[test1];
$test2[] = $row[test2];
$test3[] = $row[test3];
$test4[] = $row[test4];
$test5[] = $row[test5];
$test6[] = $row[test6];
$test7[] = $row[test7];
$test8[] = $row[test8];
$test9[] = $row[test9];
$test10[] = $row[test10];
$test11[] = $row[test11];
$test12[] = $row[test12];
$test13[] = $row[test13];
$test14[] = $row[test14];
}

?>