SQL Error When Run In Site

Question

I have a function that generates and runs an mysql update query. When I run the generated code in phpMyAdmin I have no troubles, however when I try running it from my site I get an error. What is odd is I am using the same format for several other update queries and they are working flawlessly.

MySQL Query:

UPDATE `capc`.`bio_positions` SET `Pos_Name` = 'IT Specialist' WHERE `bio_positions`.`Pos_ID` = 63;UPDATE `capc`.`bio_positions` SET `Pos_Company` = 'CSG' WHERE `bio_positions`.`Pos_ID` = 63;

Error:

Could not get data: 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 'UPDATE `capc`.`bio_positions` SET `Pos_Company` = 'CSG' WHERE `bio_position' at line 1

Code Used To Generate MySQL Query

function update($rank, $name, $company) {
    if (!empty($rank) && $rank !== $this->Pos_Rank) {
        $sql = "UPDATE `capc`.`bio_positions` SET `Pos_Rank` = " . $rank . " WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    if (!empty($name) && $name !== $this->Pos_Name) {
        $sql .= "UPDATE `capc`.`bio_positions` SET `Pos_Name` = '" . $name . "' WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    if (!empty($company) && $company !== $this->Pos_Company) {
        $sql .= "UPDATE `capc`.`bio_positions` SET `Pos_Company` = '" . $company . "' WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";
    }
    echo "<br>" . $sql . "<br>";
    if (!empty($sql)) {
        $capc = new CAPC;
        $capc->query($sql);
        Bio_Position($this->Pos_ID);
    }
}

Updated Update Function From Answers That Works

function update($rank, $name, $company) {
    $capc = new CAPC;
    $sql = "UPDATE `capc`.`bio_positions` SET ";
    $go = 0;
    if (!empty($rank) && $rank !== $this->Pos_Rank) {
        $sql .= " `Pos_Rank` = " . $rank;
        $go++;
    }
    if (!empty($name) && $name !== $this->Pos_Name) {
        if($go > 0){
            $comma = ",";
        }
        $sql .= $comma . " `Pos_Name` = '" . $name . "'";
        $go++;
    }
    if (!empty($company) && $company !== $this->Pos_Company) {
        if($go > 0){
            $comma = ", ";
        }
        $sql .= $comma . " `Pos_Company` = '" . $company . "'";
        $go++;
    }
    $sql .= " WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID . ";";

    if (!empty($sql) && $go > 0) {
        //echo $sql . "<br>";
        $capc = new CAPC;
        $capc->query($sql);
    }
}

Answer 1

Your WHERE has the same condition, so why are you dividing this to three queries? You can do:

UPDATE `capc`.`bio_positions` SET `Pos_Rank` = " . $rank . ", `Pos_Name=` = " . $name . ", Pos_Company=" . $company . " WHERE `bio_positions`.`Pos_ID` = " . $this->Pos_ID;

EDIT: (according to comments)

function update($rank, $name, $company) {
       $new_rank = $this->Pos_Rank;
       $new_name = $this->Pos_Name;
       $new_company = $this->Pos_Company;

        if (!empty($rank)) {
            $new_rank = $rank;
        }
        if (!empty($name)) {
                $new_name = $name;
        }
        if (!empty($company)) {
                $new_company = $company;
        }

        $q = ...

}

Answer 2

Running multiple queries in one "batch" is disallowed by at least some of the MySQL APIs, but PHPMyAdmin allows it.

Seeing that the query updates the same set of rows, you can rewrite it as a single query though, this will speed up the execution since the WHERE condition only has to be evaluated once;

UPDATE `capc`.`bio_positions` 
SET `Pos_Name` = 'IT Specialist', `Pos_Company` = 'CSG' 
WHERE `bio_positions`.`Pos_ID` = 63

Answer 3

When I run the generated code in phpMyAdmin I have no troubles

UPDATE capc.bio_positions SET Pos_Name = 'IT Specialist' WHERE bio_positions.Pos_ID = 63;

UPDATE capc.bio_positions SET Pos_Company = 'CSG' WHERE bio_positions.Pos_ID = 63;

Although phpMyAdmin appears to execute multiple queries at one time (it's actually splitting them up for you on the backend), in your code you'll need to execute them singly or rewrite the SQL to update more than one column in a single statement, i.e., UPDATE capc.bio_positions SET Pos_Name = 'IT Specialist', Pos_Company = 'CSG' WHERE bio_positions.Pos_ID = 63.

If you're using mysqli you can also check out mysqli::multi_query.

Answer 4

Try this    

UPDATE `capc`.`bio_positions` 
    SET `Pos_Name` = 'IT Specialist' 
    WHERE `Pos_ID` = 63;