php function to mysqli update

Question

Trying to create a function that would be used to update any row of any table, but I'm getting trouble getting into it.

Data sent in array where the array index is the field name in table and the value is the new value for that index.

For examplpe:

$args["name"] = "NewName";
$args["city"] = "NewCity";
$args["id"] = 4; // row ID to update

What I got:

function create_update_query($table, $keys){
    $keys = array_map('escape_mysql_identifier', $keys);
    $table = escape_mysql_identifier($table);
    $updates = "";
    $count = 0;
    foreach($keys as $index => $value){
        if($index != "id"){
            $count++;
            if($count == count($keys)-1){
                $updates = $updates . "$index = ?";
            }else{
                $updates = $updates . "$index = ?,";
            }
        }
    }    
    return "UPDATE $table SET $updates WHERE id = ? LIMIT 1";
}

After that, I have the function to really do the query:

function crud_update($conn, $table, $data){
    $sql = create_update_query($table, array_keys($data));
    if(prepared_query($conn, $sql, array_values($data))){
        $errors [] = "OK";
    }else{
        $errors [] = "Something weird happened...";
    }
}

The function that makes the prepared statement itself:

function prepared_query($mysqli, $sql, $params, $types = ""){
    $types = $types ?: str_repeat("s", count($params));
    if($stmt = $mysqli->prepare($sql)) { 
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return $stmt;
    } else {
        $error = $mysqli->errno . ' ' . $mysqli->error;
        echo "<br/>".$error; 
    }
}

When trying to submit the data with the following criteria:

$args['name'] = "Novo Nome";
$args['field'] = "New Field";
$args['numaro'] = 10101010;
$args['id'] = 4;

//create_update_query("teste_table", $args);
crud_update($link, "teste_table", $args);

Have an error:

1064 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 '1 = ?,2 = ?,3 = ? WHERE id = ? LIMIT 1' at line 1

But if I echo the query created by create_update_query it seems ok:

UPDATE `teste_table` SET name = ?,field = ?,numaro = ? WHERE id = ? LIMIT 1

Any help would be appreciated. Thanks.

Nigel Ren · Accepted Answer · 2019-09-10 16:48:13Z

1

The problem is that as you pass the keys to create_update_query() as

create_update_query($table, array_keys($data));

Using array_keys() will just take the key names, so the $keys parameter is just a list of the field names as something like ...

Array(
 0=> 'name',
 1 =>'field',
 2 =>'numaro'
)

You then extract the data using

foreach($keys as $index => $value){

and build your SQL with

$updates = $updates . "$index = ?";

so at this point, the indexes are the numeric value, so change these lines to...

$updates = $updates . "$value = ?";

which is the name of the field.

With the various other changes, I would suggest the code should be...

foreach($keys as $value){
    if($value != "id"){
        $updates = $updates . "$index = ?,";
    }
}    
$updates = rtrim($updates, ",");
return "UPDATE $table SET $updates WHERE id = ? LIMIT 1";

edited Sep 10, 2019 at 16:48

answered Sep 10, 2019 at 16:21

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Tiago Over a year ago

But to create the query I want the key names as name, field and numaro. It seems when I use the data in the second function it translates to index. The change you provided doesnt work. Don't give any error but doesn't work.

Tiago Over a year ago

No, it doesnt work. Doesn't show any error, but doesn't work.

Nigel Ren Over a year ago

Have you check the SQL that it is generating?

Tiago Over a year ago

It's posted in the initial question I guess.

Nigel Ren Over a year ago

So the output using this code is posted in the original question?

|

Collectives™ on Stack Overflow

php function to mysqli update

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related