#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Talking Date comparison issue


    Hello, Am a PHP developer , am working a hotel application but the issus am having here maynot be so heard to achive but am stock here and i need your help here.
    I have two table which are "blockedrooms" and "bookingstest"

    blockedrooms has
    blockedrooms (id, id_item, the_date, id_state, id_booking, roomno, hotel_id, entryCode, status, entryDate, blockedby, unblockedby, unblockedDate) VALUES
    (16, 1, '2012-11-08', 4, 0, 100, '1', '20121109103253', 0, '2012-11-09 10:32:53', '2', '', '0000-00-00 00:00:00'),
    (17, 1, '2012-11-09', 5, 0, 101, '1', '20121109103320', 0, '2012-11-09 10:33:20', '2', '', '0000-00-00 00:00:00'),

    bookingstest has

    INSERT INTO bookingstest (id, id_item, the_date, id_state, id_booking, roomno, hotel_id, entryCode, status, entryDate) VALUES

    (18, 1, '2012-11-08', 5, 0, 101, '1', '20121109103320', 0, '2012-11-09 10:33:20'),
    (20, 1, '2012-11-08', 4, 0, 100, '1', '20121109103253', 0, '2012-11-09 15:01:27'),
    (21, 1, '2012-11-09', 4, 0, 100, '1', '20121109103253', 0, '2012-11-09 15:02:15'),
    (22, 1, '2012-11-09', 5, 0, 101, '1', '20121109103320', 0, '2012-11-09 15:02:15'),
    (23, 1, '2012-11-13', 4, 0, 100, '1', '20121109103253', 0, '2012-11-12 13:15:14'),
    (24, 1, '2012-11-12', 5, 0, 101, '1', '20121109103320', 0, '2012-11-12 13:15:14'),
    (25, 1, '2012-11-15', 4, 0, 100, '1', '20121109103253', 0, '2012-11-15 10:26:39'),
    (26, 1, '2012-11-14', 5, 0, 101, '1', '20121109103320', 0, '2012-11-15 10:26:39');

    I want to update a bookingstest, but i need to first get the dates that i already have in the bookingstest then compare it with my date range (This date range is the range of dates between a specified Startdate and Enddate) to avoid dupplication in bookingstest and the date range comparesion will start from *the_date* in blockedrooms till today. please how do i achive it i just want to get the dates that are not in bookingstest for each id_state from their start date (2012-11-08 and 2012-11-09) my code for that is below.


    $rst=mysql_query("select * from blockedrooms where status=0 order by the_date asc") or die(mysql_error);
    //$start = "2012-02-05";
    while ($row = mysql_fetch_array($rst,MYSQL_ASSOC)){

    $start= $row['the_date'];
    $end = date('Y-m-d');
    $init_date = strtotime($start);
    $dst_date = strtotime($end);

    $offset = $dst_date-$init_date;

    $dates = floor($offset/60/60/24) + 1;

    for ($i = 0; $i < $dates; $i++)
    {
    $newdate = date("Y-m-d", mktime(12,0,0,date("m", strtotime($start)),
    (date("d", strtotime($start)) + $i), date("Y", strtotime($start))));


    //echo $rsts['the_date'];
    //echo $newdate ."<br>";

    $rsts=mysql_query(" select * from bookingstest where the_date IN ('$newdate') and entryCode=".$row['entryCode']." and roomno= ".$row['roomno']." order by roomno asc") or die(mysql_error);
    while($rw = mysql_fetch_array($rsts,MYSQL_ASSOC)){
    echo $rw['the_date']. ' - '.$rw['roomno']."<br>";

    }

    }



    }
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2001
    Location
    UK
    Posts
    543
    Rep Power
    58
    It might be easier to get the date range directly from mysql in your initial query with something like:
    Code:
    "SELECT * FROM blockedrooms
    WHERE the_date  BETWEEN '$startdate' AND '$enddate'

IMN logo majestic logo threadwatch logo seochat tools logo