Page 1 of 1

[Solved] Multidimensional Array Manipulation

Posted: Wed Aug 29, 2007 8:05 am
by zoonose
I have created a similar multidimensional array (as shown below) and would like to know the best way to add this to a mysql database using an INSERT string.

I understand i could use the implode() function to create a string, but i have had trouble using it on a 3D array. Apparently
implode(" ",implode(" ",implode(" ",$array))) doesn't really work!!

Not sure where to go from here!

Code: Select all

$array = array
(
  "0"=>array
  (
	"0"=>array ( 
			"Date",
			"ID",
			"Family"
  		   ),
	"1"=>array ( 
			"Date",
			"ID",
			"Family"
  		   )
  ),
  "1"=>array
  (
	"0"=>array ( 	"Date",
			"ID",
			"Family"
  		   ),
	"1"=>array ( 
			"Date",
			"ID",
			"Family"
  		   )
  ),
  "2"=>array
  (
	"0"=>array ( 
			"Date",
			"ID",
			"Family"
  		   ),
	"1"=>array ( 
			"Date",
			"ID",
			"Family"
  		   )
  )
);
I should add this is to go into a database with the structure:
3 columns "Date" , "ID", "Family"

Posted: Wed Aug 29, 2007 8:31 am
by xpgeek

Code: Select all

foreach($arr as $arry){
foreach($arry as $values){
$val = implode($values);
//here exec sql command to insert val
}
}
Sorry you need to use foreach in foreach.

Posted: Wed Aug 29, 2007 11:11 am
by zoonose
xpgeek wrote:

Code: Select all

foreach($arr as $arry){
foreach($arry as $values){
$val = implode($values);
//here exec sql command to insert val
}
}
Sorry you need to use foreach in foreach.
Hi Thanks for you advice!!!

But i can see a small problem here, If i do this then i get a string >

Code: Select all

$value1 $value2 $value3 $value1 $value2 $value3 $value1 $value2 $value3 etc...
For a MySQL INSERT i need a string

Code: Select all

( ($value1,$value2,$value3),($value1,$value2,$value3),($value1,$value2,$value3) );
How do i format a 3D array using implode, and maintaining the "row" structure required for input into the database. In other words i am trying to input about 100 rows with 3 variables from an array ??

Posted: Wed Aug 29, 2007 8:00 pm
by Stryks
Maybe I'm not understanding your post, but why are you wanting to use implode at all?

You could possibly try storing the entire array using serialize.

But it seems that you want to store the values as rows in a database, with the three columns you specified. In this case you would use the code posted by zoonoose, minus the implode line. You then insert the values per your sql example except with one query per set.

The latter approach might leave you with issues if you're wanting particular items to 'belong' to specific groupings ... but you haven't asked about that yet so I'll assume it's not the issue.

But as I said, maybe I'm misunderstanding.

Posted: Wed Aug 29, 2007 8:50 pm
by Stryks
To clarify (untested):

Code: Select all

foreach($arr as $arry){
   foreach($arry as $data){
      // INSERT INTO tablename (Date, ID, Family) VALUES ($data["0"], $data["1"], $data["2"]);
   }
} 

Posted: Thu Aug 30, 2007 12:58 am
by Stryks
Or, (just because I got bored during a meeting this afternoon) you could extend your arrays a little in the build stage and just automate the write parameters.

Create array:

Code: Select all

  $array = array(
    "0"=>array(
        "0"=>array(
            "Date"=>"data",
            "ID"=>"data",
            "Family"=>"data"
            ),
        "1"=>array(
            "Date"=>"data",
            "ID"=>"data",
            "Family"=>"data"
            )
        ),
    "1"=>array(
        "0"=>array(
            "Date"=>"data",
            "ID"=>"data",
            "Family"=>"data"
            ),
        "1"=>array(
            "Date"=>"data",
            "ID"=>"data",
            "Family"=>"data"
            )
        )
    );
Loop and write:

Code: Select all

  foreach($array as $arr) {
      foreach($arr as $data) {
          $keys = implode(", ", array_keys($arr));
          $values = implode(", ", $data);
          
          echo "INSERT INTO tablename ($keys) VALUES ($values)<br>";
      }
  }
I know ... kind of pointless replying when I don't really understand what you're trying to achieve ... but hey, I got bored. 8)

Posted: Thu Aug 30, 2007 3:26 am
by zoonose
Stryks wrote:
Loop and write:

Code: Select all

  foreach($array as $arr) {
      foreach($arr as $data) {
          $keys = implode(", ", array_keys($arr));
          $values = implode(", ", $data);
          
          echo "INSERT INTO tablename ($keys) VALUES ($values)<br>";
      }
  }
I know ... kind of pointless replying when I don't really understand what you're trying to achieve ... but hey, I got bored. 8)
NOPE !! Not pointless Stryks - because it solves my problem elegantly. However, now to add another problem...
I guess the reason why i am reluctant to use this is because the array generated may have as many 100 rows/entries for the database.

This would mean a lot of queries directed at the database? Would this be ok in terms of server load? and speed of the script? What would you do? Or would you worry at all?

Posted: Thu Aug 30, 2007 3:42 am
by VladSun
You may generate the SQL query like this:

Code: Select all

INSERT INTO `tablename` VALUES ($val1_1, $val1_2, $val1_3), ($val2_1, $val2_2, $val2_3), (.....
It has to be done in the loop and you will be able to execute a single SQL query outside the loop.

Posted: Thu Aug 30, 2007 5:32 am
by Stryks
I'm not overly sure that I'd be too worried about server load. You could try using innodb transactions and see if that speeds things up, or you could just give it a quick twist and do it all in one step.

Remove the $keys line, because it's not really needed. Doing it my way means you could have dynamic inserts with whatever data needed to be saved for each item. Doing it all in one step assumes that all data arrays have the same data set, so you can just either hard code the column destinations or just omit them.

Then replace the echo with:

Code: Select all

$datum[]  = "($values)";
Then after both loops are finished, something like:

Code: Select all

echo "INSERT INTO tablename VALUES (" . implode("), (", $datum) . ")";
Should do the trick. Not tested though, so forgive me if it does nothing but produce pretty errors.

Posted: Thu Aug 30, 2007 5:43 am
by Stryks
Ahem. :oops:

Code: Select all

    foreach($array as $arr) {
      foreach($arr as $data) {
          $values[] = implode(", ", $data);
      }
    }
    echo "INSERT INTO tablename VALUES (" . implode("), (", $values) . ")";
Considering I said I didn't know why you were using implode, I've sure given it a good serve here.

Oh well.

Let me know how you go anyhow.

Posted: Thu Aug 30, 2007 9:11 am
by zoonose
Wicked!

Will give this a shot, many thanks!

Just tested..

Posted: Fri Aug 31, 2007 8:58 am
by zoonose
Stryks.. Works a treat!!

many thanks.. TA!