Using foreach to update fields in a mysql databse based on another query

Question

Ok I really should be be clued up on this now but its a bit late here in england and I was wondering if someone could point out the obvious to me.

I have to do a first query which is SELECT * from table WHERE NOT column=0, and use the results from that to do a foreach loop something like below.

foreach($results as $result) { 
$nox = $result[field_x]; 
//Use nox for whatever; 
//Then update some fields in SQL; }

And keep doing that until all the items in the first query are done,

What would the syntax be as Im too tired for a long winded tried and tested routine i do,

thanks everyone for any help

Answer 1

You'd just use a simple update command:

update tbl set field = '$value' where id = $id

In PHP-ese:

$sql = 'update tbl set field = \'' .
        mysql_real_escape_string($value) . '\' where id = ' . $id;
mysql_query($sql);

Answer 2

.

$sql = "SELECT * FROM `myTable` WHERE `column` = 0";

// query the database
$resource = mysql_query($sql);

// loop through the results
while ($result = mysql_fetch_assoc($resource)) {
    // grab the value
    $nox = $result['field_x'];   // you should use quotes here, btw

    // make your modifications;
    ++$nox; // or whatever

    // build the query
    $sql = sprintf(
        "UPDATE `myTable` SET `field_x` = %d WHERE `id` = %d"
        , $nox
        , $result['id']
    );

    // run the query
    mysql_query($sql);
}

Answer 3

Unless you have some extensive processing done in PHP you can most likely do that in a single, multi-table UPDATE.

For example:

UPDATE table1 t1
  JOIN table2 t2 ON t2.id = t1.id
   SET t1.col = t2.col + 1
 WHERE t1.somecol <> 0