Simple MySQL UPDATE problem, cant figure out what is the problem.

Hi All,
I have a simple problem i am reading a value from col4 of evry row and doing some manipulation and updating it in the col8 of the same table same row. But the problem is it is updating the last row's col4 value in all the rows clo8.
but it is printing properly.
here is the code.

$query_read = "SELECT * FROM table_content";
$result_read = mysql_query($query_read);

while ($row = mysql_fetch_array($result_read, MYSQL_NUM))
{
$pubdate = $row[4];
$pub_date =$pubdate +1;
echo "Date is $pub_date";
$query = "UPDATE table_content SET col8= '$pub_date'";
$result = mysql_query($query);
continue;
}

waiting for reply....

Thanx in advance.

 

You need to be updating based on a primary or other unique key. Otherwise you will update every row in the table.

Assuming that your primary key is called something like `id`:

$query_read = "SELECT * FROM table_content";
$result_read = mysql_query($query_read);

while ($row = mysql_fetch_array($result_read, MYSQL_NUM))
{
$rowId = $row[0];
$pubdate = $row[4];
$pub_date =$pubdate +1;
$query = "UPDATE table_content SET col8= '$pub_date' WHERE id = '$rowId'";
$result = mysql_query($query);
echo "Date is $pub_date";
continue;
}

Code (markup):

 

It should update every row col8 with manipulated col4 value.
how can i do that?

 

Your original script should do that. But, I'm a little unclear about what you're trying to accomplish. If you update every col8 with the manipulated col4 value, on each loop, then you are essentially writing over all the data you just updated in the previous loop. What you will end up with is whatever is in the last row of the table.

Are you trying to deal with just a single record or are you meaning to rewrite all of the data each time you loop?

 

It sounds like you want to update col8 with the corresponding col4+1. In that case, you need to do what jestep suggested....

Add:

WHERE id = '$rowId'"

 

I want to update All the data each time i loop.

 

Why loop if you are trying to update all of the data?

Sorry, I don't understand what you are trying to accomplish. Maybe you could provide a working example with data, and explain how you want the results to work?

 

$query_read = "SELECT * FROM table_content";
$result_read = mysql_query($query_read);

while ($row = mysql_fetch_array($result_read, MYSQL_NUM)) {
echo "Date is " . ($row[4]+1);
}

$query = "UPDATE table_content SET col8=col4+1";
$result = mysql_query($query);

PHP: