1

Why does this return no MySQL rows:

$sql = $conn -> prepare("select * from table where id = ?");
$sql -> bind_param('i', $array[0]);
$array = array(1);
$sql -> execute();

But when I put the array before bind_param it's working. It works like this:

$sql = $conn -> prepare("select * from table where id = ?");
$array = array(1);
$sql -> bind_param('i', $array[0]);
$sql -> execute();

Or

$array = array(1);
$sql = $conn -> prepare("select * from table where id = ?");
$sql -> bind_param('i', $array[0]);
$sql -> execute();
Dharman
  • 30,962
  • 25
  • 85
  • 135
Khurshid Alam
  • 25
  • 3
  • 2
    You'd get a notice for undefined array index, since it is: not defined yet. – mario May 19 '19 at 17:03
  • 1
    @mario [Referencing an undeclared variable automatically defines its name in that scope](https://stackoverflow.com/questions/15852981/why-php-does-not-complain-when-referencing-a-non-existing-variable#comment22560867_15852981) – Your Common Sense May 19 '19 at 20:33
  • @YourCommonSense Alright. No warning then for current PHP versions. Though, OP probably doesn't need to be confused with variable references and their autocreation. Lets just leave it at undefined and overwritten values. – mario May 19 '19 at 20:51

2 Answers2

2

mysqli_statement::bind_param($types, &$var1) accepts variables by reference. So, it's intended to deal with variables that are not defined yet.

And normally you can do like this

$sql = $conn -> prepare("select * from table where id = ?");
$sql -> bind_param('i', $i);
$i = 1;
$sql -> execute();

and it would work flawless.

However, an array is another matter. Like it is noted my @mario in the comment, Referencing an undeclared variable automatically defines it. So, when you reference a variable (and here $sql->bind_param('i', $array[0]); you are effectively doing it), both $array and $array[0] would be created. Where $array[0] is a reference.

Were you assigning a value to this variable, i.e $array[0] = 1;, it would have worked.
But you assigned an brand new value to the entire array. As a result, it now contains not a reference, but a new value.

The above can be illustrated with a simple code snippet

As you can see, as long as you retain the original array member, the reference remains.
But as soon as you assign a brand new value to the entire array, the reference is gone!

But it's gone only from the array, but not from the function. There it still points to that odd &NULL value. That's why you cannot get any result.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-3

shared my thoughts in the comments

$sql = $conn -> prepare("select * from table where id = ?");
$sql -> bind_param('i', $array[0]);// undefined since $array is not yet defined
$array = array(1);// you've just defined it here
$sql -> execute();

$sql = $conn -> prepare("select * from table where id = ?");
$array = array(1);// you have defined it first
$sql -> bind_param('i', $array[0]);// PHP will know $array, since you've defined it in the row above
$sql -> execute();

hope this helps!

if there's something unclear, please let me know!

Rangga Wiriaputra
  • 62
  • 4