Prepared statement with UPDATE

[IndianaGuy]

My very first prepared statement :-). I am sure its wrong, but not sure why . I get error "Call to a member function bind_param() on boolean".  Thank you for your advice.

	 
	<?php
	function updateCallStats($colName,$sesID){
    
$servername = "localhost";
$username = "root";
$password = "";
$database = "abc"; 
$conn = new mysqli($servername,$username,$password,$database);
 
    $stmt = $conn->prepare("UPDATE tbl_calls SET ? = ? + 1 WHERE memberid_fk = ?");
    $stmt->bind_param("ssi", $colName, $colName, $sesID);
    $stmt->execute();
    echo "Wooohoo. I did it";
}
	
$colName = "opener_Ask";
$sesID = "10";
	updateCallStats($colName, $sesID);
	?>
   

     

[Don E]

Looks like you are trying to set a column name with bind_param. You can just name the column after SET instead of trying to bind_param it with ?. Also if the column name is opener_Ask, why aren't you updating that column row value with "opener_Ask + 1"?

Also, Between bind_param and execute, you set the parameters like the following:

 $stmt->bind_param("ssi", $colName, $colName, $sesID);
 $colName = $colName;
 $sesID = $sesID;
 $stmt->execute();

See https://www.w3schools.com/php/php_mysql_prepared_statements.asp

Edited November 26, 2017 by Don E

[IndianaGuy]

I appreciate your help. Still trying to figure out your response and how to use it on a function, or if that is even a good idea. this $stmt is going to be ran several times in  a row. I figure prepared statement is the best way to go. Also, column opener_Ask is a integer. it's just a counter keeping track of how many times it was triggered.

 

The following works but I would rather keep it the old way so I can learn more on how it works lol.Also $colName is the one that will change everytime more so than the $sesID

	 $stmt = $conn->prepare("UPDATE tbl_calls SET $colName = $colName + 1 WHERE memberid_fk = ?");
	 

Edited November 26, 2017 by IndianaGuy

[Don E]

Yes that's what I meant. Instead of  SET $colName = $colName + 1, you can just do: SET opener_Ask = opener_Ask + 1. In this situation you probably don't need to do a prepare statement for that column. Usually/typically, doing prepare statements are when user inputs are being used/supplied for SQL queries  but on some occasions it's not necessary. But it's definitely good practice. 

[justsomeguy]

You cannot substitute identifiers like table or column names in prepared statements, the placeholders are only for the data.  The reason for that is because when the database prepares the statement it creates an execution plan, and it cannot create an execution plan if it doesn't know what tables, columns, etc are going to be used.

If you need to use dynamic identifiers you should make sure to surround them with backticks, and it's also a good idea to validate everything (especially if those identifiers get submitted from a user).