bind_param problem with binding boolean values

Question

I have a problem binding booleans using mysqli_stmt::bind_param in PHP5.

The SQL query is the following:

insert into `nvp_notes` (subject,messageid,receivedate,read) values (?,?,?,?)

Where 'read' is a tinyint, either 0 or 1, as I've had issues with bit using mysqli. So the types that I list in bind_param are:

$stmt->bind_param('sdsd', ...);

I've also tried 'sdsb' and 'sdss' but nothing seems to work, and I always get the message:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

When I remove the read field in the statement everything works fine. I've run out of ideas with this one. Surely bind_param works with booleans?

Answer 1

You could convert the boolean to a 1 or 0 using intval() (or cast it with (int) or (integer)). According to mysqli_stmt::bind_param()'s documentation, the only types you may bind are ints, doubles, strings, and blobs.

Answer 2

Thanks for the answer, I finally managed to fix the problem (after trying type casting and even leaving it out of bind_param by fixing it as a 1 or 0 in the query). Anyway, 'read' is a reserved column name in MySQL, so I just changed the column name and it works fine. Seems strange to receive that particular error message however for this kind of problem.

Answer 3

As what you need to bind is actually an Integer, I would swtich the 'd'(double) by an 'i':

$stmt->bind_param('sisi', ...);