CODEWITHSUNDEEP
phpmysqlarraysexplode
velvetmonster
velvetmonster

Reputation: 87

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

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'");

        }

Upvotes: 0

Views: 180

Answers (1)

vijaykumar
vijaykumar

Reputation: 4806

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");

    }

Upvotes: 1

Related Questions