SQL problem? using the right code?

Databases
1

Guys I’m trying to fill in some fields in my database, what im trying to do is generate some txt with my function. then insert it into the mytxt column in my table for that tow. then move on to the next row and stick some new text that has been generated by the function,

instead for some reason it seems like that the text that is inserted on the last run appears in every other mytxt field. I know that the text is differant each time because i echo it out and it works.

Am i using the wrong SQL to do this?

$row = mysql_fetch_array($result);

while($row = mysql_fetch_assoc($result)){

$myspuntext = "";
echo "done 1<BR>";
$myspuntext = spin($text);
mysql_query("UPDATE locations SET mytxt = '".mysql_real_escape_string($myspuntext)."'",$connection) or die(mysql_error());
echo $myspuntext;
echo "<BR><BR>";

}

2

Try adding this function to check how many rows are affected:

int mysql_affected_rows ([ resource $link_identifier ] )

Description:
int mysql_affected_rows ([ resource $link_identifier ] )
Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query associated with link_identifier.

http://php.net/manual/en/function.mysql-affected-rows.php

.

3

Yeah but, in this query -

mysql_query(“UPDATE locations SET mytxt = '”.mysql_real_escape_string($myspuntext).“'”,$connection) or die(mysql_error());

You are updating the entire locations table, setting every single row of ‘mytxt’ to whatever the value of $myspuntext is.

If you want to make sure it just updates 1 row you can just use LIMIT 1 in the UPDATE query also, but that wont be accurate on the rows you need to update

The best way is just to use a WHERE clause

4

on every loop you are setting the ‘mytxt’ column for every single row in the table, so everytime the whole of the table will be set to whatever the variable $myspuntext currently is hense on the last loop it remains at that

you need a WHERE clause in your UPDATE query.

5

Does it not fetch one row at a time while in the loop?