select and update one table simultaneously...

richie

What I'm trying to do is simple, yet for the life of me, I can't get it to work.

Basically:
1. I want to select data from a table, loop through the data with mysql_fetch_array, and use it to do some date calculation.

Based on the result of the calculation, I want another column updated within the same table.

In other words, for each iteration of the loop, I'm carrying out another db query to update the same table. This query is not updating the table.

Note: $connection is correct in the actual php script.

Here is the code, and any help will be greatly appreciated.

$db = "mydb";
$query = "select lastEdit,id from agents ";
$result = @mysql_db_query($db,$query,$connection);

while ($myrow = mysql_fetch_array($result))
{

$id = $myrow["id"];
$lastEdit = strtotime ($myrow["lastEdit"]); // convert to seconds

$CURRENT_DATE = strtotime ("now"); // current time in seconds

$db = "mydb";

$sql = "update agents set ";

if(($CURRENT_DATE - $lastEdit)>86400) // 1 day
{
$sql .= "oneDay *=0 ";
}

$sql .= "where id='$id' limit 1";

$updtResult = mysql_db_query($db,$sql,$connection);

}

jeremuck

Try outputting the query using echo or print and make sure the queries are right. Remove the @ at the first query to make sure you see the errors if there are any (you can put it back once you know it is working).

After each query try:

echo "Query : " . $query . "<br>";

richie

jeremuck, thanks.

The queries echoed are:

select lastEdit, id from agents

update agents set oneDay = '0' where id='30'

The problem must lie with the second query, since $id is showing correctly from the first query.

All the queries are right are right, yet the table is not updating.

nnichols

Surely you could accomplish the same thing with a single query -

UPDATE agents
SET oneDay=0
WHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(lastEdit))>86400

richie

Thanks nnichols.

Good to know your suggestion is possible.

The code I posted here is a highly simplified version of the actual thing. I'm forced to use my earlier method, because of the iteration involved. Luckily, I found the error, and it now works perfectly.

nnichols

Even with a more complex set of conditions you should still be able to fit all the logic into your sql -

UPDATE tbl SET col1=IF(condition, new_val, col1)

Using IF you can achieve very complex logic statements. You can even nest the IF statements -

UPDATE tbl SET col1=IF(condition, new_val, IF(another_condition, new_val, col1))

This may not apply to your current situation but it is all useful stuff to know.