MySQLi INSERT bind_param问题 [英] MySQLi INSERT bind_param problem

问题描述

我在这里遇到了一个问题，我无法让脚本重复我已成功完成其他地方的事情。



在我的注册表单，在通过所有验证和检查之后，我正在尝试将信息插入数据库以注册用户......但这是我第一次使用MySQLi，特别是准备和bind_params所以我很生疏到目前为止......由于某种原因，似乎无法第一次正确使用它。



无论如何，我在MySQLi数据库访问包装器中有一个功能使用传递的参数来创建INSERT SQL并执行它...实际插入它。我将此函数用于所有情况（这意味着绑定参数会不时地不同，并且函数需要接受动态数量的参数）。这是我的插入函数。

Hi, I've got a problem here where I can't get the script to repeat something that I have successfully accomplished some place else.

At my register form, after all the validation and checks have being passed, I'm trying to insert the information into the database to register the user... but this is my first time using MySQLi, especially prepare and bind_params so I'm very rusty on this so far... can't seem to get it right the first time for some reason.

Anyway, I have a function in my MySQLi database access wrapper that uses the passed arguments to create the INSERT SQL and execute it... actually insert it. I have this function be used for all of the situations (which means the bind parameters would be different from time to time and the function needs to accept a dynamic amount of parameters). Here is my insert function.

public function insert($tbl_name, $fields, $values, $types, $vars)
{
    // Initiating our insert SQL
    $sql = "INSERT INTO `$tbl_name`";
    
    // Generating the fields
    $sql .= " (`" . implode("`, `", $fields) . "`)";
    
    // Generating the values of the SQL
    $sql .= " VALUES ('" . implode("', '", $values) . "')";
    
    // Initiating the mysqli statement
    $stmt = $this->mysqli_link->stmt_init();
    
    // Setting the last sql used
    $this->last_sql = $sql;
    
    // Preparing the statement
    $stmt->prepare($sql) or die($stmt->error);
    
    // The variable array ([0] => $types, [1+] $vars)
    $array = array(array($types), $vars);
            
    // Merging all of the arrays into one array
    $array = call_user_func_array('array_merge', $array);
    
    // The reference array being created
    $refArray = $this->make_ref($array);
    
    // Initiating the mysqli_stmt VIA ReflectionClass
    $ref = new ReflectionClass('mysqli_stmt');
    
    // Getting the bind_param function
    $method = $ref->getMethod('bind_param');
    
    // Invoking the function (actually binding here)
    $method->invokeArgs($stmt, $refArray);
    
    // Checking against errors in the execution of the SQL
    if(!$stmt->execute())
    {
        // There was an error
        throw die($stmt->error);
    }
    
    // Returning true on success
    return true;
}

以下是我如何使用

Here is how I use it

// The values to be added to the database
$values = array('null', // user id
                'null', // user session id
                '1',    // user permission id
                '1',    // user group id
                '?',    // [s] (username)
                '?',    // [s] (user password)
                'null', // Avatar
                '?',    // [s] (user email)
                '?',    // [i] (Date of birth)
                time()  // Date of register
                );

// The values to be bound (mostly because they are user submitted)
$vars = array($vars['form']['username'],
              $pass,
              $vars['form']['email'],
              $dob
             );

// No errors occurred... we may go ahead and insert the user into the database
$stmt = $db->insert(USER_TABLE, $user_table_schema, $values, 'sssi', $vars);

我收到以下错误！

And I'm getting the following error!

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\www\cms\includes\mmysqli.php on line 406

$ refArray显示为：

$refArray shows as:

Array
(
  [0] => sssi
  [1] => userName
  [2] => passWord
  [3] => [e-mail]
  [4] => 651189600
)

问题是，我正在为其他地方的bind_params做这个确切的方法它在那里运作良好。以下是我正在使用此功能的函数：

The thing is, I'm doing this exact method to bind_params elsewhere and it works well there. Here is the function that I'm using this thing at:

public function build_query($sql)
{
    // Checking if $sql was an array
    if(!is_array($sql))
    {
        if($this->silent === false)
        {
            trigger_error('The variable $sql is not an array ', E_USER_ERROR);
        }
        return false;
    }
    
    // Setting a default where clause
    $where = array(null,null,array(),null);
    
    // Checking the WHERE clause
    if(isset($sql[2]) && is_array($sql[2]))
    {
        // Getting our WHERE clause generated
        $where = $this->build_where($sql[2]);
    }
    
    // Checking the ORDER BY clause
    $orderby = ((isset($sql[3])) ? ' ORDER BY ' . trim($sql[3]) : null);
    
    // Checking the LIMIT clause
    $limit = ((isset($sql[4])) ? ' LIMIT ' . trim($sql[4]) : null);
    
    // The generated SQL ready to be prepared and bound (if need be for either)
    $sql = "SELECT {$sql[0]} FROM `{$sql[1]}`{$where[0]}{$orderby}{$limit}";
    
    // Checking if the built query is of correct format
    if($this->valid_query($sql))
    {
        // Initiating the mysqli statement
        $stmt = $this->mysqli_link->stmt_init();
        
        // Setting the last sql used
        $this->last_sql = $sql;
        
        // Preparing the statement
        $stmt->prepare($sql) or die($stmt->error);
        
        // Making sure we have anything to bind
        if(!empty($where[2]))
        {
            // The arguments array
            $array = array(array($where[1]), $where[2]);
            
            // Merging all of the arrays into one array
            $array = call_user_func_array("array_merge", $array);
            
            // The reference array being created
            $refArray = $this->make_ref($array);
            
            // Initiating the mysqli_stmt VIA ReflectionClass
            $ref = new ReflectionClass('mysqli_stmt');
            
            // Getting the bind_param function
            $method = $ref->getMethod("bind_param");
            
            // Invoking the function
            $method->invokeArgs($stmt, $refArray);
        }
        
        // The last stmt object we used
        $this->last_stmt = $stmt;
        
        // Returning the built and prepared SQL query
        return $stmt;
    }
    else
    {
        // There was an error in the SQL Generation, return false
        return false;
    }
}

它适用于build_query（）但不适用于insert（）！



我有4个问号（用于绑定），4种类型（sssi）和4个参数（$ refArray中的类型和参数）......一切都匹配......也许MySQLi忘了怎么做数学？

It works in build_query() but not in insert()!

I have 4 question marks (for binding), 4 types (sssi) and 4 parameters (types and parameters in $refArray)... everything matches... maybe MySQLi forgot how to do math?

推荐答案

tbl_name，

fields，

values，

这篇关于MySQLi INSERT bind_param问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！