Jump to content

Recommended Posts

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);
Link to post
Share on other sites

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 by KillGorack
Link to post
Share on other sites

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.
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.