Insert multidimensional array information into database

Question

This seems like it would have a really easy solution...but I've had a hard time figuring it out. I need an array to go into a database. For example:

$usa = array(
'idaho' => array(
           county1 => array(
                     'pocatello', 'arimo', 'downey'
                      ),
           county2 => array(
                      'bear', 'cuprum', 'mesa'
                      )
'iowa' => array(
           county1 => array(
                     'des moines', 'adel', 'desoto'
                      ),
           county2 => array(
                      'douglas', 'grant', 'jasper'
                      )

);

I tried this method of inserting into the database:

foreach($usa as $state => $county){
    foreach($county as $name => $city){
    $s=$state;
    $county_name = strtolower($name);
    $city = strtolower($city);
    $us = "INSERT INTO us
           SET state='{$s}',county='{$county_name}',city='{$city}'
           ";
    $us_result = mysql_query($us,$connection);
         }
  }

I believe the problem is the foreach (passing the state variable into the second foreach loop). I have tried this a number of different ways. Thanks in advance for your help!

***Note: everything works great when I remove the $s=$state variable and the state='{$s}' portion of the insert. I still cant get it to insert the state

clayjar · Accepted Answer · 2012-05-10 20:26:30Z

1

There are two major problems.

First, your array isn't delimited properly and it's better to use a single or double quote for the county names, which you are referring to as strings anyway:

$usa = array(
  'idaho' => array(
       'county1'=>array(
                 'pocatello', 'arimo', 'downey'
                  ),
       'county2'=>array(
                  'bear', 'cuprum', 'mesa'
                  )),
  'iowa' => array(
       'county1'=>array(
                 'des moines', 'adel', 'desoto'
                  ),
       'county2'=>array(
                  'douglas', 'grant', 'jasper'
                  ))

);

Secondly, there should be one more foreach loop to account for city names:

foreach($usa as $state => $county){
  foreach($county as $name => $city){
    foreach ($city as $cityname) {
      $s = $state;
      $county_name = strtolower($name);
      $city = strtolower($cityname);
      $us = "INSERT INTO us SET state='{$s}',county='{$county_name}',city='{$city}'";
      echo $us.'<br>';
    }
  }
}

Hope this helps!

answered May 10, 2012 at 20:26

clayjar

665 bronze badges

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

4 Comments

Ja͢ck Over a year ago

+1, though you're missing the emphasis on proper escaping using mysql_real_escape_string

clayjar Over a year ago

I'd prefer the questioner learn the pertinent things, few things at a time, because he/she seems to be a novice PHP programmer.

Ja͢ck Over a year ago

I'm a firm believer in immersion; what if you never saw this person again and he would continue writing his queries like that ;-)

clayjar Over a year ago

Well, thanks to you, if he's up to it he may have looked into what mysql_real_escape_string is.

EmmanuelG · Accepted Answer · 2012-05-10 20:26:40Z

First. As @itsmeee stated, you were missing one more foreach that iterates through the array of cities in that county. Besides that, your input array is missing 2 close parens to wrap the array of data per state.

If you fix that I would just do:

$us     = "INSERT INTO us (state, county, city) VALUES ";
$values = array();

foreach($usa as $state => $county){
    foreach($county as $name => $cities){
        foreach($cities as $city){
            $county_name = strtolower($name);
            $city        = strtolower($city);
            $values[]    = "('{$state}','{$county_name}','{$city}')";
        }
    }
}

$us       .= join(',',$values);
$us_result = mysql_query($us,$connection);

This would build the insert string and do all the inserts in one shot. You could also do individual inserts but for a data set this small doing one large insert is more efficient.

Good Luck!

Jeroen · Accepted Answer · 2012-05-10 20:11:56Z

0

Your INSERT query is incorrect. Try this:

$us = "INSERT INTO us (state, county, city) VALUES ('" . mysql_real_escape_string ($s) . "', '" . mysql_real_escape_string ($county_name) . "', '" . mysql_real_escape_string ($city) . "')";

answered May 10, 2012 at 20:11

Jeroen

13.3k4 gold badges45 silver badges63 bronze badges

1 Comment

Ja͢ck Over a year ago

Good tip, but this is not the biggest problem :)

itsmeee · Accepted Answer · 2012-05-10 20:20:11Z

0

Seems like you've missed one more foreach:

foreach($county as $name => $cities) {
 foreach ($cities as $city) {
....

answered May 10, 2012 at 20:20

itsmeee

1,64511 silver badges12 bronze badges

Collectives™ on Stack Overflow

Insert multidimensional array information into database

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related