Update SQL table by prepare binding parameters in PHP using a Common Function

Question

I work on making a common function in PHP to update table because I have a lot of forms update MySQL tables. It is working fine and it update my table: below is my code with some comments:

<?php
include('../config.php');
if (isset($_POST['loginfo'])) {
    $table = "users";
    $creteria = "id =?";
    if (update_table($table,$creteria)){
        echo "<h1> Successfully Updated Table: ". $table. "</h1>";
    }   
}           
        
function update_table($tablename,$creteria) {
    $conn = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
    $sql = "UPDATE ".$tablename. " SET ";                                                   
    $postdata = $_POST;
    $count = count($postdata);  
    $nonempty = count(array_filter($postdata, function($x) { return ($x !== ""); }));
    $i = 0;                                                                                 
    $vartype = "";
    foreach ($postdata as $key => $value) { 
        $i++;
        if (!empty($value)) {
            $nonempty--;
            $sql .= " $key = ? ";
            if ($nonempty >0) {
                $sql .= " ,";
            }           
            if(is_int($value)){
                $vartype .= "i";
            } else {
                $vartype .= "s";
            }
        }            
    }   
    $sql .= "WHERE ".$creteria;
    $vartype .= "i";
    $stmt = $conn->prepare($sql);
    $params = array(&$fullname, &$email, &$phone, &$id);// this line must be out side function
    call_user_func_array(array($stmt, "bind_param"), array_merge(array($vartype), $params));    
        $fullname = $_POST['fullname']; // fullname,email,phone, id must be out of function
        $email = $_POST['email'];
        $phone = $_POST['phone'];
        $id = $_POST['id'];
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return true;    
}
?>

How to put $params array, out side function? So I can pass different parameters regarding submitted form?

You can add another parameter in `update_table($tablename,$creteria,$params)` What's wrong with that? — Dharman, Nov 01 '22 at 10:15
@Dharman actually, there is so much wrong. $params is just a tip of the iceberg. Variables below are hardcoded, making the whole struggle in vain. — Your Common Sense, Nov 02 '22 at 07:44

score 0 · Accepted Answer · edited Nov 02 '22 at 07:45

0

I rewrite your function,and is working fine for me, you can check this. In your code criteria is still SQLIA vulnerable. Thus, following is the equivalent code update any table with condition as follows-

function update_table(string $tablename,array $criteria, array $updateData) {
    $params = array_merge(array_values($updateData), array_values(reset($criteria)));
    try {
        $conn = new PDO(sprintf('mysql:dbname=%s;host=%s', DB_DATABASE, DB_HOSTNAME), 
                            DB_USERNAME, 
                            DB_PASSWORD);
        $updateLastKey = array_key_last($updateData);        
        $sql = sprintf("UPDATE %s SET ", $tablename);
        
        foreach($updateData as $key => $item) {
           $sql = sprintf("%s %s = ?%s ", $sql, $key,$key == $updateLastKey? "" : "," );
        }
        
        $sql = sprintf("%s WHERE %s", $sql, key($criteria));
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $conn = null;
    } catch (PDOException $e){
        die($e->getMessage());
    }

    return true;    
}

Now you can call above function as follows-

 $updateData = ["name"=>$_POST['name'], "price"=> $_POST['price']];
 $criteria = ["category_id = ? and price>=?  " => [1, 500]];
 update_table('products',$criteria,$updateData);
// this will update all the products whose category id is 1 and price is greater than 500

Note : I think using PDO is better than mysqli

edited Nov 02 '22 at 07:45

Your Common Sense

156,878
40
214
345

answered Nov 01 '22 at 06:55

Veshraj Joshi

3,544
3
27
45

`foreach($updateData as $key => $key)` What is this? – Dharman Nov 01 '22 at 10:14
1

PDO is indeed better but this function is still wrong on many levels. there must be no new PDO, no die($e->getMessage()), no $databaseName – Your Common Sense Nov 02 '22 at 07:32
3

What is more important, thus function is rather useless. the same can be done with less code being **100 times easier to read**, `prepared_query($db, "UPDATE products SET name=?, price=? WHERE category_id = ? and price>=?", [$_POST['name'],$_POST['price'],1,500]);` where prepared_query being 10 times shorter and more universal. – Your Common Sense Nov 02 '22 at 08:10
@YourCommonSense agree with you. This is in case somebody donot want to see the raw query most of the time. In your way code is also more efficient because there is no further processing regarding the mapping. – Veshraj Joshi Nov 02 '22 at 08:24
1

@VeshrajJoshi, your code is work with me for all forms and tables. as I'm newer in PHP I don't know why some developers tell you your way is not good. but can you do fever to modify function as per their suggestion? – musab Nov 04 '22 at 10:53

score -1 · Answer 2 · answered Nov 01 '22 at 04:42

You can use params variable as global. And you can merge or directly to equal them with interval variable, inside function.For example:

$paramsGlobal = ['name'=> 'Tural Rza'];

function update_table($tablename,$creteria) {
    global $paramsGlobal;
    $conn = new mysqli(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
    if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
    }
    $sql = "UPDATE ".$tablename. " SET ";                                                   
    $postdata = $_POST;
    $count = count($postdata);  
    $nonempty = count(array_filter($postdata, function($x) { return ($x !== ""); }));
    $i = 0;                                                                                 
    $vartype = "";
    foreach ($postdata as $key => $value) { 
        $i++;
        if (!empty($value)) {
            $nonempty--;
            $sql .= " $key = ? ";
            if ($nonempty >0) {
                $sql .= " ,";
            }           
            if(is_int($value)){
                $vartype .= "i";
            } else {
                $vartype .= "s";
            }
        }            
    }   
    $sql .= "WHERE ".$creteria;
    $vartype .= "i";
    $stmt = $conn->prepare($sql);
    $params = array_merge(array(&$fullname, &$email, &$phone, &$id),$paramsGlobal);// this line must be out side function
    call_user_func_array(array($stmt, "bind_param"), array_merge(array($vartype), $params));    
        $fullname = $_POST['fullname']; // fullname,email,phone, id must be out of function
        $email = $_POST['email'];
        $phone = $_POST['phone'];
        $id = $_POST['id'];
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return true;    
}
?>

You shouldn't use global like this https://stackoverflow.com/a/12446305/285587 — Your Common Sense, Nov 02 '22 at 07:12

Update SQL table by prepare binding parameters in PHP using a Common Function

2 Answers2