Using msqli bind param with inconsistant data

Question

My question is simple.. When using bind_param with mysqli extension, you have telling mysqli whether it is a string, integer, double, or blob.

My issue is I am using an API to retrieve data back from a service. Sometimes I have an issue where the data expected would be an integer (but there is nothing, so it's NULL) which causes an error.

Which way should I pursue the issue:

1. tell msyqli they are all strings and allow the null value

2. cycle through each variable that would potentially be an integer and if it is null set it to 0.

An example of my query is as follows:

$stmt->bind_param("issssssisisiiiissssssssiiiiisis", $MLSNumber, $ListPrice, $this->propertyType, $ListingStatus, $this->newAddress, $City, $State, $PostalCode, $SchoolDistrict, $YearBuilt, $AcresApx, $TotalRooms, $Bedrooms, $BathsFull, $BathsHalf, $PublicRemarks, $remark, $remark, $remark, $remark, $remark, $remark, $remark, $ListAgentID, $ListAgentID, $ListOfficeID, $ListAgentID, $ListOfficeID, $this->retsUpdatedDateTime, $ListingID, $this->retsPhotosUpdatedDateTime);

The problem is, like I stated, I cannot be 100% sure that the values returned will be there. If they are, they will always be the correct format (string or int) otherwise, they are NULL.

Thanks for the input.

Answer 1

If you are going to enforce data type of parameters by using bind_param() you probably need to handle the validation of the variables you are going to bind as parameters before you try to execute the bind operation. If for example, a variable is not defined before the bind_param() call is made, you would probably want to explicitly set the variable to NULL.

So long as your columns in the database accept null values, you should not have a problem using null values for inserts or updates. If however you are trying to use this the context of a WHERE clause you are not going to have any luck as a construct such as WHERE some_field = ?, providing a null value will cause the query to fail (as you would really need something like WHERE some_field IS NULL.

You have not shared enough about you actual prepared statement SQL or the problematic data examples you are encountering to give more advice on a specific solution. If you add those to your question, perhaps you can get more specific answers on how to approach the problem.

The bottom line though is that id you are are trying to enforce specific parameters type behaviors in the binding, you should inspect the parameters to make sure you have what you need to run the prepared statement before you even get to the point of attempting to prepare the statement. Without doing this, you are going to have some fragile code.