the importance of the $types parameter in mysqli_stmt::bind_param
I've been testing my wrapper for mysqli prepared statements, and have come accross something I didn't expect. In my table guests, first_name is a varchar (basically a string), and id is an integer. My function uses getType on parameters like $first_name and $id in the example below to build the $types parameter for the bind_param method. I was ready to tackle issues like what happens when $id is taken from form input and is actually a string, or, what happens if somweone enters a string that is a number, when I realised it didn't matter 0_0
$SQL = 'update guests set first_name = ? where id = ?'; $mysqli->execute($SQL, $first_name, $id);
all the following cases resulted in successfull inserts:
$id = "1"; $first_name = "Frank"; $types param was 'ss';
$id = 1; $first_name = 3; $types param was 'ii';
So, what's the deal with this?
call_user_func_array(array($statement, 'bind_param'), $bind_params);
You think the way I'm calling bind_param is a factor? In any way, I would love to know why.
The $types parameter affects the generated SQL, so the first example gives the following SQL:
update guests set first_name = 'Frank' where id = '1';
and the second:
update guests set first_name = 3 where id = 1;
These are both valid SQL statements, as MySQL handles the type conversion for you. Hence, the $types parameter matters, but only up to the validity of the SQL statement it generates.