PHP mysqli type bind_param for text

Question

For a feedback form that will upload user comments to a MySQL table, I don’t know what type of bind_param to use for user-provided feedback text (field type MySQL = 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 THAT?

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

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

What is the size limit for type bind_param ("s")?

Is there anything else that needs to be done when using bind_param ("b")? The manual (and something else that I read somewhere / once) suggests blob types are handled differently - all I need to know?

thanks

Answer 1

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

If your data is merged under this "max_allowed_packet" , just use the "s" for the binding type for any text field. Infact, you can usually get away using "s" for any type of field (date, float, etc.).

If your entire record that you want to insert exceeds 1 mb (or whatever you reset it) in length, you will want to use mysqli_stmt :: send_long_data and the binding type "b" to send this particular field to pieces.

Answer 2

For those who want to use mysqli bind_param('ssbss', $data) , you should use bind_param('sssss') while you do UPDATE or INSERT. So you can dynamically replace everything? in a prepared query with values ​​stored in an array:

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

$typeValues is an array, combined using array_merge or array_unshift by array ('sssss') and array (reference to var1, reference to 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 an empty cell in the blob column.