PHP,mysqli : Dynamic prepared statement not works

Question

I need to create dynamic queries for a webpage. The users can select what categories they want to search in (the column names in the SQL database) and what are the values they are looking for. I run through some similar posts on stackoverflow and other sites, but I wasn't able to find a solution. I use the code below (It's for testing, so the category I wrote in the category names and the values). In case I don't try to bind the parameters dynamicly the query works well, I can print the values of the $tester array. What could be the solution for this problem? I guess the problem is with the 'call_user_func_array...' part.

$stmt = mysqli_stmt_init($conn);
//The category names and variable names
$cat1 = "LCVAR";
$cat2 = "APOLG";
$LCVAR = "KALOCSA";
$APOLG = "magyar";
//creating the statement
$statement = "SELECT VNEV,KNEV FROM TORZS WHERE ";
$statement = $statement . " " . $cat1 . " ". "=?". " ". "AND" . " ";
$statement = $statement . " " . $cat2 . " ". "=?";
//SELECT VNEV,KNEV FROM TORZS WHERE LCVAR =? AND APOLG =?
mysqli_stmt_prepare($stmt,$statement);
$a_params = array();
$a_param_type = array("s","s");
$a_bind_params = array($cat1 => $LCVAR,$cat2 => $APOLG);
$param_type = '';
// creating array for call_user_function_array
$n = count($a_param_type);
for($i = 0; $i < $n; $i++) {
$param_type .= $a_param_type[$i];
} 
$a_params[] = &$param_type;
for($i = 0; $i < $n; $i++) {
$a_params[] = &$a_bind_params[$i];
}
call_user_func_array(array($stmt,'mysqli_stmt_bind_param'),$a_bind_params);
mysqli_stmt_execute($stmt);
$result = array();
mysqli_stmt_bind_result($stmt, $result['VNEV'], $result['KNEV']);
$tester = array();
while(mysqli_stmt_fetch($stmt)){
$tester[] = array($result['VNEV'],$result['KNEV']) ;
}

EDIT 1:

Result of print_r($stmt) :

  mysqli_stmt Object 
     ( [affected_rows] => 0 
       [insert_id] => 0 
       [num_rows] => 0 
       [param_count] => 2 
       [field_count] => 2 
       [errno] => 0 
       [error] => 
       [error_list] => Array ( ) 
       [sqlstate] => 00000 
       [id] => 1 )

Result of print_r($a_bind_params) :

      Array ( [LCVAR] => KALOCSA
              [APOLG] => magyar )

What's the problem? "not works" is very vague. What have you done to debug it? Have you looked at the generated queries before you pass them to `mysqli_stmt_prepare` to see if they are right? — Quentin, Sep 03 '16 at 19:56
Yes, I checked the queries. If I don't try binding parameters dynamicly and I give the parameters in a 'static' statement, everything works fine. I guess the problem could be with the 'call_user_func_array(array($stmt,'mysqli_stmt_bind_param'),$a_bind_params)' part. — István Barna, Sep 03 '16 at 20:04
I wrote before that the query itself works fine in this state. The problem starts when I want to use dynamic parameter binding and I use call_user_func_array. — István Barna, Sep 03 '16 at 20:08

score 0 · Accepted Answer · edited May 23 '17 at 12:32

0

From the PHP Manual page for bind_param:

Note:

Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.

Beyond this no one can give you a clearer solution because you need to tell us exactly what the problem is, what your PHP error log is telling you and what your MySQLi stmt error says.

It may also be helpful to yourself and to us if you can output what your SQL query actually is (print_r($stmt)) as well as what your bound parameters actually are (print_r($a_bind_params)) .

Edit your question and add these details and I'm sure a solution will then be obvious.

P.s As referenced by Fred-ii you can simplify your $statement with:

 $statement = $statement . " " . $cat1 . " ". "=?". " ". "AND" . " ";

becoming:

 $statement .= " " . $cat1 . " ". "=?". " ". "AND" . " ";

And applying this for each time (twice I can see) that your declaring of a variable value contains the variables preceeding value, that's what the .= syntax does for you.

edited May 23 '17 at 12:32

Community

1
1

answered Sep 03 '16 at 20:10

Martin

19,815
6
53
104

In this case what sould I do to solve this problem? I'm quite new to PHP. – István Barna Sep 03 '16 at 20:18
Do what I have advised in my answer, set out what your MySQLi errors are and what your PHP errors are, and then search Stack Overflow for a solution to the `bind_param` issue, if this is the principle error, you will not be the first person to have this issue. @IstvánBarna – Martin Sep 03 '16 at 20:20
Thanks! Result of print_r($stmt) : mysqli_stmt Object ( [affected_rows] => 0 [insert_id] => 0 [num_rows] => 0 [param_count] => 2 [field_count] => 2 [errno] => 0 [error] => [error_list] => Array ( ) [sqlstate] => 00000 [id] => 1 ) result of print_r($a_bind_params) : Array ( [LCVAR] => KALOCSA [APOLG] => magyar ) – István Barna Sep 03 '16 at 20:23
@IstvánBarna edit your question and update these details into your question. It's hard to read code in comments. – Martin Sep 03 '16 at 20:27
@IstvánBarna I saw, that's good. Your update shows there are no errors in the SQL, read `[errno] => 0` so you need to find if your error is in the PHP code – Martin Sep 03 '16 at 20:35

PHP,mysqli : Dynamic prepared statement not works

1 Answers1