Syntax Error??

[shiggins]

This one has me stumped. I'm attempting to update some of the fields in a single row in a table and getting a syntax error.

//get the current values for forecasted sales
$get_cur=mysql_query("select * from salesForecastPinsDist where distid='$distid'");

if ($row=mysql_fetch_row($get_cur)) {

$curForecast=array_slice($row,1);//slices the distid from the row

}

//update current values with new forecast
for($i=0;$i<sizeof($pincount);$i++) {

$update=mysql_query("update salesForecastPinsDist set $curForecast[$i]='$pincount[$i]' where distid='$distid'");

		}

1064: You have an error in your SQL syntax near '0='0' where distid='139'' at line 1

Not all fields are to be updated but mysql recognizes that and won't update if the new values are the same as the original values.

Thanks in advance!

[van__]

$update=mysql_query("update salesForecastPinsDist set $curForecast[$i]='$pincount[$i]' where distid='$distid'");

1064: You have an error in your SQL syntax near '0='0' where distid='139'' at line 1

---

looks to me like $curForecast[$i] isn't providing a column name if you read the error message.
??'0='0'??

[shiggins]

Boy what was I thinking!!! Of course, I was telling it to update the VALUE not the column. Must have banged head this morning.

Let me ask you this, since there are 24 columns, rather than having "set col1=blah" and so on 24 times in the query, what's the best way to run the query, just assign an array?

[Anon]

I don't think so. But you can make it better viewable for future reference by splitting the query into many lines, e.g.:

$query = "UPDATE tbl SET ";
$query .= "col1 = '$var1', ";
$query .= "col2 = '$var2', ";
$query .= "col3 = '$var3', ";
$query .= "col4 = '$var4'";

This way it is much clearer for yourself and for the next programmer that needs to update your script.

Diego

[Anon]

To clean it up further you can say:

$query = "UPDATE tbl SET
col1='$var1',
col2='$var2',
col3='$var3',
col4='$var4'
WHERE
some_condition='met'";

But one drawback I find to this is syntax-highlighting editors that don't recognise multiline strings.