PHP mysqli bind_param

[KillGorack]

Is there a way to bind parameters in a select SEPARATELY?

 

I know how to do this in one line, but would like some direction on how to do this one line at a time, OR means to stick that array into the bind_param line where it will work with different array lengths.

 

using mysqli to modify some older code.

 

Understanding of course that the ?'s count must match the amount of parameters.

 

End result to make a generic reader function.

  $sql = "SELECT ID, foo, bar FROM table WHERE ID = ? AND bar = ?";
  $vars[] = array('typ' => "i", 'vlu' => $id);
  $vars[] = array('typ' => "s", 'vlu' => $rkey);
  $stmt = $db->prepare($sql);
  foreach($vars as $var){
    $stmt->bind_param($var['typ'], $var['vlu']);
  }
  $stmt->execute();
  $res = $stmt->get_result();
  $rows = $res->fetch_array(MYSQLI_ASSOC);

[Barand]

You could switch to PDO. EG

sql = "SELECT ID, foo, bar FROM table WHERE ID = ? AND bar = ?";
$params = [ $id, $rkey ];
$stmt = $db->prepare($sql);
$stmt->execute($params);

[KillGorack]

Yea  I know , it's sooo much easier and going back to mysqli is a headache....currently working with some older code.. until I get everything switched over, it's not an option.

 

investigating using "call_user_func_array" currently..

Edited January 9, 2018 by KillGorack

[gizmola]

This is essentially a built-in depending on how you look at it. See http://php.net/manual/en/mysqli-stmt.execute.php

 

People sometimes miss the property of variable binding, in that you can repeatedly execute a query, each time with a different set of variables, just by changing the contents of those variables and doing a new execute.

 

In your example a loop that associates bound variables with the array values and a subsequent execute would work.

 

  $sql = "SELECT ID, foo, bar FROM table WHERE ID = ? AND bar = ?";
  $bar = '';
  $stmt = $db->prepare($sql);
  $stmt->bind_param('is', $id, $bar);
  foreach($vars as $var){
    $id = $var['id'];
    $bar = $var['vlu'];
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = $res->fetch_array(MYSQLI_ASSOC);
  }

Where your example lacks consistency, is in the implication that that your query requires an integer and a string. Once you set up the parameter binding, it doesn't make sense that you would run the same query with a string and a string, when you require an integer. The binding is for data typing the parameter for the query. It is not something PHP needs.

 

If you just want to build up the parameter list on the fly for a single query, you could certainly do that, and call_user_func or some other array callback function might be beneficial in doing so, but that would also need to match the query specifically, so you would need at very least an array like:

 

$parms[] = array(
    'column_name' => 'bar',
    'column_type' => 's',
    'value' => 'some bar'
);

This of course assumes that you are going to construct a large WHERE clause. When you look at querybuilders for ORM's like Doctrine or Eloquent you'll see that query building is complicated if you want any degree of sophistication. How much of this code you want to write for DRY and reusability is highly dependent on the restrictions that are acceptable for your code.