I am pretty new to PHP so please go easy on me.

I am receiving this error "SQLSTATE[HY093]: Invalid parameter..." when I am preparing my SQL statement and binding the values.

I have a function that takes three parameters: $table, $data, $id

Where $table is simply the table to update in the database.

$id is the id of the item within the table I am trying to update.

$data is an associative array where the $data[$key] matches the column name in the database.

public function update_database($table, $data, $id)
    {

        $sql_statement = "'UPDATE `" . $table . "` SET ";

        foreach ($data as $key => $value) {
            if ($key === array_key_last($data)) {
                $sql_statement .= $key . " = :" . $key . " WHERE id = :id '";
            } else {
                $sql_statement .= $key . " = :" . $key . ", ";
            }
        }

        $this->db->query($sql_statement);

        foreach ($data as $key => $value) {
            $this->db->bind(':' . $key, $data[$key]);
        }
        $this->db->bind(':id', $id);


        if($this->db->execute()){
            //it worked!
            return true;

        }else{
            //something did not work
            return false;
        }

I under stand that the error is simply telling me I am not binding the correct amount of values.

Within my SQL statement I am binding 17 values. 16 for each item in the associative array. and I tag on the 17th at the end of the statement. Here is the SQL statement generated with the first foreach loop:

string(594) "'UPDATE `clan-info-static` SET tag = :tag, name = :name, location_id = :location_id, location_name = :location_name, location_iscountry = :location_iscountry, location_countrycode = :location_countrycode, badgeUrls_small = :badgeUrls_small, badgeUrls_medium = :badgeUrls_medium, badgeUrls_large = :badgeUrls_large, requiredTrophies = :requiredTrophies, warFrequency = :warFrequency, isWarLogPublic = :isWarLogPublic, warLeague_id = :warLeague_id, warLeague_name = :warLeague_name, requiredVersusTrophies = :requiredVersusTrophies, requiredTownhallLevel = :requiredTownhallLevel WHERE id = :id '"

The second foreach loop is binding the values required in the SQL statement. It is using the same data set as the first foreach loop.

Ive modified the code to add a ticker to count each time it binds a value. The ticker counts to 16, which is to be expected. That makes 16 times Ive binded the value with the foreach loop, and the 17th time is done manually.

Since both foreach loops are using the same dataset, I am at a loss for why it is giving me this error.

EDIT: to include my database class.

class Database{
private $host = DB_HOST;
private $user = DB_USER;
private $pass = DB_PASS;
private $dbname = DB_NAME;

private $dbh;
private $stmt;
private $error;

public function __construct(){

    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
    $options = array(
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

    );

    try{
        $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);

    }catch(PDOException $e){
        $this->error = $e->getMessage();
        echo $this->error;


    }
}


public function query($sql){
    $this->stmt = $this->dbh->prepare($sql);
}

public function bind($param, $value, $type = null){
    if(is_null($type)){
        switch(true){
            case is_int($value):
                $type = PDO::PARAM_INT;
                break;
            case is_bool($value):
                $type = PDO::PARAM_BOOL;
                break;
            case is_null($value):
                $type = PDO::PARAM_NULL;
                break;
            default:
                $type = PDO::PARAM_STR;
                
        }

    }

    $this->stmt->bindValue($param, $value, $type);
}


public function execute(){
    return $this->stmt->execute();
}

}

CodePudding user response：

try{
    $update_str = "";
    foreach ($data as $key => $value){ 
        $update_str .= (strlen($update_str) ? ", ":" ").$key." = :".$key;
    }
    $data["pk"] = $id;
    $sql_statement = "'UPDATE `".$table."` SET ".$update_str." where id = :pk";
    $this->db->prepare($sql_statement)->execute($data);
    return true;
}
catch(\Exception $e){
    // die($e->getMessage()); // get error message
    return false;
}
    

Include this into the function, this is what i usually do in my project. Please sanitise the value input to prevent unhappy event. You may use the beginTransaction approach to rollBack if mistake happened.

CodePudding user response：

Okay, I got it working.

The problem was with the $this->db->query($sql_statement); For some reason it does not like when I simply pass a premade statement. When I build the statement like so: $this->db->query('UPDATE ' . $table . ' SET ' . $items . ' WHERE id = :id');. It works as expected.

I edited my first foreach loop to get items instead of building the entire statement:

OLD:

$sql_statement = "'UPDATE `" . $table . "` SET ";

    foreach ($data as $key => $value) {
        if ($key === array_key_last($data)) {
            $sql_statement .= $key . " = :" . $key . " WHERE id = :id '";
        } else {
            $sql_statement .= $key . " = :" . $key . ", ";
        }
    }

    $this->db->query($sql_statement);

NEW:

        $items = "";
        foreach ($data as $key => $value) {
            if ($key === array_key_last($data)) {
                $items .= $key . " = :" . $key;
            } else {
                $items .= $key . " = :" . $key . ", ";
            }
        }
        
        $this->db->query('UPDATE `' . $table . '` SET ' . $items . ' WHERE id = :id');

I did get it working, but I am not sure why the first way did not work. I will dive into the docs and try to find an answer. I will report back when I find out.