Multidimensional array to Mysql

[devofash]

Hi,

 

Could really do with some help.

 

So I've got a Multidimensional array like so

 

Array
(
    [Asia] => Array
        (
            [0] => China
            [1] => Japan
        )

    [Europe] => Array
        (
            [0] => France
            [1] => Germany
            [2] => Spain
            [3] => UK
        )
)

 

I have a Mysql table (continents pre-filled)

 

Continent - Country
Asia       
Europe

 

I need to update country where continent is xxx

 

So I need a query/php function which will when run would fill in "china, japan" into "asia" and "france, germany, spain, uk" into "europe", like so

 

Continent - Country
Asia          - China, Japan
Europe     - France, Germany, Spain, UK

 

I don't really know where to start so I don't have a code. Would really appreciate some assistance.

 

 

[tipsmail7]

Iterate that array in first level (Continent)

Then, you could use implode function in PHP to concatenate array to string

 

foreach ($arr as $continent => $countries) {
$val = implode(", ", $countries); // $val = 'China, Japan'
$sql = "UPDATE table_name SET Country='$val' WHERE Continent='$continent'";
}

[ManiacDan]

You shouldn't be storing data like this.  You should have:

CONTINENT

-------------

CONTINENT_ID

NAME

 

 

COUNTRY

-------------

COUNTRY_ID

CONTINENT_ID

NAME

 

 

That way, you don't have delimited strings in your database (which is [almost] always wrong) and you'll be able to do real queries on this data.

 

Then, your inserts would be easier too, without having to use implode().

[devofash]

Iterate that array in first level (Continent)

Then, you could use implode function in PHP to concatenate array to string

 

foreach ($arr as $continent => $countries) {
$val = implode(", ", $countries); // $val = 'China, Japan'
$sql = "UPDATE table_name SET Country='$val' WHERE Continent='$continent'";
}

 

Perfect many thanks.

[devofash]

You shouldn't be storing data like this.  You should have:

CONTINENT

-------------

CONTINENT_ID

NAME

 

 

COUNTRY

-------------

COUNTRY_ID

CONTINENT_ID

NAME

 

 

That way, you don't have delimited strings in your database (which is [almost] always wrong) and you'll be able to do real queries on this data.

 

Then, your inserts would be easier too, without having to use implode().

 

Thanks. Point noted. But I'm filling in for someone and don't really want to change too much of their code.