php mysqli_bind_param 函数问题。尝试实现准备好的语句

Question

我正在尝试建立与MySql的数据连接并创建准备好的语句，其中query_f函数接受任意数量的参数，其中第一个参数是sql语句，其他参数是要替换的值在准备好的声明中。

这是我所拥有的。我遇到的第一个错误是当我尝试将值绑定(bind)到语句时。

function query_f(/* query, [...] */){
    $user = "root";
    $pass = "root";
    $host = "localhost";
    $database = "mcnair";
    $conn = mysqli_connect($host,$user,$pass);
    if(!$conn)
    {
        echo "Cannot connect to Database";
    }
    else
    {
        mysqli_select_db($conn, $database);
    }

    // store query
    $query = func_get_arg(0);
    $parameters = array_slice(func_get_args(), 1);
    $param = "'".implode("','",$parameters)."'";

    // Prepare the statement
    $stmt = mysqli_prepare($conn, $query);
    if ($stmt == false)
    {
        echo "The statement could not be created";
        exit;
    }

    // Bind the parameters
    $bind = mysqli_stmt_bind_param($stmt, 's', $param);
    echo mysqli_stmt_error($stmt);
    if ($bind == false)
    {
            echo "Could not bind";
    }
    else
    {
        echo "Bind successful";
    }

    // Execute the statement
    $execute = mysqli_stmt_execute($stmt);
    if ($execute = false)
    {
        echo "Could not execute";
    }



    // fetch the data
    $fetch = mysqli_stmt_fetch($stmt)
    if ($fetch == false)
    {
            echo "Could not fetch data";
    }
    else
    {
        return $fetch;
    }
}

我正在使用的函数调用是:

query_f("SELECT Hash FROM alumni WHERE Username = '?'", "zm123");

Answer 1

使用类(而不是函数)并以 OO 方式而不是以过程方式使用 mysqli 怎么样？

这是我使用的简化版本。并不完美，所以如果有人想提出改进建议，我洗耳恭听。

class Connection {

    private $connection;

    public function __construct()
    {
        //better yet - move these to a different file
        $dbhost = '';
        $dbuname = '';
        $dbpass = '';
        $dbname = '';

        $this->connection = new mysqli($dbhost, $dbuname, $dbpass, $dbname);
    }

    /*
     * This is the main function.
     * 
     * @param $arrayParams = array (0 => array('s' => 'Example string'), 1 => array('s' => 'Another string'), 2 => array('i' => 2), 3 => array('d' => 3.5) )
     */
    public function executePrepared($sql, $arrayParams)
    {
        $statement = $this->prepareStatement($sql);
        if ($statement) {
            $this->bindParameter($statement, $arrayParams);
            $this->executePreparedStatement($statement);
            $result = $this->getArrayResultFromPreparedStatement($statement);
            //only close if you are done with the statement
            //$this->closePreparedStatement($statement);
        } else {
            $result = false;
        }

        return $result;
    }

    public function prepareStatement($sql)
    {
        $statement = $this->connection->prepare($sql) or $this->throwSqlError($this->connection->error);
        return $statement;
    }

    public function bindParameter(&$statement, $arrayTypeValues)
    {
        $stringTypes = '';
        $arrayParameters = array();
        $arrayParameters[] = $stringTypes;
        foreach ($arrayTypeValues as $currentTypeVale) {
            foreach ($currentTypeVale as $type => $value) {
                $stringTypes .= $type;
                $arrayParameters[] = &$value;
            }
        }
        $arrayParameters[0] = $stringTypes;

        call_user_func_array(array($statement, "bind_param"), $arrayParameters);
    }

    public function getArrayResultFromPreparedStatement(&$statement)
    {
        $statement->store_result();

        $variables = array();
        $data = array();
        $meta = $statement->result_metadata();

        while($field = $meta->fetch_field())
            $variables[] = &$data[$field->name]; // pass by reference

        call_user_func_array(array($statement, 'bind_result'), $variables);

        $i = 0;
        $arrayResults = array();
        while($statement->fetch())
        {
            $arrayResults[$i] = array();
            foreach($data as $k=>$v)
            {
                $arrayResults[$i][$k] = $v;
            }
            $i++;
        }

        return $arrayResults;
    }

    public function executePreparedStatement($statement)
    {
        $result = $statement->execute() or $this->throwSqlError($statement->error);
        return $result;
    }

    public function closePreparedStatement($statement)
    {
        $statement->close();
    }

    public function throwSqlError()
    { ... }

}