Display Range of Date depend on input

[guidely]

Hi, Yesterday I try to figure out that if we input day and the system will add day to current date and display result but I got problem example below

input 1 day to add

table

1st record 2012-05-05 Add 1 day 2012-05-06

input another 3 day

table

1st record 2012-05-05 Add 1 day 2012-05-06

2st record 2012-05-05 Add 3 day 2012-05-08

The problem is 2nd need to start from 2012-05-06 not current date here code part of my big system

It should be like

input 3 day 2st record 2012-05-06 Add 3 day 2012-05-09
input 4 day 3nd record 2012-05-09 Add 3 day 2012-05-12

THank

        if ($_REQUEST[roomrent_id] == '') {
            $sql_roomrent = "SELECT * FROM `roomrent` WHERE `guest_id`=(SELECT `id` FROM `guests` WHERE `room_id`='$_REQUEST[id]' AND `checkout_datetime`='0000-00-00 00:00:00') ORDER BY `id` DESC";
         } else {
          $sql_roomrent = "SELECT * FROM `roomrent` WHERE `guest_id`=(SELECT `id` FROM `guests` WHERE `room_id`='$_REQUEST[id]' AND `checkout_datetime`='0000-00-00 00:00:00') AND `id`='$_REQUEST[roomrent_id]'  ORDER BY `id` DESC";
         }
  $q_roomrent = mysql_query($sql_roomrent);
 while ($row_roomrent = mysql_fetch_assoc($q_roomrent)) {

$day_paid = $row_roomrent['day_pay'];  // This what I called day input from DB
 $date_4 = $row_roomrent['start_datetime'];  //called the date that checkin 
$stop_date = date("Y-m-d", strtotime($date_4. '+'. $day_paid.'day')); // added together
 mysql_query("UPDATE `roomrent` SET `date`='$stop_date' WHERE `guest_id`=(SELECT `id` FROM `guests` WHERE `room_id`='$_REQUEST[id]' AND `checkout_datetime`='0000-00-00 00:00:00') AND `id`= $roomrent_id "); // store back to other field


<td><font color="#<?= $font_color ?>"><?= $date_4 ?></font></td>
<td><font color="#<?= $font_color ?>"><?= $days_total ?> <?= DAYT ?></fo;nt></td>
<td><font color="#<?= $font_color ?>"><?= $day_paid ?> <?= DAYT ?></font></td>
 <td><font color="#<?= $font_color ?>"><?= $stop_date ?> </font></td>

[vibhaJ]

Use join for good practice:

"SELECT roomrent.* FROM `roomrent`
    LEFT JOIN guests ON guests.id = roomrent.guest_id
    WHERE `room_id`='$_REQUEST[id]' AND `checkout_datetime`='0000-00-00 00:00:00' AND `id`='$_REQUEST[roomrent_id]' ORDER BY `id` DESC"

Also why are you updating in whle loop? Where is the code for inserting into table?

[guidely]

The Insert is in other file of system , I don't know Why I update in while loop. The program is not makke by my self it programming who make system but he left :( that part is my code

[diafol]

Your SELECT SQL would probably be better using JOIN syntax than subqueries.
IN addition, you'd probably be better just making one big update rather that update on every loop iteration.
Perhaps if you gave an explanation of what you're trying to do too.
I'm guessing that you have a room booker and that you're trying to automatically calculate the leaving date when you have a booking for a start date and the number of nights.
What I don't understand is the 'automatically use the leaving date of the previous booking as the start date of the next booking'.

[guidely]

What I try to do was firstly I gonna tell this is just part of 1000 of line of code, So was I try is if guess pay for roomrent they can able to chosse how may day they want to pay after the paid date that check in will add depend how many day he paid then display as rang Example

total night from check in currently 4 night check in date was 28/04/2012

so customer paid for first 2 night so 28/04/2012 + 2 day = 30/04/2012

so table should display

paid for 28/04/2012 to 30/04/2012

next he come paid another 1 day so now from 30/04/2012 + 1 day = 01/05/2012

Table should look like this

paid for 28/04/2012 to 30/04/2012

paid for 30/04/2012 to 01/05/2012

SO am not really sure how to do it

[diafol]

OK, now I get you. As opposed to paying for the total stay in one go, they can pay in installments.
I still don't understand why a guest would have multiple unpaid stays though for one continuous stay. Would it not make more sense to just have date_from/date_to in the table 'days_paid'. That way you can calculate outstanding debts with a simple sql, e.g.:

SELECT guest_id,date_from,date_to,DATEDIFF(date_to, date_from) - days_paid AS owing FROM sometable WHERE days_paid <> DATEDIFF(date_from, date_to)

That's just one idea. There are many ways in which you could do it.
NB - the fields/tables in the example are made up - use your own.

[guidely]

Thank for Help but I cant use DATEDIFF() it php 5.3 or later on my server is php 5.2.17