更新mysqlDB的函数在foreach循环中不起作用

Question

I two tables (authors and books) linked with the table "book authors".

The "book authors" table contains BookAuthorID, AuthorID and BookID, meaning I can have multiple books from the same author etc, and matching them is a doddle.

Unfortunately I messed up the ID field for the authors table and have created a new one that is primary and autoincrements. Now I need to populate a matching column in the "book authors" table with the new ids, matching them with the old ones.

I have the following function to update the "book authors" table...

function update_id($BookAuthorID, $NewAuthorID) {
$result = mysql_query("UPDATE `book authors` SET NewAuthorID='$NewAuthorID' WHERE BookAuthorID='$BookAuthorID'") or trigger_error(mysql_error()); }

I know this function works as I have tested it by creating a blank page with the function and two example variables ($BookAuthorID, $NewAuthorID) and it updated the table correctly.

Now I have the following code which finds every instance of each author, and then a foreach loop on each book matches the ids...

$result = mysql_query("SELECT * FROM authors");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $AuthorID= $row["AuthorID"];

    $match = get_books($BookID);

    foreach ($match as $value) {    
        update_id($value["BookAuthorID"], $value["NewAuthorID"]);
    } 
}

the get_books function is as follows and works for the rest of the site...

function get_books($id) {
    $result = mysql_query("SELECT * FROM `book authors` WHERE AuthorID= $id") or trigger_error(mysql_error());
    $array = array();

    while($row_ids = mysql_fetch_assoc($result)){
        $array[] = $row_ids;
    }
return $array; 
}

I cant figure out why the function works outside the foreach loop, but does nothing inside (and no errors are generated).

Answer 1

Couldn't you do something like:

UPDATE `book authors`, authors 
SET `book authors`.newauthorid = authors.newauthorid 
WHERE `book authors`.authorid = authors.authorid;

instead of looping?