通过使用 Common Function 在 PHP 中准备绑定参数来更新 SQL 表-解网

问：

我致力于在PHP中制作一个通用函数来更新表，因为我有很多表单更新MySQL表。它工作正常，它更新了我的表格：下面是我的代码和一些注释：

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

如何把$params数组，外侧功能？所以我可以传递有关提交表单的不同参数？

php mysqli 预备语句

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

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

现在您可以按如下方式调用上述函数 -

 $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

注意：我认为使用PDO比mysqli更好

通过使用 Common Function 在 PHP 中准备绑定参数来更新 SQL 表

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

评论

评论

评论