Adding prepare statement results in warning

Question

I am trying to create the best global select query using prepare statements, and everything is ok excepting the fact that I am getting a warning:

Warning: mysqli_stmt_bind_result(): Number of bind variables doesn't match number of fields in prepared statement

The unique global select function

function querysp($selectquery, $type_bind, $colval) {
    global $db;
    $stmt = $db->prepare($selectquery);
    $stmt->bind_param($type_bind,$colval);
    $stmt->execute();
    mysqli_stmt_bind_result($stmt, $colval);
    $arr = array();
    if ($stmt) {
        while ($result = mysqli_stmt_fetch($stmt)) {
            array_push($arr, $result);
        }
    }
    return $arr;    
}

The example use:

$select = "SELECT * from advertising WHERE status = ?";
$status = 1;
$advertising = querysp($select, 'i', $status);
foreach ($advertising as $ad) {
    $banner[] = $ad['banner'];
    $bannercode[] = $ad['bannercode'];
    $location[] = $ad['location'];
    $status = $ad['status'];
}

What am I missing? Sorry did not get exactly prepare staments, checked also here at SO and on Google, but can't get to fix this.

EDIT 2

I have changed the select query because I changed the type of bind param from b (I thought it meant boolean) to i, but still getting the error.

EDIT 3 - current version -- still getting same error:

$select = "SELECT banner, bannercode, location, status from advertising WHERE status = ?";
$status = 1;
$advertising = querysp($select, 'i', $status);
foreach ($advertising as $ad) {
    $banner[] = $ad['banner'];
    $bannercode[] = $ad['bannercode'];
    $location[] = $ad['location'];
    $status = $ad['status'];
}

and the function

function querysp($selectquery, $type_bind, $colval) {
    global $db;
    $stmt = $db->prepare($selectquery);
    $stmt->bind_param($type_bind,$colval);
    $stmt->execute();
    $stmt->bind_result($result);
    $arr = array();
    while($stmt->fetch()) {
        $arr[] = $result;
    }
    $stmt->close();
    return $arr;
}

Answer 1

Took some while but here's my version of the prepared statements its a quite the wall but it pretty much captures most errors that can possibly be made. I tried adding some doc here and there to explain what happens. Just read it through step by step and you can hopefully understand what happens. Ask questions if anything is unclear.

To use the class posted below do this.

$query  = "SELECT ? FROM ?"; // can be any query
$params = array($param1, $param2); //must equal to ammount of "?" in query.
//an error will occur if $param1 or 2 is not of the type string, int, 
//bool or double, it can however be a double like this 2,1 instead of 2.1
$db = new databaseHandler();
$result = $db->runQuery($query, $params);
//or for short runQuery("SELECT * FROM *" , array());
if($result !== false){
    while($row = mysqli_fetch_array($result){
        $column1 = $row['columnname'];
        //just do with the result what you want.
    }
}else{
    echo "error occured";
}

This is the class that will be able to handle database interactions. Take not that you need to set the connection the way you feel best. You can run all types of queries on this.

class databaseHandler{

private $x = ""; //required

//$query = the query, $params = array with params can be empty.
public function runQuery($query, Array $params){
    if($this->checkparams($params, $query)){
        //starts and returns the database connection
        $con = startConnection();    //<--- CHANGE THIS SO IT WORKS FOR YOU
        if($stmt = $con->prepare($query)){
            //obtains type of params if there are any.
            if(count($params) < 0){
                $type = "";
                $i=0;
                foreach($params as $par){
                    $par = $this->checktype($par);
                    $params[$i] = $par;
                    $type = $this->setType($type);
                    if($type === false){
                        echo "type error occured"
                        return false;
                    }
                    $i++;
                }
                //sets $type on the first spot of the array.
                array_unshift($params, $type)
                //binds the params
                call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
            }
            $stmt->execute();
            $result - $stmt->get_result();
            stmt->close();
            return $result; // return the result of the query.
        }else{
            echo "error occured, bad connection";
            return false;
    }else{
        echo "params dont match prepared statement";
        return false;
    }
}

//checks if params are equal to prepared statement requirements.
checkparams($params, $query){
    //counts occurences of ? in query.
    $count = substr_count($q, "?");
    $i = count($params);
    if($count == $i){
        return true;
    }else{
        return false;
    }
}

//Checks the type of a param
public function checktype($par){
    $this->x = gettype($par);
    if($this->x == "string"){
        $npar = str_replace(",", ".", $par);
        if(is_numeric($npar)){
            $this->x = "integer";
            if(strpos($npar, ".")){
                $this->x="double";
                return $npar;
            }
        }
    }
    return $par;
}

//sets/adds to the type.
public function setType($type){
    //$this->x from checktype;
    switch($this->x){
        case "string":
        $type = $type."s";
        break;
        case "integer":
        $type = $type."i";
        break;
        case "double":
        $type = $type."d";
        break;
        case "boolean":
        $type = $type."b";
        break;
        case "NULL":
        $type = $type."s";
        default:
            return false;
    }
    return $type; 
}

//This function exist to resolve a few version errors in php.
//not sure what it does exactly, but it resolved some errors I had in the past.               
function refValues($arr){
    if(strnatcmp(phpversion(),'5.3') >= 0){
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}
}
}

So pretty much what happens here are a set of checks to execute the query, if anything goes wrong it returns false if nothing goes wrong it returns the result of the query even if the result is empty. It is also possible to not do all of this in a class, though that would make $x global. I think it would be best if you modify certain things so they fit your application best. like the error handling/messages variable names, etc.

The only thing this code doesnt protect you at is errors in your query.

EDIT ---- I DID FIND something this code didnt protect against too, inserting NULL values. I changed this code to make it protect against inserting NULL values, they will be inserted as type string. The database will see its NULL and insert it as NULL value.

just dont try inserting objects or empty values because that would be useless anyway.