0

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)

halfer
  • 18,701
  • 13
  • 79
  • 158
Luuuud
  • 3,720
  • 1
  • 23
  • 34
  • What kind of answer you expect? – Your Common Sense Feb 24 '14 at 18:23
  • @YourCommonSense Either if there's something wrong in my code or why nothing is written to the database. – Luuuud Feb 24 '14 at 18:26
  • So, you want someone to read your code and execute it mentally, inside their head, taking notice of all execution errors - right? – Your Common Sense Feb 24 '14 at 18:27
  • @YourCommonSense I'd call that a bit extreme, since I've stated other prepared statements are executed correctly in the same page and no errors are produced. I'd like to now for instance how a `var_dump()` on `call_user_func_array()` could return `NULL` or if I've made a (visible) mistake in my code. – Luuuud Feb 24 '14 at 18:35
  • http://stackoverflow.com/questions/18457821/how-to-make-mysqli-throw-exceptions-using-mysqli-report-strict – Your Common Sense Feb 24 '14 at 18:46
  • @YourCommonSense I had already tried that, and as stated: no errors – Luuuud Feb 24 '14 at 18:57
  • 1
    Well, if no errors - then everything works well. – Your Common Sense Feb 24 '14 at 18:59
  • if that were the case, I'd probably not post a question here... – Luuuud Feb 24 '14 at 19:09
  • Is Autocommit turned on? Is `table` really your table name? It happens to be a reserved word in SQL. – O. Jones Feb 24 '14 at 19:13
  • @OllieJones `table` is just a placeholder I put in the code here, in the actual application it's called differently and the name is certainly not on the reserved words list ;). I haven't touched Autocommit, nor do I know what it is. I'm going to look into it. Thanks! – Luuuud Feb 24 '14 at 19:17
  • I'd be interested in seeing the cumulative value of $art_str. – alfadog67 Feb 24 '14 at 19:49
  • `var_dump($art_str)` returns `string(51) "INSERT INTO articles SET col1=?, col2=?, col2=?"` This is when no optional parameters are given. Nothing is being written to the DB though... – Luuuud Feb 24 '14 at 20:06

1 Answers1

0

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.

halfer
  • 18,701
  • 13
  • 79
  • 158
  • Thanks for adding it as an answer. although it isn't really an answer but a quirky work-around. – Luuuud Feb 25 '14 at 10:08
  • That's okay - just bear in mind that since we have a data API here, and flags to specify that a question is answered, it's best for answers to be stored as answers. Though, as far as I know, there is no `is_quirky` boolean field `:-p`. – halfer Feb 25 '14 at 10:27