Page 1 of 1

inserting multiple incrementing values into same column

Posted: Sun Nov 02, 2003 11:01 am
by ruud
hi there

can u help a newbie who's confused?!!?

i would be really grateful if anyone can help me out. i would like to know how i can insert a series of incrementing date values in date format (eg 2003-10-03, 2003-10-04, 2003-10-05 etc) into seperate rows of a table but in the same 'date' column i have created.

eg table with 3 columns iam using

ID #### booking id### date
1 ##### 1 ######## 2003-10-03
2 ##### 1 ######## 2003-10-04
3 ##### 1 ######## 2003-10-05

i am doing a booking system project in php that the user can hire out a motorhome for a set 7 days. i have managed to get a starting date from the user that then passes that value and the next seven day values from the same form value. However, using a basic insert sql command it only takes the initial date and inserts that. i am assuming that i need to more than the basic sql command i am using

thanks in advance for any help :lol:

Posted: Mon Nov 03, 2003 2:53 am
by twigletmac
Are you inserting a row for each day the motorhome is booked out? Personally, I would change the structure so that you have a starting and ending date in the same record. It will make it much easier to search for records and easier to insert and update them too.

Mac

Posted: Mon Nov 03, 2003 5:17 am
by ruud
thanks for replying mac :lol:

i did think of doing what you said - using a start date and an end date but the problem i have which i should have made clear first time around is that the user can also book a shorter break starting from a friday to the following monday. so i could do with a table where all the dates are displayed so there is no chance of any overlapping of bookings. there is only one motorhome so obviously double bookings would be bad.


do you have any suggestions? thanks again.

Posted: Mon Nov 03, 2003 5:20 am
by ruud
sorry about the cross post by the way - i thought it was more an sql question after i had posted the original. :oops:

Posted: Mon Nov 03, 2003 5:39 am
by twigletmac
Although the logic will have to be thought through a bit more, for a booking system I would still recommend having start and end date and not all the dates inbetween. However, to insert multiple records you could do something like the following:

First you need to know how many days the rental is for, then you need to run an INSERT query for each of those days, maybe something like this untested script:

Code: Select all

for ($i=0; $i < $days_rental; $i++) {
    $sql = "INSERT INTO table (ID, booking_id, date_booked) VALUES(NULL, $booking_id, DATE_ADD($start_date, INTERVAL $i DAY))";
     mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
}
Mac

Posted: Mon Nov 03, 2003 6:16 am
by ruud
thanks mac - i'll work on that :lol: