Dynamically Bind MySQLi_Stmt Parameters and Then Bind Result (Php)

Dynamically bind mysqli_stmt parameters and then bind result

Okay, here is a way to do it:

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

How to bind mysqli bind_param arguments dynamically in PHP?

found the answer for mysqli:

public function get_result($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = $this->mysqli->prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++) 
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query 
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata(); 

        while ($field = $meta->fetch_field()) { 
            $var = $field->name; 
            $$var = null; 
            $parameters[$field->name] = &$$var; 
        }

        call_user_func_array(array($stmt, 'bind_result'), $parameters); 

        while($stmt->fetch()) 
        { 
            return $parameters;
            //print_r($parameters);      
        }

        # the commented lines below will return values but not arrays
        # bind result variables
        //$stmt->bind_result($id); 

        # fetch value
        //$stmt->fetch(); 

        # return the value
        //return $id; 

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

then:

$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'s',array('1')));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql, 'ss',array('1','Tk')));

mysqli is so lame when comes to this. I think I should be migrating to PDO!

Dynamic bind_result on PHP

To start, your code that generates the year fields has a nice syntax error:

"SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0) ".$year.","

The SUM( opening statement is missing a closing parentheses. On top of that, you have a floating ".$year." that isn't part of any of the enclosed methods (though, it could be used as an alias; I'm not used to seeing it without a preceeding AS though - so this could be my mistake). To take a guess, I would say to replace the ".$year." with a ) and that should fix that part:

$concatYear .= "SUM(IF(c.datecreated='".$year."',IF(LOWER(c.fullTimeEployeeType)='basic hour rate', c.fullTimeEployeeTypeAmount*2080 , c.fullTimeEployeeTypeAmount),0)),";

If the $year addition is in fact to be an alias, you could add the closing parentheses immediately before it to close the SUM() function.

Regarding dynamically binding the variables, my ideal solution actually comes from a similar question/answer on SO (this is a direct copy/paste and I take no credit for it, but I do like it =P):

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

Dynamic select mysqli query with dynamic parameters returns error doesn't match number of bind variables

Because:

You are using user-supplied data, you must assume that your query is vulnerable to a malicious injection attack and
the amount of data that is to be built into the query is variable/indefinite and
you are only writing conditional checks on a single table column

You should use a prepared statement and merge all of the WHERE clause logic into a single IN statement.

Building this dynamic prepared statement is more convoluted (in terms of syntax) than using pdo, but it doesn't mean that you need to abandon mysqli simply because of this task.

$mediaArray ='Facebook,Twitter,Twitch,';
$otherMedia = 'House';

$media = array_unique(explode(',', $mediaArray . $otherMedia));
$count = count($media);

$conn = new mysqli("localhost", "root", "", "myDB");
$sql = "SELECT * FROM mediaservices";
if ($count) {
    $stmt = $conn->prepare("$sql WHERE socialmedianame IN (" . implode(',', array_fill(0, $count, '?')) . ")");
    $stmt->bind_param(str_repeat('s', $count), ...$media);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $result = $conn->query($sql);
}
foreach ($result as $row) {
    // access values like $row['socialmedianame']
}

For anyone looking for similar dynamic querying techniques:

SELECT with dynamic number of LIKE conditions
INSERT dynamic number of rows with one execute() call

Possible to pass string of variables: to stmt-bind_param()?

There's a number of solutions to call mysqli_stmt::bind_param with a dynamic number of parameters, none of them involve describing a list as a string, that's just dumb. When you need a list, you use an array.

You can use call_user_func_array() to call any function in PHP specifying the parameters it should be called with in an array. It creates a problem though because the paramters of mysqli_stmt::bind_param have to be passed as reference and not by value, but workarounds can be made.

Example:

$vars = [$dogs, $cats, $weasels];
$refs = [$variabletypelist];

foreach ($vars as &$val)
    $refs[] = &$val;
call_user_func_array([$stmt, 'bind_param'], $refs);

You can also use the non-oop version of this method if this syntax looks confusing to you:

$vars = [$dogs, $cats, $weasels];
$refs = [$stmt, $variabletypelist];

foreach ($vars as &$val)
    $refs[] = &$val;
call_user_func_array('mysqli_stmt_bind_param', $refs);

It can also be done with ReflectionClass() that (I didn't test) apparently doesn't require fixing references:

$vars = [$dogs, $cats, $weasels];

$ref = new \ReflectionClass('mysqli_stmt');
$met = $ref->getMethod('bind_param');
$met->invokeArgs($stmt, array_merge([$variabletypelist], $vars));

Variable parameter/result binding with prepared statements

In PHP you can pass a variable number of arguments to a function or method by using call_user_func_array. An example for a method would be:

call_user_func_array(array(&$stmt, 'bindparams'), $array_of_params);

The function will be called with each member in the array passed as its own argument.

Dynamically Bind MySQLi_Stmt Parameters and Then Bind Result (Php)