CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 14 of 14

Thread: php - mysql

  1. #1
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    php - mysql

    Hi.

    i have records with these fields, start date and end date in string format D-M-Y H:M:S
    i need to get 7 records the middle result should from now, start and end should be within the current time.

    i have no idea how to start with the query... thanks

  2. #2
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    [ moved ]
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  3. #3
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    Why don't you just use MySQL's BETWEEN method?
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  4. #4
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    i haveno idea how?

  5. #5
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    I just posted you a link of the documentation! Read it and you will know how.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  6. #6
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    ok i do this

    Code:
    $result = $oDatabase->Query('SELECT Start_date, End_date, Booking_id, Description FROM dms.bookings WHERE (Date_Format(Now(), \'%Y-%m-%d %H:%i:%s\') BETWEEN Start_date AND End_date) AND Product_id='.$zaal.' AND Description IS NOT NULL LIMIT 7');
    problem is...

    i want to get 7 results
    i now get the result that is the current event.
    i want now to add events to the query that are closest to the current event..

    say now i have the event rapper ice cube.
    but in a few hours i have the event nas.
    i want to have the list from current running event and the closest to now.

    thanks for your help
    Last edited by cj-wijtmans; January 26th, 2009 at 04:03 AM.

  7. #7
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    You really should read some documentation.

    Code:
    SELECT * FROM tablename ORDER BY timestamp DESC LIMIT 0, 7
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  8. #8
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    You dont understood my problem

    Code:
    $result = $oDatabase->Query('SELECT `Start_date`, `End_date`, `Booking_id`, `Description`, (DATE_FORMAT(NOW(), \'&#37;Y-%m-%d %H:%i:%s\') BETWEEN `Start_date` AND `End_date`) as `is_now` FROM `bookings` WHERE DATE_FORMAT(NOW(), \'%Y-%m-%d %H:%i:%s\') < `End_date` '.(($zaal > 0) ? 'AND `Product_id`='.$zaal.' ' : '').'AND`Description` IS NOT NULL ORDER BY `Start_date` ASC LIMIT 7');
    this code works.
    however i must ROTATE the events.
    the current event must be in the middle.
    and below that must be a few earlier events in order
    and above that musta be a few later events in order.

    it must rotate

  9. #9
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    I guess I'm not understanding your problem because I am having difficulty understanding your English.

    I understand that you want to pick a date and show a specific number of dates before, and a specific number after. However, what are you talking about by "rotating?"
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  10. #10
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    i am sorry for not writing proper english grammer....

    i want to have this result example.

    2009-01-10 - 2009-01-11 - after tomorow
    2009-01-09 - 2009-01-10 - tomorow
    2009-01-08 - 2009-01-09 - TODAy
    2009-01-07 - 2009-01-08 - yesterday
    2009-01-06 - 2009-01-07 - before yesterday

    after X interval this info is refreshed. ( i havent include time to make easier)
    so you can see TODAY will be "move" up in the rotation...

    thanks

  11. #11
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    Okay, what you need is to make use of PHP's built in functions date() and mktime(). Using these together, you can work with a temporary date formatted variable which can change giving you the "rotation" effect.

    PHP Code:
    // altering these variables will give you the rotation
    // setting them as session variables would get them across pages
    $y_temp 2009;
    $m_temp 1;
    $d_temp 27;

    // the following will give you the parts for the SQL query's BETWEEN method
    $begin date('Y-m-d'mktime(000$m_temp$d_temp 2$y_temp));
    $end date('Y-m-d'mktime(000$m_temp$d_temp 2$y_temp)); 
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  12. #12
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    this doe not help me at all cause i do not know the time diffrent between events.



    and there is no way those functions can help me.

  13. #13
    Join Date
    May 2002
    Posts
    10,943

    Re: php - mysql

    The example of dates you just gave prior to my post shows nothing about events. It shows 5 dates back to back. The code I posted does exactly what you said you wanted in post #10.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

  14. #14
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    77

    Re: php - mysql

    no it doesnt help me.

    and the dates ARE the events!

    i do not know the time difrence between events!
    the event before today
    it might as well been 2 years ago!
    or 2 mintus ago.

    i will never know!

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured