specifying mysqli bind_param types

Question

what are the pros and cons of specifying mysqli bind_param types i,d,s,b?

I store a UNIX timestamp in a db column with data type INT. In my prepared statement I set it to bind_param('s',$timestamp); and it stores the value without any problems. So what impact did it make that I used s instead of i during bind_param?

Answer 1

It influences how MySQL will see the query/value. Take this query:

INSERT INTO foo VALUES (?)

If you bind the parameter as int:

$stmt->bind_param('i', $bar);

Then the query will be about equivalent to:

INSERT INTO foo VALUES (42)

However, if you bind as string:

$stmt->bind_param('s', $bar);

The query will be about equivalent to:

INSERT INTO foo VALUES ('42')

In many cases MySQL doesn't particularly care whether you send it a string or an int and will simply cast the value to the appropriate type on the fly, just as PHP does most of the time. However, in some circumstances and with some types you may get an unexpected conversion, which is why you should bind the value as the type that you want it as and not leave it up to MySQL's casting rules.