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
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)
$end = strtotime("+2 days", $timestamp);
b) Modify the endpoints of the range in your MySQL query using DATE_ADD and DATE_SUB
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.
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 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?
Put it in yyyy-mm-dd format and MySQL will understand. Otherwise use STR_TO_DATE() in your query to parse that string.
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.
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.
$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
$name = $xmlReader->name;
if ($name == “product”
$product[$name] = $xmlReader->readString
$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
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: “
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
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?
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