Jump to content

Prepared statement with UPDATE


Recommended Posts

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.

	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);
    echo "Wooohoo. I did it";
$colName = "opener_Ask";
$sesID = "10";
	updateCallStats($colName, $sesID);
Link to comment
Share on other sites

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;

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

Edited by Don E
Link to comment
Share on other sites

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 by IndianaGuy
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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).

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...