[Solved] Multidimensional Array Manipulation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
zoonose
Forum Newbie
Posts: 8
Joined: Wed Aug 29, 2007 7:44 am

[Solved] Multidimensional Array Manipulation

Post 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"
Last edited by zoonose on Fri Aug 31, 2007 8:58 am, edited 1 time in total.
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post 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.
zoonose
Forum Newbie
Posts: 8
Joined: Wed Aug 29, 2007 7:44 am

Post 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 ??
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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"]);
   }
} 
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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)
zoonose
Forum Newbie
Posts: 8
Joined: Wed Aug 29, 2007 7:44 am

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post 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.
zoonose
Forum Newbie
Posts: 8
Joined: Wed Aug 29, 2007 7:44 am

Post by zoonose »

Wicked!

Will give this a shot, many thanks!
zoonose
Forum Newbie
Posts: 8
Joined: Wed Aug 29, 2007 7:44 am

Just tested..

Post by zoonose »

Stryks.. Works a treat!!

many thanks.. TA!
Post Reply