CODEWITHSUNDEEP
phpmysqlnested
zztop
zztop

Reputation: 791

PHP/MYSQL:Carry out UPDATE within SELECT query

There are many questions on SO about this but I cannot find one that quite meets my situation.

I want to use the values in some fields/columns of a table to set the value of a third field/column

In other words something like:

table races
athleteid|difficulty|score|adjustedscore

$sqlSelect = "SELECT athleteid,difficulty,score FROM races";
$res = mysql_query($sqlSelect) or die(mysql_error());
while ($row = mysql_fetch_array($res)){

$adjustedscore=difficulty*score;

$sqlupdate = "UPDATE race, set adjustedscore = '$adjustedscore' WHERE athletes = 'athletes'";
$resupdate = mysql_query($sqlupdate);

}

My understanding, however, is that MYSQL does not support update queries nested in select ones.

Note, I have simplified this slightly. I am actually calculating the score based on a lot of other variables as well--and may join some tables to get other inputs--but this is the basic principal.

Thanks for any suggestions

Upvotes: 1

Views: 75

Answers (2)

Grynets
Grynets

Reputation: 2525

First of all, as previous commentators said, you should use PDO instead of mysql_* queries. Read about PDO here.
When you'll get data from DB with your SELECT query, you'll get array. I recommend you to use fetchAll() from PDO documentation.
So, your goal is to save this data in some variable. Like you did with $row.
After that you'll need to loop over each array and get your data:
foreach($row as $r) { //We do this to access each of ours athlete data $adjustedscore= $row[$r]["difficulty"]* $row[$r]["score"]; //Next row is not clear for me... $query = "UPDATE race SET adjustedscore = '$adjustedscore' WHERE athletes = 'athletes'";
And to update we use PDO update prepared statement
$stmt = $dbh->prepare($query); $stmt->execute(); }

Upvotes: 0

Jirka Hrazdil
Jirka Hrazdil

Reputation: 4021

You can run:

UPDATE `races`
SET `adjustedscore` = `difficulty` * `score`
WHERE `athleteid` IN (1, 2, 3, ...)

Upvotes: 2

Related Questions