Re: loop through a database and covert a northings and eastings value for each row

[__]

> Thanks for the reply really appreciate it. I’m getting there slowly. I’ve manged to loop through my DB and convert the values into latitude and longitude. Do you know how I can update each row in the loop with the new values?

I’m glad to hear that you’re making progress. However, it is difficult to offer advise when you ignore my questions.

> Do you understand the problems I mentioned above?

> Did you try to copy this function out of a class definition?

It is also unclear if anything I suggested actually led to a solution. You don’t show (or mention) which problems you’ve solved, how you’ve solved them, or what your code looked like afterwards. (It’s okay if my suggestion didn’t work; but I need to know – otherwise, I’m “in the dark” and it’s hard to know what to suggest next.) In fact, it’s difficult to tell if the code snippets you’ve posted are directly related, or if you’ve simply moved on to the next section you’re having problems with.

*****

> I’ve manged to loop through my DB and convert the values into latitude and longitude. Do you know how I can update each row in the loop with the new values?

Now, if I understand your current question correctly, you want to take the values from the DB, convert them, and then update the DB with the new values. Correct?

The best way would be to store the converted values in an array as you process them, then use them to write a new query when the loop is done. You can do multiple `UPDATE`s with a mutli-query, but I’d suggest using the `INSERT …ON DUPLICATE KEY UPDATE` syntax instead.

The basic structure would be like this:

# get values from DB
# make sure you get the col’s primary key also
$SQL_select = “SELECT pKey,value1,value2 FROM my_table WHERE whatever”;
if( ($result = mysql_query( $SQL_select )) !== false ){

# loop through result set
while( $row = mysql_fetch_assoc( $result ) ){

$pKey = $row;

# do your conversions
$newValue1 = conversion_function( $row );
$newValue2 = conversion_function( $row );

# write the pKey and new values as a set
# (for use in the INSERT query)
$newValues[] = “( ‘$pKey’,$newValue1,$newValue2 )”;
}
# next, write an INSERT query.
$SQL_insert = “INSERT INTO my_table( pKey,value1,value2 ) VALUES”;

# use implode() to concatenate each of the sets in $newValues
# with a comma
$SQL_insert .= implode( ‘,’,$newValues );

# finish up with the ON DUPLICATE command
$SQL_insert .= ” ON DUPLICATE KEY UPDATE
value1 = VALUES( value1 )
value2 = VALUES( value2 )”;

# run the query
if( mysql_query( $SQL_insert ) ){

print “success!”;

}else{

print “failure!”;

}

}else{

print “if you’re seeing this, the SELECT query failed.”;

}

*****
On yet another note, **you should not use the `mysql_*` functions**, *especially* if you’re learning. **ext/mysql is deprecated** and is **not recommended** for new code. It will be removed from PHP in the future. [ext/mysql**i**](http://php.net/mysqli) or [PDO](http://php.net/PDO) are recommended instead.

[More info about choosing](http://www.php.net/manual/en/mysqlinfo.api.choosing.php).