使用SELECT查询在MySQL上查找UPDATE查询

I'm using a SELECT query to obtain a variable using mysql_fetch_assoc. This then puts the variable into an UPDATE variable to put the returned value back into the database.

If I hard code the value, or use a traditional variable and it goes in just fine, but it doesn't work when using a value previously retrieved from the database. I've tried resetting the array variable to my own text and that works.

$arrgateRetrivalQuery = mysql_query(**Select Query**);
    $arrGate = mysql_fetch_assoc($arrgateRetrivalQuery);
    $arrivalGateTest = $arrGate['gatetype'];
$setGateAirportSQL = "UPDATE pilots SET currentgate = '".$arrivalGateTest."' WHERE pilotid = '".$pilotid."'";
    $setGateAirportQuery = mysql_query($setGateAirportSQL);

    // Close MySQL Connection
    mysql_close($link);

This will just make the field to update have nothing in it, however whenever I remove the variable from the SELECT to one I define, array or not, it will work.

Hope this is clear enough. Thanks in advance.

Is arrivalGateTest a number or a string? How did you try to put another value in the query? If you are sure the previous query returns a value, try to write: $setGateAirportSQL = "UPDATE pilots SET currentgate = '$arrivalGateTest' WHERE pilotid = '$pilotid'";.

Just change your sql to inlcude a subquery.

You could use the following general syntax:

UPDATE pilots SET currentgate = (SELECT gate FROM airport WHERE flight='NZ1') WHERE pilotid='2';

which is demonstrated on this fiddle

This saves the extra query and more accurately describes what you are trying to achieve.

WARNING - test it carefully first!