-1

I am getting the message “

Warning: mysqli_stmt_bind_param: Number of elements in type definition string doesn't match number of bind variables in…

in a simple php select statement

$wherePtest1 = "postcode= ? AND  proptype = ? AND ";
$whereVtest1 = "$lc_postcode, $proptype";

$where_no_andP = rtrim($wherePtest1, 'AND ');
var_dump($where_no_andP);
var_dump($whereVtest1); 

/* 1. create a prepared statement */
if ($stmt1 = mysqli_prepare($link, "SELECT $what_select4TS FROM $table WHERE $where_no_andP ORDER BY rent $reihenach LIMIT $offset, $rowsPerPage")) 
{
/* 2. bind parameters for markers */
mysqli_stmt_bind_param($stmt1, "ss", $whereVtest1);

/* 3. execute query */
etc

I am getting this warning EVEN THOUGH var_dump() correctly show 

string(29) "postcode= ? AND proptype = ?" 
string(9) "da8, Flat"!
Property Foyer
  • 43
  • 5
  • Like posted below, see comments for the PHP documentation of this function, for correct usage examples: http://php.net/manual/en/mysqli-stmt.bind-param.php – George Kagan Sep 21 '16 at 19:46
  • A HUGE thank you to Marc B and Martin for setting me straight. I attempted many things but not the correct one you suggested. Bravo, thank you. – Property Foyer Sep 21 '16 at 21:24

4 Answers4

2

Because this:

mysqli_stmt_bind_param($stmt1, "ss", $whereVtest1);
                                           ^^

MySQLi will NOT take your CSV data in $whereVtest1 and split it up for you. You have to EXPLICITLY provide a SINGLE value for EVERY placeholder you have:

mysqli_stmt_bind_param($stmt1, 'ss....s', $val1, $val2, .... $valN);
                                ^----------^
                                 ^------------------^
                                      ^------------------------^
Martin
  • 22,212
  • 11
  • 70
  • 132
Marc B
  • 356,200
  • 43
  • 426
  • 500
1

You have two wrong factors:

  1. Your $whereVtest1 = "$lc_postcode, $proptype"; is a string as it is encapsulated in quotes. The variables (2) are printed into the string. so in effect this variable is just one variable, it is NOT two or more concatenated (which it looks like you where trying to do).

You can not concatenate variables like this, use an array if required.

  1. mysqli_stmt_bind_param($stmt1, "ss", $whereVtest1); this is telling SQL to expect TWO string values (ss), but due to (1) you are giving it just one. This is your error.

To fix:

mysqli_stmt_bind_param($stmt1, "ss", $lc_postcode, $proptype);

This line will now work and marry up the PHP and SQL correctly.

Edit: As mentioned by scaisEdge you also need to ensure your comment sections are correctly closed.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132
0

You missed the close comment in the code. try adding */

/* 1. create a prepared statement  */
                                   ^^  here
if ($stmt1 = mysqli_prepare($link, "SELECT $what_select4TS FROM $table WHERE $where_no_andP ORDER BY rent $reihenach LIMIT $offset, $rowsPerPage"))
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • 1
    doesn't matter. you can have as many `/*` as you want. it'll all be a comment until a `*/` is encountered. – Marc B Sep 21 '16 at 19:45
  • 1
    @MarcB if the comment is not closed the sql instruction is not formed and the binding fails .. – ScaisEdge Sep 21 '16 at 19:47
  • ah yeah, sorry. should've looked at what was in the comment. missed the prepare call. but still. doesn't solve OP's problem. not enough values for the number of placeholders. – Marc B Sep 21 '16 at 19:48
  • well ...downvote a correct answer is not fair .. anyway – ScaisEdge Sep 21 '16 at 19:49
0

you are only binding one parameter, you have to bind the 2nd variable as well - for example:

mysqli_stmt_bind_param($stmt1, "ss", $whereVtest1, wherePtest1);
low_rents
  • 4,481
  • 3
  • 27
  • 55