Select Upcoming Dates From Mysql?

[casey90]

hello,

 

i have a problem which is im creating a backend software, one of the features of the software that you add your invoices with due to time.

 

and it should pop up before three days of due to time.

 

so if i have this row in my sql:

 

xcompany 300$ dueto: 20/12/2012

 

and today is 17/12/2012.

 

so how should the code be structured to pick due to invoices for the three upcoming days???

 

ps:

 

dueto column in the mysql database is of the type date/time.

 

thanks for the help.

[Pikachu2000]

You should store dates in the proper format, which is YYYY-MM-DD, in a DATE type field. Then you can just use the native date/time functions in MySQL.

 

The query would probably end up something like one of these.

SELECT fields FROM table WHERE date = DATE_ADD(CURDATE(), INTERVAL 3 DAY)

SELECT fields FROM table WHERE date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)

[casey90]

hello thanks for the reply,

 

will the above queries only work if the date has the proper format in mysql ? like the one you mentioned yyyy-mm-dd

 

my date picker has the following format mm/dd/yyyy.

 

so i should change it to yyyy-mm-dd before storing it in mysql?

[Pikachu2000]

Yes, exactly.

[casey90]

okay one last thing, will the / or - between the numberts matter?

 

1990/12/15, 1990-12-15

 

is it the same for mysql? or it does matter?

[Pikachu2000]

The DATE field type will only accept YYYY-MM-DD format, with the dashes not slashes. Most datepickers are configurable to send the date in pretty much any format. If you need further help, just way so.

[Pikachu2000]

Before you go and change the field type in the actual table, you'll want to either run a query to UPDATE the values that are already there to the correct format, or add another field and up date the values into the new field. At a minimum, back up the table . . .

[casey90]

thanks man for the help.

 

im having problems changing date format

 

$db_date = "05-12-2011";
$new_format_date = date("Y-m-d", strtotime($db_date));
echo $new_format_date;

 

thats my code.

 

note that $db_date = "05-12-2011"; in the format of mm-dd-yyyy.

 

i want to change it to dd-mm-yyyy, so i can easily use the code above to format it finally to yyyy-mm-dd and store in DB.

 

but its not working.

[Barand]

strtotime does not recognise all formats (especially british dates)

 

see http://forums.phpfreaks.com/topic/269457-basic-problem/?do=findComment&comment=1385209

[PFMaBiSmAd]

You can also do this directly in ONE update query (a query without a WHERE clause will update all the rows at once, no looping required) using the mysql STR_TO_DATE function to convert your existing values to a DATE value - http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

[casey90]

$query = "SELECT * FROM `test` WHERE `dueto` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)";

 

this worked for me!

 

thanks for the help.