update table from array field - code populates all fields with last value

Question

SOLVED. SEE BELOW.

Initial question:

I need to extract lat and lon from an array field (643844X2X48) in a mySQL table where the data look like this:

56.559482483762245;-104.0625;;SK;CA;S0J

I tried mysqli and pdo code and failed.

This php/mysql code updates all of the lat fields with the final loop lat value:

$sql = "SELECT 643844X2X48 from `reg_survey_643844`";
$array = mysql_query($sql);
while($row=mysql_fetch_assoc($array)){
foreach($row as $value){
    $result= explode(";", $value);
    $slice= (array_slice($result,0,1)); 
    $lat = array_shift(array_values($result));
    mysql_query("UPDATE reg_survey_643844 SET lat = '$lat'");
    }
    }

What am I doing wrong?

UPDATE:

Here is the working code.

$query = "SELECT * FROM reg_survey_643844 ORDER BY id";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
        $resultGeo= explode(";", $row["643844X2X48"]);
        $lat = $resultGeo[0];
        $lon = $resultGeo[1];
        $city = $resultGeo[2];
        $state = $resultGeo[3];
        $countryCode = $resultGeo[4];
        $zip = $resultGeo[5];
        $getid = $row['id'];
        $siteName = $row['643844X2X12'];

        $query = mysql_query("UPDATE reg_survey_643844 
                        SET siteName = '$siteName', lat = '$lat', lon = '$lon', city = '$city', state = '$state', countryCode = '$countryCode', zip = '$zip'
                   WHERE id = '$getid'");

        }

vijaykumar · Accepted Answer · 2013-11-20 07:45:37Z

1

Try this

 $sql = "SELECT 643844X2X48 from `reg_survey_643844`";
    $array = mysql_query($sql);
    while($row=mysql_fetch_assoc($array)){

    $result= explode(";", $row["your data"]);

    $lat = $result[0];
    mysql_query("UPDATE reg_survey_643844 SET lat = '$lat' whrere  your conditions");

    }

edited Nov 20, 2013 at 7:45

answered Nov 20, 2013 at 7:12

vijaykumar

4,8367 gold badges40 silver badges56 bronze badges

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

2 Comments

velvetmonster Over a year ago

Thanks for your answer, vijay4vijju. Much appreciated. I'm getting the same result, however. Quick question: by "your data" do you mean the text field column containing the georeferencing array data? That's what I used. I tried a few where conditions, but nothing seemed to work.

velvetmonster Over a year ago

Thanks vijay4vijju! With your guidance, I was able to get working code. Updated my question with it. Muchas gracias!

Collectives™ on Stack Overflow

update table from array field - code populates all fields with last value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related