php mysqli_bind_param function issues. Trying to implement prepared statements

Question

I am trying to establish a data connection to the MySql and create prepared statements, where the query_f function takes in any number of parameters, where the first parameter is the sql statement, and the other parameters are the values that would be substituted in the prepared statement.

Here is what I have. The first error I got is when I am trying to bind the values to the statement.

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

And the function call I am using is:

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

Before writing a custom function, you need to practice with raw API. — Your Common Sense
– Your Common Sense, Commented Jun 16, 2014 at 19:55
Don't put the ? in quotes. That will all be handled for you. SELECT Hash FROM alumni WHERE Username = ?. — gen_Eric
– gen_Eric, Commented Jun 17, 2014 at 15:50

Bogdan · Accepted Answer · 2014-06-17 15:47:49Z

How about using a class (instead of a function) and using mysqli in the OO way and not in the procedural way?

This is a simplified version of what I use. Not perfect, so if anyone would like to suggest improvements, I'm all ears.

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()
    { ... }

}

Collectives™ on Stack Overflow

php mysqli_bind_param function issues. Trying to implement prepared statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related