I designed the system for booking rooms and store the data by multi-dimensional array form.
Here is the array (data):
$recordBooking = array(
"111"=>array(
"date"=>array(
"29/10/2014"=>array(
array(
"from"=>1,
"to"=>3,
"user"=>"Amy",
"username"=>"CB34"
),
array(
"from"=>4,
"to"=>5,
"user"=>"Chars",
"username"=>"AA13"
)
),
"30/10/2014"=>array(
array(
"from"=>2,
"to"=>3,
"user"=>"Chars",
"username"=>"AA13"
),
array(
"from"=>3,
"to"=>6,
"user"=>"Gary",
"username"=>"SF11"
)
),
"02/11/2014"=>array(
array(
"from"=>1,
"to"=>3,
"user"=>"Billy",
"username"=>"V214"
)
),
.......
)
)
);
Also I was using the foreach-loop to separate the array values and store those data. And I using an array, $BookArrRecord, for gathering the values and insert into the database. It caused the error that $BookArrRecord can store the last values of each date only. Can anyone helps me to find out the problem and how to improve?
foreach($recordBooking as $key => $value){
$rmNum[] = $key;
foreach($value as $k => $v){
foreach($v as $bookDate => $array){
$bookingDate[] = $bookDate;
foreach($array as $room => $info){
foreach($info as $period =>$fromTo){
if($period=="username"){
$userID[] = $fromTo;
}
if($period=="from"){
$from[] = $fromTo;
}
if($period=="to"){
$to[] = $fromTo;
}
}
}
}
}
}
for($rmCount=1;$rmCount<count($userID);$rmCount++){//get the $userID to set the rows of $rmNum
$rmNum[]+=$rmID[0];
}
$BookArrRecord = array();
foreach($rmNum as $key => $value){
$BookArrRecord[] = "('" . $userID[$key] . "', '" . $rmNum[$key] . "', '". $bookingDate[$key] . "', '" . $from[$key] . "', '" . $to[$key] . "')";
}
The sql query:
$bookingInformation = "INSERT INTO `bookRecord` (`userID`, `room_Number`, `date`, `frome`, `to`)
VALUES " . implode(',', $BookArrRecord);
The checking of the query:
if(!mysql_query($bookingInformation, $dbConnection)){
die("Cannot access operation of Database(bookRecord): " . mysql_error()) . "<br>";
}else{
echo "Records added in table: BookingDate " . "<br>";
}
The results shows by using var_dump:
array(268) {
[0]=>
string(38) "('CB34', '111', '29/10/2014', '1', '3')"
[1]=>
string(38) "('AA13', '111', '30/10/2014', '4', '5')" //the date is wrong
[1]=>
string(38) "('AA13', '111', '02/11/2014', '2', '3')"
......
}
Thanks for any help.
I think your problem is that you have lots of different arrays ($userID, $rmNum, $bookingDate etc) which you're adding values to at different times. This makes it hard to keep track of which value in each array corresponds to which values in the other arrays. (It is possible, but tricky and likely leads to bugs).
An easier solution is to handle adding records to $BookArrRecord inside your foreach loops. That way you know that all the different values will be consistent.
For example:
$BookArrRecord = array();
$roomNumber = 1;
foreach($recordBooking as $key => $value){
$rmNum[] = $key;
foreach($value as $k => $v){
foreach($v as $bookDate => $array){
foreach($array as $room => $info){
$BookArrRecord[] = "('" . $info['username'] . "', '" . $roomNumber . "', '". $bookDate . "', '" . $info['from'] . "', '" . $info['to'] . "')";
$roomNumber++;
}
}
}
}
var_dump ($BookArrRecord);
Notice I've removed the innermost array. There's no need to loop over an array when you know which keys you're looking for.
As an aside, it's generally bad practice to include variables directly in a database query. You should escape them first. As this is a hardcoded array of values that you've specified, it's unlikely to go wrong now. But as soon as you start using any user-inputed values, make sure you escape them before they go anywhere near the query!