Transform an array to several sql statements

Question

I have a array that looks like this

Array
(
[provider] => Array
    (
        [id] => provider1
        [distribuitor] => Array
            (
                [0] => Array
                    (
                        [name] => distribuitor1
                        [machines] => Array
                            (
                                [0] => Array
                                    (
                                        [id] => MX3
                                        [installDate] => 2009-01-01
                                    )

                                [1] => Array
                                    (
                                        [id] => MX4
                                        [installDate] => 2008-01-01
                                    )

                                [2] => Array
                                    (
                                        [id] => MX7
                                        [installDate] => 2009-05-05
                                    )

                            )

                    )

                [1] => Array
                    (
                        [name] => distribuitor2
                        [machines] => Array
                            (
                                [0] => Array
                                    (
                                        [id] => FX3
                                        [installDate] => 2009-11-15
                                    )

                                [1] => Array
                                    (
                                        [id] => GX4
                                        [installDate] => 2008-07-22
                                    )

                                [2] => Array
                                    (
                                        [id] => RX7
                                        [installDate] => 2009-06-25
                                    )

                            )

                    )

            )

    )

)

from this array I want to generate a series of sql statements like this:

INSERT INTO table1 VALUES ('provider1','distrubuitor1','MX3','2009-01-01')
INSERT INTO table1 VALUES ('provider1','distrubuitor1','MX4','2008-01-01')
INSERT INTO table1 VALUES ('provider1','distrubuitor1','MX7','2009-05-05')
INSERT INTO table1 VALUES ('provider1','distrubuitor2','FX3','2009-11-15')
INSERT INTO table1 VALUES ('provider1','distrubuitor2','GX4','2008-07-22')
INSERT INTO table1 VALUES ('provider1','distrubuitor2','RX7','2009-06-25')

I'm not really well versed on array manipulations so my question is: how can I achieve this?

Bonus Point:
If you can provide links to resources where i can learn more about this things.
The answer should work preferably with 'unlimited' nested arrays.

P.D.
I'm not a native English speaker, I know the title of my question should be more generic, if you have a better title, please change it.

Paulraj · Accepted Answer · 2009-09-03 07:45:03Z

1

If array keys are static then hope it helps.

foreach($array as $key => $value)
{
    foreach($value['distrubuitor'] as $k => $val)
    {   
        foreach($val['machines'] as $m => $v)
        {
            mysql_query("INSERT INTO table1 VALUES ('".$value['id']."','".$val['name']."','".$v['id']."','".$v['installdate']."')");  
        } 
    }
}

answered Sep 3, 2009 at 7:45

Paulraj

3,4173 gold badges38 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alec Smart · Accepted Answer · 2009-09-03 06:37:13Z

0

You can use a simple foreach to acheive this.

foreach ($array as $a => $b) {
  foreach ...
    foreach ...
}

Link: http://us.php.net/manual/en/control-structures.foreach.php

answered Sep 3, 2009 at 6:37

Alec Smart

96.2k39 gold badges124 silver badges187 bronze badges

Comments

Scott Lundberg · Accepted Answer · 2009-09-03 13:28:22Z

0

You could use a recursive function so that it works for unlimited nesting:

Function FlattenArray($previousvalues,$currentarrray) {
   foreach($currentarray as $arraykey => $arrayitem) {
       if(is_array($arrayitem)) {
          $temp = $previousvalues.",'".$arraykey."'";
          FlattenArray($temp,$arrayitem);
       }
       else {
          $query = "INSERT INTO table1 VALUES(".$previousvalues.",'".$arrayitem."')";
          mysql_query($query);
       }
    }
 }

Then call your function with the proper starting variables. Make sure table1 has enough columns to handle the largest possible nesting or your mysql_query will fail.

answered Sep 3, 2009 at 13:28

Scott Lundberg

4452 silver badges10 bronze badges

Collectives™ on Stack Overflow

Transform an array to several sql statements

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related