István Barna István Barna - 3 months ago 12
MySQL Question

PHP,mysqli : Dynamic prepared statement not works

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 )

Answer

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.