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?
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.
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
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
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.
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.
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 "
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