0

I am trying to rewrite some code to eliminate the call_user_func_array calls, and have the following code so far :

if($stmt = $this->_db->prepare($sql)) {
    $bind_arguments = $this->params;

    // remove first element (eg. 'ssiss')
    $bind_arguments = array_shift($bind_arguments);

    // execute query passing values
    $res = $stmt->execute($bind_arguments);

}

Here is a var_export of $this->params :

array ( 0 => 's', 1 => 'admin', )

$res always evaluates to null when trying the above method. According to this comment, it appears that everything is correct, except my array has integer keys, and there is an additional , after the last element.

What do I need to do to format this array (removing the first element), to pass to the $stmt->execute method as the variable containing the array elements (this is not a static array. elements in it are modified by other methods in the class to add or remove elements from it).

Kraang Prime
  • 9,981
  • 10
  • 58
  • 124
  • 2
    This is notoriously difficult in *mysqli* but trivial in PDO. Consider switching – Phil Aug 30 '16 at 04:29
  • 1
    mysqli::execute (oo interface) doesn't need an array inside http://php.net/manual/en/mysqli-stmt.execute.php – Kevin Aug 30 '16 at 04:41
  • 1
    FYI, the [comment you linked to](http://php.net/manual/en/pdostatement.execute.php#116281) is talking about PDO, not MySQLi – Phil Aug 30 '16 at 04:43
  • 1
    `$bind_arguments = array_shift($bind_arguments);` assigning shifted argument to `$bind_arguments`... You not passing array. You passing it's first element. – BlitZ Aug 30 '16 at 04:44
  • @Ghost - ya, i just noticed that. I liked the doc for PDO. I guess there is no way to use mysqlnd and pass params in a dynamic manner. I find prepared statements such a pita of repetition code. Bind results, bind params, it's horrible esp when dealing with tables that have lots of fields, or joins. Has anyone ever successfully wrapped this to return results ? – Kraang Prime Aug 30 '16 at 04:52
  • @Num6 - also, that is incorrect. See [array_shift](http://php.net/manual/en/function.array-shift.php) . The return value of `array_shift( &arr )` is `&arr` minus the first element. :) – Kraang Prime Aug 30 '16 at 04:55
  • 1
    @SamuelJackson _Returns the **shifted value**, or NULL if array is empty or is not an array._ http://php.net/manual/en/function.array-shift.php – BlitZ Aug 30 '16 at 05:36

2 Answers2

1

First of all, to make things straight: you cannot pass an array to mysqli_execute() as this function accepts no parameters at all. What you really need is to pass an array to mysqli_bind_param()

However, the only way around call_user_func_array call is a splat operator available since PHP 5.6

function query($query, $params = NULL, $types = NULL)
{
    $statement = $this->mysqli->prepare($select);
    $types = $types ?: str_repeat('s', count($params));
    $statement->bind_param($types, ...$params);
    $statement->execute();
    return $statement;
}

Yet the preferred and bullet-proof solution would be apparently switching to PDO, which indeed has the capability of sending an array with parameters right into execute().

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • is `...` a term, or just signaling for 'other stuff' ? – Kraang Prime Aug 30 '16 at 08:57
  • @SamuelJackson its a [relatively] new operator in PHP, check http://php.net/manual/ru/migration56.new-features.php for "Argument unpacking" – Your Common Sense Aug 30 '16 at 09:12
  • Nice. <3 for this feature. Tried to google it before you replied and you would be amazed at the stuff that comes up searching for `"..." php operator` :) – Kraang Prime Aug 30 '16 at 09:26
  • Marking this as correct as reading up on that operator (5.6) is pretty cool, but for some reason it doesn't seem to work for me. Is there something aside from having php 5.6 i need to enable support for it ? I don't get any error, but I tried both as `$bind_arguments = $this->params; $types = array_shift($bind_arguments); $stmt->bind_param( $types, ...$bind_arguments );` and `$stmt->bind_param( ...$this->params );`. Neither give an error but they produce the same result where I can't get any results from it after `$stmt->execute()` . – Kraang Prime Aug 30 '16 at 09:39
  • you cannot make two calls to bind_param. it should be only one. in your case you should get rid of array shift and make something like this `$stmt->bind_param(...$this->params)`. – Your Common Sense Aug 30 '16 at 10:29
  • But for my life I won't understand why all this hassle when there is PDO – Your Common Sense Aug 30 '16 at 10:30
  • First, the `and` meant an alternative way, not simultaneously. Meaning I tried 'method 1' AND 'method 2' :) . Second, I am borking around with PDO at the same time, however this code should work -- in theory ? (fyi `$this->params` contains the field type string - eg : 'sisssiss' etc as the first value in the array .. if you hadn't guessed that already) – Kraang Prime Aug 30 '16 at 10:32
  • what is the exact content of $this->params? – Your Common Sense Aug 30 '16 at 10:33
  • by the way, does it pass prepare? – Your Common Sense Aug 30 '16 at 10:34
  • Updated last comment :) was already clarifying that. I presumed it was discernible from the first attempt removing the first element and passing it as `$types`. I clarified anyway. – Kraang Prime Aug 30 '16 at 10:35
  • Consider everything I offer up to be contained within the `if($stmt = $this->_db->prepare($sql)) { .. stuff here ... }` condition in my original question. – Kraang Prime Aug 30 '16 at 10:36
  • Programming is not a guesswork. what you *think* of a variable contents can be dramatically different from what is actually there. Either way, you have to enable error reporting and see what error you get. Check here: http://stackoverflow.com/a/22662582/285587 – Your Common Sense Aug 30 '16 at 11:08
  • Programming is not guess work, correct. Interpreting code is also following logic flow and naming conventions to determine coding pattern and figure out what the code does. For example, if I said `$x = 5; $y = 20; echo $y * $x;` it isn't hard to see what that does even without naming convention. In my examples supplied in the comments here, `$types` (same var name you used) ` = array_shift($bind_params);` shows very clearly that the first value is at least assumed to contain the requisite composite string as required by -- looking at next lines .. `bind_param`. – Kraang Prime Aug 30 '16 at 11:51
  • Breaking it down even further, I could write entire books on every single line, but I truly do assume basic knowledge of the commands (which you have since you showed me the `...` operator), so I really don't understand where you would have problems understanding the snippet unless you assume that I have things other than expected data stored in those values. If that is the case, I could simply tell you - No. I have the requisite values as `call_user_func_array` WORKS!!! which it wouldn't work as expected if I had those values off. I would get a `bind_param` error message. – Kraang Prime Aug 30 '16 at 11:53
  • For the code snippet `$x = 5; $y = 20; echo $y * $x;` values for `$x` and `$y` are clearly seen. but for `$this->params` variable it is not. That's the difference. – Your Common Sense Aug 30 '16 at 11:59
-2

Array should be associative array and key name should be column name of table and data should be value of key. Like the following:

$arr = [ 'column1' => 'value1', 'column2' => 'value2' ];

than use $res = $stmt->execute($arr);

BlitZ
  • 12,038
  • 3
  • 49
  • 68
piyushiiitm
  • 25
  • 3