Dynamically bind mysqli_stmt parameters and then bind result (PHP)

Answers of > Dynamically bind mysqli_stmt parameters and then bind result (PHP)

I'm trying to dynamically bind mysql_stmt w3coded php mysqli parameters and get the result in an associative w3coded php mysqli array. I've found this post here on w3coded php mysqli stackoverflow where Amber posted an answer with w3coded php mysqli the following code:,Here are a couple of w3coded php mysqli functions which you may find it useful to w3coded php mysqli examine. The first allows you to bind the w3coded php mysqli results of a query to an associative array, and w3coded php mysqli the second allows you to pass in two arrays, one w3coded php mysqli an ordered array of keys and the other an w3coded php mysqli associative array of data for those keys and w3coded php mysqli have that data bound into a prepared w3coded php mysqli statement:",I cut the code from a class I made w3coded php mysqli that can dynamically query data. There are a few w3coded php mysqli things I removed to make it easier to read. In w3coded php mysqli the class I have I allow the user to define w3coded php mysqli definitions tables and foreign keys so that data w3coded php mysqli entry on the front-end is restricted as well as w3coded php mysqli filter and sort options for said related data. w3coded php mysqli These are all parameters I removed as well as w3coded php mysqli the automated query builder.,I tried studying w3coded php mysqli the code to understand what it does and I've w3coded php mysqli made the second function work properly but I w3coded php mysqli don't know what I should do to be able to w3coded php mysqli utilize the first function. How do I use it to w3coded php mysqli retrieve an array similar to w3coded php mysqli mysqli_result::fetch_assoc()?

Edited, to fix bug when fetching multiple rows

$sql = "SELECT `first_name`,`last_name` FROM `users` WHERE `country` =? AND `state`=?";
$params = array('Australia','Victoria');

/*
    In my real app the below code is wrapped up in a class 
    But this is just for example's sake.
    You could easily throw it in a function or class
*/

// This will loop through params, and generate types. e.g. 'ss'
$types = '';                        
foreach($params as $param) {        
    if(is_int($param)) {
        $types .= 'i';              //integer
    } elseif (is_float($param)) {
        $types .= 'd';              //double
    } elseif (is_string($param)) {
        $types .= 's';              //string
    } else {
        $types .= 'b';              //blob and unknown
    }
}
array_unshift($params, $types);

// Start stmt
$query = $this->connection->stmt_init(); // $this->connection is the mysqli connection instance
if($query->prepare($sql)) {

    // Bind Params
    call_user_func_array(array($query,'bind_param'),$params);

    $query->execute(); 

    // Get metadata for field names
    $meta = $query->result_metadata();

    // initialise some empty arrays
    $fields = $results = array();

    // This is the tricky bit dynamically creating an array of variables to use
    // to bind the results
    while ($field = $meta->fetch_field()) { 
        $var = $field->name; 
        $$var = null; 
        $fields[$var] = &$$var; 
    }


    $fieldCount = count($fieldNames);

// Bind Results                                     
call_user_func_array(array($query,'bind_result'),$fields);

$i=0;
while ($query->fetch()){
    for($l=0;$l<$fieldCount;$l++) $results[$i][$fieldNames[$l]] = $fields[$fieldNames[$l]];
    $i++;
}

    $query->close();

    // And now we have a beautiful
    // array of results, just like
    //fetch_assoc
    echo "<pre>";
    print_r($results);
    echo "</pre>";
}

$results is an empty array, that holds the result

    if (!is_string($sqlStmt) || empty($sqlStmt)) {
        return false;
    }

    // initialise some empty arrays
    $fields = array();
    $results = array();

    if ($stmt = $this->prepare($sqlStmt)) {
        // bind params if they are set
        if (!empty($params)) {
            $types = '';
            foreach($params as $param) {
                // set param type
                if (is_string($param)) {
                    $types .= 's';  // strings
                } else if (is_int($param)) {
                    $types .= 'i';  // integer
                } else if (is_float($param)) {
                    $types .= 'd';  // double
                } else {
                    $types .= 'b';  // default: blob and unknown types
                }
            }

            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) {
                $bind_name = 'bind' . $i;       
                $$bind_name = $params[$i];      
                $bind_names[] = &$$bind_name;   
            }

            call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        // execute query
        $stmt->execute();

        // Get metadata for field names
        $meta = $stmt->result_metadata();

        // This is the tricky bit dynamically creating an array of variables to use
        // to bind the results
        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $fields[$var] = &$$var;
        }

        // Bind Results
        call_user_func_array(array($stmt,'bind_result'),$fields);

        // Fetch Results
        $i = 0;
        while ($stmt->fetch()) {
            $results[$i] = array();
            foreach($fields as $k => $v)
                $results[$i][$k] = $v;
            $i++;
        }

        // close statement
        $stmt->close();
    }

Just to compare excellent answers from @Emmanuel and @matzino with the code you can get if choose PDO over mysqli:

$sql = "SELECT `first_name`,`last_name` FROM `users` WHERE `country` =? AND `state`=?";
$params = array('Australia','Victoria');

$stm = $query->prepare($sql);
$stm->execute($params); 
$results = $stm->fetchAll(); // or fetch() or fetchColumn() depends on expected type

Current topics : Dynamically bind mysqli_stmt parameters and then bind result (PHP)

Newly Added Questions