Dynamically generated MySQLI prepared statement not working

Issue

I’m using a prepared statement to write an article to a database. There are some optional fields, which should not be written to the DB if left empty. That’s why I want to use dynamically prepared statements. I’ve already found some answers here on SO, and generally speaking the solution is using call_user_func_array(). But this somehow does not seem to be working.

Code:

//generating query
$art_str = 'INSERT INTO table SET ';
$art_str.= 'col1=?';
$art_str.= ', col2=?';
$art_str.= ', col3=?';

$art_stmt_params = array(); //array with parameters for binding
$art_stmt_params[] = 'sss'; //$types

//obligated parameters (already been checked)
$art_stmt_params[] = $_POST['par1'];
$art_stmt_params[] = $_POST['par2'];
$art_stmt_params[] = $_POST['par3'];

//$articleParagraphs is an Array. Correctly generated and checked
if(isset($articleParagraphs)){
    $art_str.= ', col4=?'; //expanding query
    $art_stmt_params[0].= 's'; //adding type
    $art_stmt_params[] = json_encode($articleParagraphs); //adding parameter to array
}

if(!empty($_POST['par5'])){
    $art_str.= ', col5=?';
    $art_stmt_params[0].= 's';
    $art_stmt_params[] = $_POST['par5'];
}

if(!empty($_POST['par6'])){
    $art_str.= ', col6=?';
    $art_stmt_params[0].= 's';
    $art_stmt_params[] = $_POST['par6'];
}

$art_stmt = $mysqli->prepare($art_str); //$mysqli is correct
call_user_func_array(array($art_stmt, 'bind_param'), $art_stmt_params);

$art_stmt->execute();
$art_stmt->close();

The code above does not generate any errors. And code following the code above is also executed correctly. But nothing is written into the DB. Everything is correct: column names, table name, database connection, parameters which were posted. When I do a var_dump() on the call_user_func_array() however, it prints NULL. Also I’m using the prepared statements earlier on the same page, to write some meta data to the database. Since the paramaters here are always the same ones I haven’t used dynamic prepared statements here, but $stmt->bind_param(...). I hope you’ll be able to help me with this issue. Thanks!

ps. This is my first time using prepared statements (it’s been a while since I’ve been sql’ing)

Solution

Posted on behalf of the OP:

I’ve managed to fix it, by creating if(){}elseif(){}elseif for each possible combinations of optional parameters and $art_stmt->bind_param(...). Now it works. I still don’t know why call_user_func_array() didn’t work though.

Answered By – halfer

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published