mysql not returning query

[phingoc]

Hi.

 

i want to sort out mysql rows and display them only between dates. The date is stored inn dd.mm.yyyy... i have now 3 entries in the DB (just for testing);

 

 

04.12.2011

02.12.2011

04.12.2011

 

when i try to fetch rows between dates:

01.12.2011 and 03.12.2011, i get 1 return witch is correct..

01.12.2011 and 05.12.2011, i get 3 returns witch is correct..

03.12.2011 and 05.12.2011, i get 2 returns witch is correct..

 

But.... when i try between 01.01.2000 and 01.01.2020, i get 0 returns..

why?

 

I works when between 01.01.2000 and 31.12.2020...

 

form

<form method="POST" action="listebetween.php" target="_BLANK">
<table width="600" border="0">
       <tr>
           <td width="50%" align="center">Fra dato:<input type="text" name="fra" maxlength="10" size="12"></td>
           <td width="50%" align="center" rowspan="2"><INPUT TYPE="submit" VALUE="Utfør"></td>
       </tr>
       <tr>
           <td width="50%" align="center">Til dato:<input type="text" name="til" maxlength="10" size="12"></td>
       </tr>
</table></form>

 

script

<?php

include("mysqlconnect.php");

$fra = $_POST['fra'];
$til = $_POST['til'];


$query = "SELECT * FROM prod2011 WHERE dato BETWEEN '$fra' AND '$til'";

$result = mysql_query($query) or die(mysql_error());

echo "<center><table border='1'>";
echo "<tr> <th width='90'>Dato</th> <th width='90'>Ordre</th> <th width='50'>Solgt</th> <th width='50'>Brukt</th> <th width='100'>Kommentar</th> </tr>";


while($row = mysql_fetch_array( $result )) {


echo "<tr><td>";
echo $row['dato'];
        echo "</td><td>";
echo $row['ordre'];
echo "</td><td>";
echo $row['solgt'];
echo "</td><td>";
echo $row['brukt'];
echo "</td><td>";
echo $row['kommentar'];

echo "</td></tr>"; 
}

echo "</table>";
?>

[Pikachu2000]

Dates need to be stored in YYYY-MM-DD format, in a DATE or DATETIME type field in the database.

[phingoc]

if i change it to date, mysql will automaticly sett the current server date when the row is created?

 

since in the add form, i need to add another date, than the current server date..

[Pikachu2000]

No, that only happens if you set it up that way. The format is YYYY-MM-DD for a reason. In that manner, the values go from most significant to least significant which allows you do perform comparisons, mathematical operations, etc. using the many MySQL date/time functions that are available.

[phingoc]

thank you. that sorted it out, but that brings a new problem that i will make a new tread for under php help..

 

becouse in norway, we use dd.mm.yyyy, so all my inputs will be in that format, and then the dates dont get stored right in the db.