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?

CodePudding user response：

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;    
}
?>

CodePudding user response：

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)));
    $databaseName = "developnow";
    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 => $key) {
           $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