How to give or take 3 days in mysql

I have a database with records with different start and end dates.
I have a query in php that displays the date from one range to another and it works. In my table sometimes there are no records for the dates selected but I want to display records from alterantive dates for instance, the next couple of days. Is there a query I can use to show the next 2 days as well as the dates selected by the user. something like give or take 2 days?

My page is written in PHP

Any advise would be appreciated

Thanks

You can either:

a) Modify the endpoints of the range submitted by the user in your PHP code (using strtotime() or simply adding/subtracting the appropriate number of seconds to a UNIX timestamp representation)

Example:

$end = strtotime("+2 days", $timestamp);

b) Modify the endpoints of the range in your MySQL query using DATE_ADD and DATE_SUB

Example:

SELECT something FROM table WHERE date < DATE_ADD('2010-02-21', interval 2 day)

Thanks for the reply. the only issue with the add_date is I wouldnt know what date the user selects. For instance, i have a calendar that they pick the departure date and arrival date, so each time it would be a differnt date. the DATE_ADD(‘2010-02-21’) will specify a given date rather than the date selected by the user. this is my query:

$result = mysql_query(“SELECT departureDate, returnDate, type where departureDate = ‘$dep’ and returnDate = ‘$ret’ and type = ‘$typeof’ order by returnDate asc”)

so its the departureDate that will nedd the interval adding to 1 or 2, the same holds for returnDate.

any ideas?

thanks again


select departureDate, 
         returnDate, 
         type 
 where departureDate between '$dep' - interval '2' day and '$dep' + interval '2' day
    and returnDate between '$ret' - interval '2' day and '$ret' + interval '2' day
   and type = '$typeof' 
 order by returnDate

swamboogie:

The scripts doesnt return any entries. I tried removing the quotes but didnt work. any ideas?

What does the query look like after the variables have been substituted? Do you have the date format that mysql requires? That is, yyyy-mm-dd.

good point: The dates on my page are in the following format: dd/mm/yyyy

so nothing is returned when i used it. but when i just use it like normal (where datecolumn = variablesdate) it works.

do i need to change the date format for the interval to work ?if so how?

thanks

Put it in yyyy-mm-dd format and MySQL will understand. Otherwise use STR_TO_DATE() in your query to parse that string.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

The interval option is the best option. The date in mysql table are stored in the following format e.g 23/02/2010, so not sure how to change it to the mysql format yyyy-mm-dd. I have 2 pages: one to insert the values in the table and the second one retrieve them, the issue i need to display the date in the uk format that is dd-mm-yyyy and they are already inserted in this format in the mysql table

I tried using your suggestion (‘$dep’ - interval ‘2’ ) but it didnt work

sorry I m stuck with this

What is the column type for departureDate and returnDate? Unless you’re storing dates as strings, which you shouldn’t, there is no actual human readable storage format – there is only how you choose to format the data coming out. Formatting a date however you want it to look is trivial both in SQL and PHP, so don’t consider that when constructing your queries.

H Dan,

The columns type for departureDate and returnDate were VARCHAR so that may explain why the interval query did not work. So I changed them to Date type, and then run the insert query and they were inserted as 0s ( 0000-00-00). My dates are in the format dd-mm-yyyy so Mysql did not understand it and hence inserted 0s instead

How would I convert the date I have in my Php string for departure date and return date into mysql format?

And also once inserted in the table in the mysql format, I will need to convert them back to the uk format, that is dd-mm-yyyy after I have queried the table.

Thanks again

$date = date('Y-m-d', strtotime($weird_date_format));

or do some basic string manipulation to rearrange it

use the DATE_FORMAT function for this

Ok this is my attempt:

First Of all its the insert part into mysql table. I set the departuredate type to date.

the date comes from an xml file and is stored as dd-mm-yyyy.
My php script read the xml file data, stores in an array called $product and then insert it into the mysql table. This works without problem except for the date part. as we know its not compatible with mysql format. So I tried to convert it to mysql format before it is inserted into the mysql table. At the moment, the conversion doesnt seem to work as the date column shows 0nly 0s. This is an extract of my PHP script:

$product = array() //name of the array where the file is stored
;
while ($xmlReader->read())
{

$name = $xmlReader->name;
if ($name == “product”
)
break;

switch($name)
{

case “departurepoint”
:
case “destination”
:
case “departuredate”
:

if (!isset($product[$name])
)

$product[$name] = $xmlReader->readString
( );

break;
}

function saveProduct($product)
{

saveProduct($product);
$date1 = date(‘Y-m-d’, strtotime($product[“departuredate”]) ); // convert the departuredatae into mysql format

$result = mysql_query(
"INSERT INTO tablename SET "

.
departurepoint='” . escape($product[“departurepoint”]) . “‘, "
.
"destination=’” . escape($product[“destination”]) . "', "

.
departureDate='” . escape($product[“date1”]) . "', "

Note the script works except for the departure bit

Any ideas?

thanks again

You assigned the date to the variable $date1 but you do not use this variable in your query.

Good point. I did set it to something like this: “departureDate= $date1”

but it still inputing 0s in the date field. not sure if the syntax is correct ?

that syntax ~will~ be accepted by mysql

if $date1 resolves to an integer in yyyymmdd format, you get a valid date, otherwise it defaults to the “zero date”

you had dashes between the year and month, and between the month and day, and no quotes around them, so mysql parsed this as an arithmetic expression: 2010 minus 2 is 2008, minus 25 is 1983, and 1983 is not in yyyymmdd format

mysql will also accept ‘$date1’ which it parses as a string, and then other things happen

:slight_smile:

Hi r937,

i makes sense what you said, but it still doesnt like the ‘$date1’. I tried brackets as well but in vain. do you know the correct systax?

thanks


function saveProduct($product) 
{

saveProduct($product);

This is infinite recursion. Doesn’t your code just run endlessly without producing output once you call saveProduct?

The code loops through the xml file and inserts the other values correctly in the table but it ignores the date