0

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

    }

    }



    }
4
Contributors
3
Replies
6
Views
5 Years
Discussion Span
Last Post by cereal
2

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
2

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 />";
}
1

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!

Edited by cereal

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.