CODEWITHSUNDEEP
phpmysqlpdo
Dorino Canciani
Dorino Canciani

Reputation: 87

Php Update using PDO

I'm trying to build a custom class to manage operations with database. I'm a beginner with OOP so if you have any suggests please tell me. By the way, i have an update method which took as parameter the name of the table, the fields to update, the values with which i want to update the fields and the fields and values to put in where clause of query. At this time, i have two distinct arrays, one for the set part and one for the where part. I build the query string like so PDOStatement Object ( [queryString] => UPDATE Ordini SET Nome=':Nome', Cognome=': Cognome', Telefono=': Telefono' WHERE ID=':ID' )

Now i would like to bind the params to the variables and execute the query and that's the problem. I try this way but the query don't update the fields. - In $values i have the values i want to bind to variables in the SET part - In $wv i have the values i want to bind to variables in the WHERE part - In $fieldsQuery i have the placeholders for the SET part(":Nome" for example) - In $fieldsWhere i have the placeholders for the WHERE part

How can i bind in the right way the placeholders with the variables?

public function update($table=NULL, $fieldsQuery=NULL, $fieldsValues = NULL, $whereFields=NULL, $whereValues=NULL, $whereOperators = NULL)
{
    if($fieldsQuery != NULL)
        $fields = explode(",", $fieldsQuery);
    if($fieldsValues != NULL)
        $values = explode(",", $fieldsValues);
    if($whereFields != NULL)
        $wf = explode(",", $whereFields);
    if($whereValues != NULL)
        $wv = explode(",", $whereValues);

    $fieldsQuery = array_map(function($field) { return ":$field";}, $fields);
    $bindValuesSet = array_combine($fieldsQuery, $values);
    //return an array in which every field is => Fieldname=':Fieldname' 
    $bindSetInitial = array_combine($fields, $fieldsQuery);


    //transform every item in array from field to :field
    $fieldsWhere = array_map(function($field) { return ":$field";}, $wf);
    $bindValuesWhere = array_combine($fieldsWhere, $wv);
    $bindWhereInitial = array_combine($wf, $fieldsWhere);

    //implode an array mantaining both key and value
    $fieldsValues = implode(', ', array_map(function ($v, $k) { return sprintf("%s='%s'", $k, $v); }, $bindSetInitial, array_keys($bindSetInitial)));
    $fieldsWhere = implode(', ', array_map(function ($v, $k) { return sprintf("%s='%s'", $k, $v); }, $bindWhereInitial, array_keys($bindWhereInitial)));

    $query = $this->db->prepare('UPDATE ' . $table . ' SET ' . $fieldsValues . ' WHERE ' . $fieldsWhere);

    $query->bindParam(':Nome', $values[0]);
    $query->bindParam(':Cognome', $values[1]);
    $query->bindParam(':Telefono', $values[2]);
    $query->bindParam(':ID', $wv[0], PDO::PARAM_INT);

    $query->execute();
    print_r($query->debugDumpParams());
}

Upvotes: 0

Views: 160

Answers (1)

u_mulder
u_mulder

Reputation: 54831

':Nome' is not a placeholder for a prepared statement. It's just a string ':Nome'

Placeholder is :Nome (without `) and without any spaces, tabs etc. I.e. : Nome is not a placeholder too.

So, you query should be:

UPDATE Ordini SET Nome=:Nome, Cognome=:Cognome, Telefono=:Telefono WHERE ID=:ID

And thanks to @Fred-ii- - read error handling section of PDO

Upvotes: 1

Related Questions