Type casting before inserting in database?

Question

Consider the following code snippet:

$day = '3'; // form input 

...

$stmt = $conn->stmt_init();
$q = 'INSERT INTO recording (release_day) VALUES(?)';
$stmt->prepare($q);
$stmt->bind_param('i', $day);
$stmt->execute();
...

The variable $day is a string from a form input, but i'm binding it to an integer. The database column is a smallint datatype.

I'm assuming the database is getting an integer, right? Do i need to do some sort of type casting before the binding? What is consider to be a good practice?

Answer 1

Do i need to do some sort of type casting before the binding?

You are already doing type casting here:

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

This coerces $day into an integer, because of the first parameter value 'i' and then passes that value to the database.

For example, if your variable were '123hello', only 123 would be passed.

Answer 2

$i = 1; // PHP Casts to integer because the lack of quote marks for example.
$i = "1"; // PHP Casts to string because of the quote marks 
$i = 1.39; // PHP Casts to float because of the decimal place

These are an automatic data cast when you have created the variable, so there is no need to have a secondary cast for your variable, just review over the MySQL manual to see the limitations/max byte size of a smallint. Ensure that you make the servers criteria and you wont have a problem http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html