SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    $end strtotime("+2 days"$timestamp); 
    b) Modify the endpoints of the range in your MySQL query using DATE_ADD and DATE_SUB

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

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    swamboogie:

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

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    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.

  7. #7
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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/...functions.html

  9. #9
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $date date('Y-m-d'strtotime($weird_date_format)); 
    or do some basic string manipulation to rearrange it

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hm9 View Post
    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.
    use the DATE_FORMAT function for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You assigned the date to the variable $date1 but you do not use this variable in your query.

  16. #16
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hm9 View Post
    ...something like this: "`departureDate`= $date1"
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #19
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function saveProduct($product
    {

    saveProduct($product); 
    This is infinite recursion. Doesn't your code just run endlessly without producing output once you call saveProduct?

  20. #20
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code loops through the xml file and inserts the other values correctly in the table but it ignores the date

  21. #21
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you printed out what's in $date, and printed out the query, so that you can verify they're what you expect?

    Do some debugging. Make sure every line of code works as expected. Start at the top.

  22. #22
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok there is some progress now:

    When I print $date1 after converson, It prints it in the correct format but incorrect date

    it always print 1969-12-31

    It should print 2010-05-10 instead. I think the conversion code is incorrect for some reason

    this is the conversion code:

    $date1 = date('Y-m-d', strtotime($product["departuredate"]) );


    Note If I print $product["departuredate"] ); on its own before the conversion, it prints the correct date but in the old format e.g 10-05-2010


    can you spot any problem with the conversion code?

    thanks again

  23. #23
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That format must be too ambiguous for strtotime to guess what that date is (as evidenced by you saying it should be May 10th and I would've read that as October 5th).

    Anyway, for you:
    PHP Code:
    $parts split("-"$product['departuredate']);
    $date1 $parts[2] . '-' $parts[1] . '-' $parts[0]; 

  24. #24
    SitePoint Evangelist
    Join Date
    Dec 2003
    Location
    uk
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok but your code prints the date in this format dd-mm-yyyy e.g. 10-05-2010 I want it to print as yyyy-mm-dd instead because in mysql table the departuredate has this format yyyy-mm-dd

    Is that what you meant?

  25. #25
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hmm no, it doesn't.

    PHP Code:
    //this is what you said $product['departuredate'] holds
    $product['departuredate'] = '10-05-2010';

    $parts split("-"$product['departuredate']);
    $date1 $parts[2] . '-' $parts[1] . '-' $parts[0];

    echo 
    $date1
    Output:

    Code:
    2010-05-10
    Which is what you said it should print.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •