PHP mysqli bind_param type for text

For a feedback form that will dump user comments into a MySQL table, I’m unsure which bind_param type to use for the user-supplied feedback text (MySQL field type = text)

function sql_ins_feedback($dtcode,$custip,$name,$email,$subject,$feedback)
{
    global $mysqli ;
    if($stmt = $mysqli->prepare("INSERT INTO feedback (dtcode,custip,name,email,subject,feedback) VALUES (?,?,?,?,?,?)")) 
    {
        $stmt->bind_param("ssssss", $dtcode,$custip,$name,$email,$subject,$feedback);
        $stmt->execute() ;
        $stmt->close() ; 
    }
}

OR THIS?

        $stmt->bind_param("sssssb", $dtcode,$custip,$name,$email,$subject,$feedback);

So, is the blob type the correct bind_param type for a text field?

What is the size limit for a bind_param(“s”) type?

Is there anything else one must do when using bind_param(“b”) ? The manual (and something else I read somewhere/sometime) suggests blob types are treated differently — anything I should know?

Thanks

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that “max_allowed_packet” threshold, just use “s” for the binding type for any text field. Infact, you can usually get away with using “s” for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you’ll want to use mysqli_stmt::send_long_data method and the “b” binding type to send this particular field in chunks.

Solution 2

To those who wanna use mysqli’s bind_param('ssbss', $data) you should use bind_param('sssss'), while you are doing UPDATE or INSERT. This way you can dynamically replace all ? in the prepared query with values stored in an array:

call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($typeValues));

$typeValues is an array merged using array_merge or array_unshift by array(‘sssss’) and array(reference of var1, reference of var2, …);

makeValuesReferenced:

/**
 * All prepared variables' references are needed by function bind_param
 * @param &$arr: array constituted of types and values
 */
function makeValuesReferenced(&$arr){
    $refs = array();
    foreach($arr as $key => $value) {
        // Param 1 of bind_param only needs value of types array
        if($key === 0) {
          $refs[$key] = $arr[$key];
        } else {
          $refs[$key] = &$arr[$key];            
        }
    }
    return $refs;
}

Using bind_param(‘ssbss’, $data) you will only get empty cell in the blob column.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply