Page 1 of 1

Separate array values from one key and insert to databas

Posted: Sun Nov 30, 2014 11:19 pm
by tkp99172
I designed the system for booking rooms and store the data by multi-dimensional array form.

Here is the array (data):

Code: Select all

    $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?

Code: Select all

     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:

Code: Select all

$bookingInformation = "INSERT INTO `bookRecord` (`userID`, `room_Number`, `date`, `frome`, `to`) 
								VALUES " . implode(',', $BookArrRecord);
The checking of the query:

Code: Select all

        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:

Code: Select all

        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')"
          ......
        }
Did I have syntax error on the for-loop? OR it is not recommend to store values in array based on the $rmID?
Thanks for any help.

Re: Separate array values from one key and insert to databas

Posted: Mon Dec 01, 2014 7:35 am
by Celauran
Your data is in a reasonable format to begin with, so it isn't really clear to me why you're breaking it apart into a number of arrays and then trying to put them back together. Why not build up your query values on your first pass through the multidimensional array?

Re: Separate array values from one key and insert to databas

Posted: Sat Dec 13, 2014 4:02 pm
by Pazuzu156
When building up your query, try avoiding multidimensional arrays like the one you have in your example. Structuring data is very important, but doing it this way, and then having multiple nested foreach loops is overkill. If you absolutely must structure your info into a multidimensional array, consider having a base key, and values follow suit to this, even if it's another array. Also consider writing a method to parse your data. That'll make obtaining the info easier. Another note, the older mysql methods are deprecated, consider looking into PDO. Refer to a post I made a while back on the subject: viewtopic.php?f=1&t=139881#p693760