Could really do with some help. So I've got the 2 arrays like so

Array
(
    [0] => UK
    [1] => UK
    [2] => USA
    [3] => USA
    [4] => USA
    [5] => France
)
Array
(
    [0] =>  London
    [1] =>  Manchester
    [21] => New York
    [22] => Florida
    [23] => Hawaii 
    [24] => Paris
)

And I need to insert it into mysql database like so

Country  City
UK       London, Manchester
USA      New York, Florida, Hawaii
France   Paris

I'm okay with PHP but I'm really stuck with this one. Don't have any code to show as I've absolutely got no idea how to accomplish this. Can someone help me achieve this please. Thanks in advance for the help.

share|improve this question
6  
Do you already have the database? Do you know how to connect to it from php? Are you SURE you want to insert a comma separated list into a column called "City"? – Jeremy Lawson May 11 '12 at 8:20
Take a look at foreach(). – CodeCaster May 11 '12 at 8:20
Why are the indices of New York - Paris not 2-5? that would make this a lot easier. – dragon112 May 11 '12 at 8:22
What have you tried? – cbuckley May 11 '12 at 8:24

6 Answers

up vote 0 down vote accepted
$countries = array("UK","UK","USA","USA","USA","FRANCE");
$cities = array(0=>"London",1=>"Manchester",23=>"New York",24=>"Florida",25=>"Hawaii",26=>"Paris");

$combined = array();
$cities = array_values($cities);
foreach ($countries as $index => $country)
{
    $combined[$country][] = $cities[$index];    
}

foreach ($combined as $country => $cityList)
{
    $sql = sprintf("INSERT INTO `MyTable` (Country,City) VALUES ('%s','%s')",$country,implode(',',$cityList));
share|improve this answer
Thanks... its pretty much what I want but not quite... I need it to do an insert like below. How do I go about doing that? INSERT INTO MyTable (Country,City) VALUES ('UK','London, Manchester') INSERT INTO MyTable (Country,City) VALUES ('USA','New York, Florida, Hawaii') INSERT INTO MyTable (Country,City) VALUES ('France','Paris') – Dev May 11 '12 at 8:59
Do you mean sending the query to the database? – gunnx May 11 '12 at 9:07
Yup when I run your above code it inserts each country into new row ... is there a way to concatenate the results and insert it? – Dev May 11 '12 at 9:17
You just want one row inserted? Can you write how the query should look? – gunnx May 11 '12 at 9:32
I was getting confused... sorry. this works sorry and many thanks for the helps. Much appreciate it =) – Dev May 11 '12 at 9:33

The first array is $countries and the second array is $cities.

$map = array();
while (!empty($countries) && !empty($cities)) {
  $map[array_shift($countries)][] = array_shift($cities);
}

foreach ($map as $country => $cities) {
  $cities = join(', ', $cities);
  // TODO: insert row with $country and $cities into database.
}

As as side note, I suspect your database is not normalized. Read up on normalization of databases, if you have not already done so.

share|improve this answer

Have a read through this tutorial: http://www.tizag.com/mysqlTutorial/

share|improve this answer

If you reindex your arrays you can then iterate over both of them in parallel:

$cities = array_values($cities);
$countries = array_values($countries);
for($i = 0; $i < count($cities); ++$i) {
    $city = $cities[$i];
    $country = $countries[$i];

    // now do something with $city and $country
}

Now let's see what the "do something" part might be. If you want to concatenate city names by country, you need to group by country first. So let's do that:

$groups = array();

for (...) {
    $city = $cities[$i];
    $country = $countries[$i];

    if (!isset($groups[$country])) $groups[$country] = array();
    $groups[$country][] = $city;
}

At this point you should do a print_r($groups) to see that we have grouped the cities. Converting each group to a concatenated string is easy:

foreach($groups as &$group) { // ATTENTION: iterating by reference
    $group = implode(', ', $group);
}

And do a print_r again.

However, this type of database structure doesn't look right (depends on the application, so not definite). Perhaps you should consider having a table with country/city columns, so each pair of elements from the input arrays would correspond to a row.

share|improve this answer
thanks very much. appreciate your help – Dev May 11 '12 at 9:34
  1. You must binding country code with city like this:

    Array
    (
        [0]['code'] => UK
        [0]['city'] => London
    )
    ....
    
  2. If you have multidimensional array with => pairs, you can create appropriate sql's using foreach and case (if) statements.
share|improve this answer

So I am not sure what you are trying to do or why you have two arrays for what should probably be 1, this is assuming of course that the city is always tied to its correct country. What I would do is say is Manchester and London are both in the UK so their country field in the database would be be UK, and then you could call them with a simple MySQL query asking for all the cities where the country is equal to the city code

So your database would look like this

 ID      CITY       COUNTRY
 1       London     UK
 2       Manchester UK
 3       New York   USA
 4       Florida    USA
 5       Hawaii     USA
 6       Paris      France

And then you can call it with a MySQL query just as you want,

 SELECT * FROM table WHERE country = 'UK'

This would then give you a result for London and Manchester

This should get you going in the right direction

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.