Question 41902 in PHP Question posted in
Official manual can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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;    
}
?>
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search