problem inserting an array into database

[slanton]

If I have the following problem.

$adults=$_POST['adults'];//lets say its 2
$children=$_POST['children'];//lets say its 3
$rooms =array('101','102');

foreach($rooms as $roomnumber){
$insertSQL = "INSERT INTO bookings (roomNumber, adults, children) VALUES ( '$roomnumber', '$adults', '$children')";
$Result1 = mysql_query($insertSQL) or die(mysql_error());
}

This works BUT..each room gets (say) 2 adults and 3 children whereas I want the numbers spread over each room so that room 101 gets maybe 2 people and room 102 gets 3 people or vice versa and not double -up on the number of overall people. In a real life situation the user would choose the number of rooms and adults etc so the solution would need to allow for this. Can anyone see a way of doing this?

 

 

 

 

 

 

 

[Barand]

Perhaps you could redesign your form

 

[pre]

    Room          Adults            Children

   

  +---------+    +----------+      +----------+

  |  101  |    |    2    |      |          |

  +---------+    +----------+      +----------+

 

  +---------+    +----------+      +----------+

  |  102  |    |          |      |    3    |

  +---------+    +----------+      +----------+

[/pre]

[slanton]

Hi Barry

No I can't do that. I am working on a complicated little function that I hope will sort it.

[Barand]

Do you hold "number of beds" for each room number?

[slanton]

Yes I do. If the room has 4 beds and there are 6 people they need two rooms and I need to be able to put 3 in each rather than 4 and 2 as it is more cost effective.

[per1os]

So basically if a room has 4 beds it can hold 6 people?

 

Wouldn't you just need to do 1 bed per person. If you have 6 people you need 6 beds, if the room has less than 6 beds you need to split the people up into 2 rooms instead of just one.

 

// 6 / 4 = 1.25 should go up to 2
     $numRooms = ceil($numStaying / $maxBeds); // round up to the next whole number
     $perRoom = ceil($numStaying / $numRooms); // Note this needs to be configured for odd numbers

     $roomNum = array("104", "105", "106");
     $i=1;
     while ($numRooms > $i) {
          // the - 1 since arrays are zero based
          $roomArr[$roomNum[($i - 1)]] = $perRoom;
      }

      print_r($roomArr);

 

Is that what you are looking for?

[pplNet]

totalAssign = 6ppl

Adults = 2

Children = 4

Room = 2

split them equaly in each room

 

 

 

 

averagePpl = round(6/2)  // 6ppl / 2rooms then round it up

Note: averagePpl can be ur numbers of bed in a room.

RemAssign = totalAssign

RemAdultsAssign = adults

RemChildrenAssign = Children

 

foreach($rooms as $roomnumber){

'assume that adults always set as priority then only children
if RemAdultsAssign > averagePpl {
	$adults = averagePpl
	$remAdultsAssign
= $RemAdultsAssign - $averagePpl
	$Remassign = $Remassign - $averagePpl
}else{
	$adults = $RemAdultsAssign
	$Remassign = $Remassign - $RemAdultsAssign
	$remAdultsAssign
= 0				
}

//same concept goes to $children

$insertSQL = "INSERT INTO bookings (roomNumber, adults, children) VALUES ( '$roomnumber', '$adults', '$children')";
$Result1 = mysql_query($insertSQL) or die(mysql_error());
}

[pplNet]

sorry that you have to correct my coding....

[slanton]

Thanks for your input

Frost110

If a room has 4 beds it can only hold 4 people so if there are 6 people they need two rooms. That situation is not so difficult. eg 2 adults and 4 kids ...2/2 and 4/2 =1 adult and 2 kids in each room. The real problem arises when there is 1 adult and 5 kids...1/2 and 5/2 =.5 adults and 2.5 kids in each room!! Somehow from that I have to get 1 adult and 2 kids in the first room and 0 adults and 3 kids in the second room. I tried your code but got a timeout from an endless loop or something.

 

pplnet I am working through your code ..but I can't follow it.

[btherl]

I think a "complicated little function" is the way to go.  To write it you need a full list of the various business constraints you need to work with (which we don't have).  Then you can write a function that implements those constraints.

 

If you give us all the constraints, we can probably come up with an implementation for them.

 

It may be easiest to just write a list of rules like "If splitting 1 adult and 5 kids between 2 rooms, do it this way".  If someone is booking large numbers of rooms at one time, you can fall back to manual assignment (or give a temporary automatic but non-optimal assignment, which can be reviewed later)

[slanton]

Yes I think you are right. The business constraints are fairly general in any accommodation. Each room only sleeps a certain number of people and if the number of people is more that that they need to book another room. All I am really trying to do is not double up on the number of people.

[Barand]

Given 2 rooms, each with a capacity of 4, and 2 adults and 3 children to accomodate you can have

[pre]

  room      adults    children

    1            1          3

    2            1

   

    1            1          1

    2            1          2

   

    1            2         

    2                        3

   

    1            2          2

    2                        1

   

    1            2          1

    2                        2[/pre]   

However, if a business rule is "No kids without accompanying adult" then the last 3 options must be rejected 

[slanton]

Just to complicate it even further some places have a base rate per room for 2 people eg $100 and then an additional fee per extra person eg $20.

 

So if you have 2 rooms and 5 people...4 people in one room and 1 in the other costs

Room 1                                              Room2

base rate = $100                                  base rate = $100

2 extra    = $40

Total      = $140                                  For 2 rooms $240

 

which is more expensive than having 3 in one room and 2 in the other

 

Room 1                                                Room 2

base rate = $100                                  base rate = $100

1 extra    = $20

Total      = $120                                  For 2 rooms $220

 

So to be fair the room assignment has to be the cheapest option. My head is starting to hurt.

[Barand]

So target is to get occupancy for  the rooms as equal as possible