Trying to do math across columns for each row... not working

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nou
Forum Newbie
Posts: 8
Joined: Tue Oct 25, 2011 3:21 am

Trying to do math across columns for each row... not working

Post by nou »

I can't figure the answer to this.

I have a table called "zip_codes" with columns ZIP, STATE, LATITUDE, LONGITUDE, CITY, FULL_STATE, and POINT.

I'm trying to write a script to automatically take the latitude and longitude of each row, divide them, and put the result into column POINT(BIGINT type).

This is the code I currently have:

Code: Select all

$result = mysql_query("SELECT * FROM zip_codes");
while ($row = mysql_fetch_array($result)) { //set variables for rows
	$lat = $row['latitude'];
	$lon = $row['longitude'];
	}

$sql = "UPDATE zip_codes SET point = ($lat / $lon)";
$result = mysql_query($sql);
When I run the page, my POINT column becomes populated with the same exact number for each row. How could I do this math operation for each row without doing it manually? (there's 42000 rows!) Thanks
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Trying to do math across columns for each row... not wor

Post by Celauran »

Your UPDATE query is outside your while loop, so it's only being run once and with the last set of data. Moreover, you have no WHERE clause to tell it which record to update, so it's happily updating all of them.

Code: Select all

// Never SELECT *
$sql = "SELECT zip, latitude, longitude FROM zip_codes";
// You shouldn't use mysql_ functions either, but one thing at a time...
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
    $sql = "UPDATE zip_codes SET point = ({$row['latitude']} / {$row['longitude']}) WHERE zip = {$row['zip']}";
    @mysql_query($sql);
}
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Trying to do math across columns for each row... not wor

Post by mikosiko »

why do you need to SELECT first to do just an UPDATE? ... I don't follow


In the more simplest way you just need

Code: Select all

$sql = "UPDATE zip_codes SET point = latitude / longitude";
$result = mysql_query($sql);  // need to add error control here for better coding
// need to add control of affected_rows here if needed (... you must remember that affected_rows is no necessarily accurate)
no SELECT, no additional queries in nasty loops
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Trying to do math across columns for each row... not wor

Post by pickle »

^ what he said
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply