Date comparison issue

[Kcachilove]

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>";

    }

    }



    }

[LastMitch]

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"

I got no idea what you want to do? You just want to update the data from both tables? Was there any errors when you run the code>? If so then post the error.

Here is a query to update from the database:

UPDATE table_name
SET column1=value,
WHERE some_column=some_value

[diafol]

Here's some code that may help:

$start = '2011-09-26';
$end = '2011-10-05';

$disallowed = array('2011-09-28','2011-10-05'); //this is a mockup of your output from a DB query loop

$d = $start;
$date = new DateTime($d);
while($end > $d) {
    $date->add(new DateInterval('P1D'));
    $d = $date->format('Y-m-d');
    if(!in_array($d,$disallowed))echo $d . "<br />";
}

[cereal]

In addition to diafol suggestion, I think this can also be done at SQL level, but the_date should be converted to a datetime field in order to do comparisons, now I think it's a varchar. You need to use BETWEEN ... AND ... expression:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between

For example:

create table booking (
    id int(9) not null auto_increment primary key,
    the_date datetime null,
    id_state int(9) not null
) engine=myisam default charset=utf8;

insert into booking (the_date,id_state) values('2012-11-08 09:30:00','4'), ('2012-11-08 10:00:00','5'), ('2012-11-10 12:30:00','4'), ('2012-11-09 10:00:00','5'), ('2012-11-12 11:30:00','4'), ('2012-11-14 11:15:00','5');

select * from booking where the_date not between '2012-11-08' and '2012-11-10' and the_date <= curdate();

+----+---------------------+----------+
| id | the_date            | id_state |
+----+---------------------+----------+
|  3 | 2012-11-10 12:30:00 |        4 |
|  5 | 2012-11-12 11:30:00 |        4 |
|  6 | 2012-11-14 11:15:00 |        5 |
+----+---------------------+----------+

The output will include the end date, so just extend the range at script of SQL level. Bye!