在MySQL中使用多个UPDATE

Question

I'm trying to "force" this code to run through every line and update every line that matches the statement above..

if(isset($itemID1) && isset($itemID2) && isset($itemID3) && isset($itemID4) && isset($itemID5) && isset($itemID6) && isset($itemID7) && isset($itemID8) && isset($itemID9) && isset($itemID10)){    
        $upd = "UPDATE booking SET status='$status/ADM', verification='No'";
        if($CalDate !=''){
            $upd.= ",CalDate='$CalDate'";
        }
        if($DueDate !=''){
            $upd.= ",DueDate='$DueDate'";
        }
        if($groupid1 != 0){
            $upd.= " WHERE groupid IN ('$groupid1')";
        }
        if($groupid2 != 0){
            $upd.= " WHERE groupid IN ('$groupid2')";
        }
        if($groupid1 == 0){
            $upd.= " WHERE itemid IN ('$itemID1')";
        }
        if($groupid2 == 0){
            $upd.= " WHERE itemid IN ('$itemID2')";
        }
    }

Known errors: Error could not update data reason: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE itemid IN ('143')' at line 1

Either that or the script will only update the first line that it passes by. As for groupid1 != 0

Answer 1

The error is likely to be where you build your WHERE clauses.

It is probably due to the fact that you are adding multiple WHERE clauses into your statement. Your SQL statement can only contain one WHERE clause.

For example, if groupid1 and groupid2 are not 0 then you will get this statement:

SELECT ... WHERE groupid IN ('$groupid1') WHERE groupID IN ('$groupid2')

This is obviously invalid. You need to instead add a single WHERE clause and then add parameters using AND or OR to separate them as appropriate:

SELECT ... WHERE groupid IN ('$groupid1') OR groupID IN ('$groupid2')