inserting multiple incrementing values into same column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ruud
Forum Newbie
Posts: 16
Joined: Mon Oct 27, 2003 9:57 am

inserting multiple incrementing values into same column

Post 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:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
ruud
Forum Newbie
Posts: 16
Joined: Mon Oct 27, 2003 9:57 am

Post 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.
ruud
Forum Newbie
Posts: 16
Joined: Mon Oct 27, 2003 9:57 am

Post 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:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
ruud
Forum Newbie
Posts: 16
Joined: Mon Oct 27, 2003 9:57 am

Post by ruud »

thanks mac - i'll work on that :lol:
Post Reply